By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,466 Members | 1,346 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,466 IT Pros & Developers. It's quick & easy.

Trying to create a GiST index in 7.3

P: n/a
Hi, everybody!

I am trying to create a custom GiST index in 7.3, but getting an error,
that I don't know how to interpret:

testdb=# create table gist_test (field int8);
CREATE TABLE
testdb=# create index gist_idx on gist_test using gist (field);
ERROR: data type bigint has no default operator class for access method
"gist"
You must specify an operator class for the index or define a
default operator class for the data type
testdb=#

I have done all the setup that was required in 7.2.4:

testdb=# select * from pg_opclass where opcname = 'gist_index_ops';
-[ RECORD 1 ]+--------------
opcamid | 783
opcname | gist_index_ops
opcnamespace |
opcowner |
opcintype | 20
opcdefault | t
opckeytype | 0
testb=# select * from pg_am where oid=783;
-[ RECORD 1 ]---+-----------------
amname | gist
amowner | 1
amstrategies | 100
amsupport | 7
amorderstrategy | 0
amcanunique | f
amcanmulticol | t
amindexnulls | f
amconcurrent | f
amgettuple | gistgettuple
aminsert | gistinsert
ambeginscan | gistbeginscan
amrescan | gistrescan
amendscan | gistendscan
ammarkpos | gistmarkpos
amrestrpos | gistrestrpos
ambuild | gistbuild
ambulkdelete | gistbulkdelete
amcostestimate | gistcostestimate

rapidb=# select * from pg_type where oid=20;
-[ RECORD 1 ]-+--------
typname | int8
typnamespace | 11
typowner | 1
typlen | 8
typbyval | f
typtype | b
typisdefined | t
typdelim | ,
typrelid | 0
typelem | 0
typinput | int8in
typoutput | int8out
typalign | d
typstorage | p
typnotnull | f
typbasetype | 0
typtypmod | -1
typndims | 0
typdefaultbin |
typdefault |
Any ideas what's missing? Is there anything new in 7.3, that I have to do?

Thanks a lot!

Dima

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 11 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Dmitry Tkach <dm****@openratings.com> writes:
I am trying to create a custom GiST index in 7.3, but getting an error,
...
I have done all the setup that was required in 7.2.4:
You should not be using the 7.2 methods anymore --- there is a CREATE
OPERATOR CLASS, use that instead. (See the contrib gist classes for
examples.)
testdb=# select * from pg_opclass where opcname = 'gist_index_ops';
-[ RECORD 1 ]+--------------
opcamid | 783
opcname | gist_index_ops
opcnamespace |
opcowner |
opcintype | 20
opcdefault | t
opckeytype | 0


Those NULL fields probably explain your problems ... (the fields are
marked NOT NULL, but due to an oversight, the constraint is not
enforced against core system catalogs in 7.3 :-()

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #2

P: n/a
On Fri, 2003-08-08 at 16:08, Tom Lane wrote:
Dmitry Tkach <dm****@openratings.com> writes:
I am trying to create a custom GiST index in 7.3, but getting an error,
...
I have done all the setup that was required in 7.2.4:


You should not be using the 7.2 methods anymore --- there is a CREATE
OPERATOR CLASS, use that instead. (See the contrib gist classes for
examples.)


I'm having the same problem as Dmitry, but I've been unable to find a
solution. I've looked everywhere googleable for info on setting up GiST
indexes, but haven't found any info that doesn't look like post-doc
papers on the theory of indexability.

I'd be happy with an RTFM response, if I could just find TFM. :-)

Any info would be much appreciated.

Cheers,

Chris

--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Communications Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax: (514) 398-2017

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #3

P: n/a
Hi there,

I'm back from vacation and clearing my mbox.
I intended to write documentation about GiST, but other things grab attention :)
There is quite short intro in Russian
http://www.sai.msu.su/~megera/postgr...teface-r.shtml
and a bunch of GiST modules
http://www.sai.msu.su/~megera/postgres/gist/
so you may learn by examples.

Oleg

On Wed, 17 Sep 2003, Christopher Murtagh wrote:
On Fri, 2003-08-08 at 16:08, Tom Lane wrote:
Dmitry Tkach <dm****@openratings.com> writes:
I am trying to create a custom GiST index in 7.3, but getting an error,
...
I have done all the setup that was required in 7.2.4:


You should not be using the 7.2 methods anymore --- there is a CREATE
OPERATOR CLASS, use that instead. (See the contrib gist classes for
examples.)


I'm having the same problem as Dmitry, but I've been unable to find a
solution. I've looked everywhere googleable for info on setting up GiST
indexes, but haven't found any info that doesn't look like post-doc
papers on the theory of indexability.

I'd be happy with an RTFM response, if I could just find TFM. :-)

Any info would be much appreciated.

Cheers,

Chris


Regards,
Oleg
__________________________________________________ ___________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: ol**@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 11 '05 #4

P: n/a
Christopher Murtagh <ch*****************@mcgill.ca> writes:
On Fri, 2003-08-08 at 16:08, Tom Lane wrote:
You should not be using the 7.2 methods anymore --- there is a CREATE
OPERATOR CLASS, use that instead. (See the contrib gist classes for
examples.)
I'm having the same problem as Dmitry, but I've been unable to find a
solution. I've looked everywhere googleable for info on setting up GiST
indexes, but haven't found any info that doesn't look like post-doc
papers on the theory of indexability.


That's about what there is AFAIK :-(. GiST suffers from a horrible lack
of documentation other than the original academic papers, which is one
of the reasons it's still not mainstream (although I'm not entirely sure
which is cause and which is effect here...)

However, if you have a working 7.2 opclass definition, it shouldn't be
that hard to make it into a 7.3 CREATE OPERATOR CLASS command. Compare
the 7.2 and 7.3 versions of any of the contrib GiST modules' sql.in
files, and all should become reasonably clear. The same basic
information is being supplied in both cases (operator names and strategy
numbers), 7.3 just does it with a much higher-level notation. For
instance, this part of 7.2's contrib/cube/cube.sql.in:

-- cube_left
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
SELECT opcl.oid, 1, false, c.opoid
FROM pg_opclass opcl, gist_cube_ops_tmp c
WHERE
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
and opcname = 'gist_cube_ops'
and c.oprname = '<<';

is replaced by a single line in 7.3's CREATE OPERATOR CLASS command:

OPERATOR 1 << ,

In particular look at this diff:
http://developer.postgresql.org/cvsw...?r1=1.4&r2=1.5

You might also want to study the docs for the pg_opclass, pg_amop,
and pg_amproc system catalogs, to clarify what the original code was
doing.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.