Thread: operator classes for index?
Hi, again index access methods, can somebody shed some light into operator classes for indexes? The documentation is an entry point, but after reading I still don't have a clue how exactly they are used and created? Perhaps somebody with great knowledge can supply an 101 on opeartor classes? Because I keep getting the hint: You must specify an operator class for the index or define a default operator class for the data type. Greets, Yves
Yves Weißig <weissig@rbg.informatik.tu-darmstadt.de> writes: > again index access methods, can somebody shed some light into operator > classes for indexes? The documentation is an entry point, but after > reading I still don't have a clue how exactly they are used and created? > Perhaps somebody with great knowledge can supply an 101 on opeartor > classes? Because I keep getting the hint: You must specify an operator > class for the index or define a default operator class for the data type. Have you read http://developer.postgresql.org/pgdocs/postgres/indexes-opclass.html http://developer.postgresql.org/pgdocs/postgres/xindex.html and the reference pages for CREATE OPERATOR CLASS/FAMILY? If it's still not coming together for you, there are numerous examples of creating operator classes in the contrib modules. The GIST and GIN documentation might be relevant as well: http://developer.postgresql.org/pgdocs/postgres/gist.html http://developer.postgresql.org/pgdocs/postgres/gin.html regards, tom lane
Am 24.04.2011 23:33, schrieb Tom Lane: > Yves Weißig <weissig@rbg.informatik.tu-darmstadt.de> writes: >> again index access methods, can somebody shed some light into operator >> classes for indexes? The documentation is an entry point, but after >> reading I still don't have a clue how exactly they are used and created? >> Perhaps somebody with great knowledge can supply an 101 on opeartor >> classes? Because I keep getting the hint: You must specify an operator >> class for the index or define a default operator class for the data type. > > Have you read > http://developer.postgresql.org/pgdocs/postgres/indexes-opclass.html > http://developer.postgresql.org/pgdocs/postgres/xindex.html > and the reference pages for CREATE OPERATOR CLASS/FAMILY? Thanks Tom, those links helped me understanding! Especially the contrib modules served as good examples. But anyway I am having trouble creating an operator class: CREATE OPERATOR CLASS abstime_opsDEFAULT FOR TYPE abstime USING ebi FAMILY abstime_ops ASOPERATOR 1 = ,FUNCTION 1 abstimeeq(abstime,abstime); yields: ERROR: invalid procedure number 1, must be between 1 and 0 SQL Status:42P17 I couldn't find additional information to the error via google, what is wrong with the create statement? Additional, I don't know yet how to create index method support routines. I want to re-use the hash functions from hashfunc.c (because I do kind of a mapping). Is this possible? How does index_getprocinfo(); now which support routine belongs to my index? > > If it's still not coming together for you, there are numerous examples > of creating operator classes in the contrib modules. The GIST and GIN > documentation might be relevant as well: > http://developer.postgresql.org/pgdocs/postgres/gist.html > http://developer.postgresql.org/pgdocs/postgres/gin.html > > regards, tom lane > Greetz, Yves
Yves Weißig <weissig@rbg.informatik.tu-darmstadt.de> writes: > But anyway I am having trouble creating an operator class: > CREATE OPERATOR CLASS abstime_ops > DEFAULT FOR TYPE abstime USING ebi FAMILY abstime_ops AS > OPERATOR 1 = , > FUNCTION 1 abstimeeq(abstime,abstime); > yields: ERROR: invalid procedure number 1, must be between 1 and 0 Apparently you've got zero in pg_am.amsupport for your new index AM. You need to set that to the number of support-procedure types your AM defines. Have you been through http://developer.postgresql.org/pgdocs/postgres/indexam.html and the docs and source code for the pg_am, pg_amop, pg_amproc catalogs? See http://developer.postgresql.org/pgdocs/postgres/catalogs.html as well as the src/include/catalog/ files for those catalogs. > Additional, I don't know yet how to create index method support > routines. I want to re-use the hash functions from hashfunc.c (because I > do kind of a mapping). Is this possible? Just list them in your CREATE OPERATOR CLASS commands. > How does index_getprocinfo(); > now which support routine belongs to my index? It looks in pg_amproc to find the routines that are entered for the opclass associated with the index. This is a pretty direct representation of the FUNCTION entries from your previous CREATE OPERATOR CLASS (or if you prefer, CREATE OPERATOR CLASS is designed to provide the information needed to populate pg_amop and pg_amproc). regards, tom lane
Am 26.04.2011 01:15, schrieb Tom Lane: > Yves Weißig <weissig@rbg.informatik.tu-darmstadt.de> writes: >> But anyway I am having trouble creating an operator class: > >> CREATE OPERATOR CLASS abstime_ops >> DEFAULT FOR TYPE abstime USING ebi FAMILY abstime_ops AS >> OPERATOR 1 = , >> FUNCTION 1 abstimeeq(abstime,abstime); > >> yields: ERROR: invalid procedure number 1, must be between 1 and 0 > > Apparently you've got zero in pg_am.amsupport for your new index AM. > You need to set that to the number of support-procedure types your AM > defines. Have you been through > http://developer.postgresql.org/pgdocs/postgres/indexam.html > and the docs and source code for the pg_am, pg_amop, pg_amproc catalogs? > See > http://developer.postgresql.org/pgdocs/postgres/catalogs.html > as well as the src/include/catalog/ files for those catalogs. > >> Additional, I don't know yet how to create index method support >> routines. I want to re-use the hash functions from hashfunc.c (because I >> do kind of a mapping). Is this possible? > > Just list them in your CREATE OPERATOR CLASS commands. Alright, now I starting to get the point. Still I have a problem, when I am trying to execute CREATE OPERATOR CLASS abstime_opsDEFAULT FOR TYPE abstime USING ebi FAMILY abstime_ops ASOPERATOR 1 = (abstime,abstime),FUNCTION1 hashint4(abstime,abstime); it yields: ERROR: function hashint4(abstime, abstime) does not exist though it exists (it is part of the hash AM), do I have to note the namespace or something else? pg_proc has a row for hashint4, but of course with different parameter types, int4 namely. Where do I cast them? Or is a implict conversion performed? Thanks again! > >> How does index_getprocinfo(); >> now which support routine belongs to my index? > > It looks in pg_amproc to find the routines that are entered for the > opclass associated with the index. This is a pretty direct > representation of the FUNCTION entries from your previous CREATE > OPERATOR CLASS (or if you prefer, CREATE OPERATOR CLASS is designed to > provide the information needed to populate pg_amop and pg_amproc). > > regards, tom lane > Greetz, Yves
On Tue, Apr 26, 2011 at 5:18 AM, Yves Weißig <weissig@rbg.informatik.tu-darmstadt.de> wrote: > CREATE OPERATOR CLASS abstime_ops > DEFAULT FOR TYPE abstime USING ebi FAMILY abstime_ops AS > OPERATOR 1 = (abstime,abstime), > FUNCTION 1 hashint4(abstime,abstime); > > it yields: ERROR: function hashint4(abstime, abstime) does not exist > though it exists (it is part of the hash AM), do I have to note the My copy of PostgreSQL has a hashint4(integer) function, but no hashint4(abstime, abstime) function. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Am 26.04.2011 14:28, schrieb Robert Haas: > On Tue, Apr 26, 2011 at 5:18 AM, Yves Weißig > <weissig@rbg.informatik.tu-darmstadt.de> wrote: >> CREATE OPERATOR CLASS abstime_ops >> DEFAULT FOR TYPE abstime USING ebi FAMILY abstime_ops AS >> OPERATOR 1 = (abstime,abstime), >> FUNCTION 1 hashint4(abstime,abstime); >> >> it yields: ERROR: function hashint4(abstime, abstime) does not exist >> though it exists (it is part of the hash AM), do I have to note the > > My copy of PostgreSQL has a hashint4(integer) function, but no > hashint4(abstime, abstime) function. > Sorry. Yes, I know, maybe my question wasn't clear enough. Following statement: SELECT am.amname AS index_method, opfamily.opfname AS opfamily_name, proc.proname AS procedure_name, amproc.*, typel.typname AS left_typname, typer.typname AS right_typname FROM pg_am am, pg_amproc amproc, pg_proc proc, pg_opfamily opfamily, pg_type typel, pg_type typer WHERE amproc.amprocfamily = opfamily.oid AND amproc.amproc = proc.oid AND opfamily.opfmethod = am.oid AND am.amname = 'hash' AND amproc.amproclefttype = typel.oid AND amproc.amprocrighttype = typer.oid ORDER BY opfamily_name, procedure_name; I get: "hash";"abstime_ops";"hashint4";2227;702;702;1;"hashint4";"abstime";"abstime" as an entry and suppose that hashint4 also takes "abstime" How is it done? How is hashint4 used to hash a value of "abstime"?
Yves Weißig <weissig@rbg.informatik.tu-darmstadt.de> writes:
> Am 26.04.2011 14:28, schrieb Robert Haas:
>> On Tue, Apr 26, 2011 at 5:18 AM, Yves Wei�ig
>> <weissig@rbg.informatik.tu-darmstadt.de> wrote:
>>> CREATE OPERATOR CLASS abstime_ops
>>> DEFAULT FOR TYPE abstime USING ebi FAMILY abstime_ops AS
>>> OPERATOR 1 = (abstime,abstime),
>>> FUNCTION 1 hashint4(abstime,abstime);
>>> it yields: ERROR: function hashint4(abstime, abstime) does not exist
>> My copy of PostgreSQL has a hashint4(integer) function, but no
>> hashint4(abstime, abstime) function.
> Yes, I know, maybe my question wasn't clear enough. Following statement:
> ...
> I get:
> "hash";"abstime_ops";"hashint4";2227;702;702;1;"hashint4";"abstime";"abstime"
> as an entry and suppose that hashint4 also takes "abstime"
> How is it done? How is hashint4 used to hash a value of "abstime"?
Cheating ;-). That entry is hard-wired in pg_amproc.h so it does not
pass through the same kind of error checking that CREATE OPERATOR CLASS
applies. It works, physically, because abstime and integer are binary
compatible (both 4-byte int-aligned pass-by-value types), but the
catalog entries are a bit inconsistent. If we wanted to make this look
completely clean, we'd have to create an alias function that was
declared to take abstime. For instance you could do it like this:
create function hashabstime(abstime) returns int4as 'hashint4' language internal strict immutable;
and then say FUNCTION 1 hashabstime(abstime) in CREATE OPERATOR CLASS.
You might find this extract from the opr_sanity regression test
instructive:
-- For hash we can also do a little better: the support routines must be
-- of the form hash(lefttype) returns int4. There are several cases where
-- we cheat and use a hash function that is physically compatible with the
-- datatype even though there's no cast, so this check does find a small
-- number of entries.
SELECT p1.amprocfamily, p1.amprocnum, p2.proname, p3.opfname
FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND p1.amprocfamily = p3.oid AND p1.amproc =
p2.oidAND (amprocnum != 1 OR proretset OR prorettype != 'int4'::regtype OR pronargs != 1 OR NOT
physically_coercible(amproclefttype,proargtypes[0]) OR amproclefttype != amprocrighttype)
ORDER BY 1;amprocfamily | amprocnum | proname | opfname
--------------+-----------+----------------+----------------- 435 | 1 | hashint4 | date_ops
1999 | 1 | timestamp_hash | timestamptz_ops 2222 | 1 | hashchar | bool_ops 2223 |
1 | hashvarlena | bytea_ops 2225 | 1 | hashint4 | xid_ops 2226 | 1 |
hashint4 | cid_ops
(6 rows)
regards, tom lane
Am 26.04.2011 17:37, schrieb Tom Lane: > Yves Weißig <weissig@rbg.informatik.tu-darmstadt.de> writes: >> Am 26.04.2011 14:28, schrieb Robert Haas: >>> On Tue, Apr 26, 2011 at 5:18 AM, Yves Weißig >>> <weissig@rbg.informatik.tu-darmstadt.de> wrote: >>>> CREATE OPERATOR CLASS abstime_ops >>>> DEFAULT FOR TYPE abstime USING ebi FAMILY abstime_ops AS >>>> OPERATOR 1 = (abstime,abstime), >>>> FUNCTION 1 hashint4(abstime,abstime); > >>>> it yields: ERROR: function hashint4(abstime, abstime) does not exist > >>> My copy of PostgreSQL has a hashint4(integer) function, but no >>> hashint4(abstime, abstime) function. > >> Yes, I know, maybe my question wasn't clear enough. Following statement: >> ... >> I get: >> "hash";"abstime_ops";"hashint4";2227;702;702;1;"hashint4";"abstime";"abstime" >> as an entry and suppose that hashint4 also takes "abstime" >> How is it done? How is hashint4 used to hash a value of "abstime"? > > Cheating ;-). That entry is hard-wired in pg_amproc.h so it does not > pass through the same kind of error checking that CREATE OPERATOR CLASS > applies. It works, physically, because abstime and integer are binary > compatible (both 4-byte int-aligned pass-by-value types), but the > catalog entries are a bit inconsistent. If we wanted to make this look > completely clean, we'd have to create an alias function that was > declared to take abstime. For instance you could do it like this: > > create function hashabstime(abstime) returns int4 > as 'hashint4' language internal strict immutable; > > and then say FUNCTION 1 hashabstime(abstime) in CREATE OPERATOR CLASS. > > You might find this extract from the opr_sanity regression test > instructive: > > -- For hash we can also do a little better: the support routines must be > -- of the form hash(lefttype) returns int4. There are several cases where > -- we cheat and use a hash function that is physically compatible with the > -- datatype even though there's no cast, so this check does find a small > -- number of entries. > SELECT p1.amprocfamily, p1.amprocnum, p2.proname, p3.opfname > FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3 > WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') > AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND > (amprocnum != 1 > OR proretset > OR prorettype != 'int4'::regtype > OR pronargs != 1 > OR NOT physically_coercible(amproclefttype, proargtypes[0]) > OR amproclefttype != amprocrighttype) > ORDER BY 1; > amprocfamily | amprocnum | proname | opfname > --------------+-----------+----------------+----------------- > 435 | 1 | hashint4 | date_ops > 1999 | 1 | timestamp_hash | timestamptz_ops > 2222 | 1 | hashchar | bool_ops > 2223 | 1 | hashvarlena | bytea_ops > 2225 | 1 | hashint4 | xid_ops > 2226 | 1 | hashint4 | cid_ops > (6 rows) > > regards, tom lane > Thanks so much Tom, I was really loosing my mind on this one... now it works! Awesome. Yves