Thread: Trying to create a GiST index in 7.3
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
Dmitry Tkach <dmitry@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
On Fri, 2003-08-08 at 16:08, Tom Lane wrote: > Dmitry Tkach <dmitry@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
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/postgres/gist/doc/gist-inteface-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 <dmitry@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: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Christopher Murtagh <christopher.murtagh@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/cvsweb.cgi/pgsql-server/contrib/cube/cube.sql.in.diff?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