Re: operator classes for index?

=?ISO-8859-1?Q?Yves_Wei=DFig?= <weissig(at)rbg(dot)informatik(dot)tu-darmstadt(dot)de> writes:
> Am 26.04.2011 14:28, schrieb Robert Haas:
>> On Tue, Apr 26, 2011 at 5:18 AM, Yves Weiig
>> <weissig(at)rbg(dot)informatik(dot)tu-darmstadt(dot)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