Re: [HACKERS] operator classes for index?
Am 26.04.2011 17:37, schrieb Tom Lane: =?ISO-8859-1?Q?Yves_Wei=DFig?= weis...@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 weis...@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 | 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator classes for index?
Am 26.04.2011 01:15, schrieb Tom Lane: =?ISO-8859-15?Q?Yves_Wei=DFig?= weis...@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_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 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator classes for index?
On Tue, Apr 26, 2011 at 5:18 AM, Yves Weißig weis...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator classes for index?
Am 26.04.2011 14:28, schrieb Robert Haas: On Tue, Apr 26, 2011 at 5:18 AM, Yves Weißig weis...@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? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator classes for index?
=?ISO-8859-1?Q?Yves_Wei=DFig?= weis...@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 weis...@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 | 1 | hashchar | bool_ops 2223 | 1 | hashvarlena| bytea_ops 2225 | 1 | hashint4 | xid_ops 2226 | 1 | hashint4 | cid_ops (6 rows) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator classes for index?
Am 24.04.2011 23:33, schrieb Tom Lane: =?ISO-8859-15?Q?Yves_Wei=DFig?= weis...@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_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 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator classes for index?
=?ISO-8859-15?Q?Yves_Wei=DFig?= weis...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator classes for index?
=?ISO-8859-15?Q?Yves_Wei=DFig?= weis...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
