Re: operator classes for index?

Am 26.04.2011 17:37, schrieb Tom Lane:
> =?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 Weißig
>>> <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
>

Thanks so much Tom, I was really loosing my mind on this one... now it
works! Awesome.

Yves