Re: operator classes for index?

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