Re: operator classes for index?

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