Subject: Problems with indices on user-defined data types

From: [email-masked] (Wolf-D. Ihlenfeldt)
Date: Thu, 6 May 93 03:53:58 -0700

Using Postgres4.1 on Sparc II runnign SunOS 4.1.2, I have serious
problems when I try to define indices on my private data types.
The comparison functions itself work fine, but if I try to
define an index on a nonempty class with the opclass containing
these functions I constantly get the message:

WARN:May  6 19:25:12:fmgr_info: function 0: cache lookup failed

If the class is empty, I can define the index, but after that 
writes into the class fail with the same message.

Thinking I made something wrong with the opclass definition,
I tediously retyped the int4_abs example from the documentation,
I get exactly the same message when I define the index:

Go 
* define index pairsind on pairs using btree (number int4_abs_ops)\g

Query sent to backend is "define index pairsind on pairs using btree (number int4_abs_ops)"
WARN:May  6 19:25:12:fmgr_info: function 0: cache lookup failed

What is wrong here ???

WDI

P.S. The script for defining an index automatically  like this:

define function uint8_in (language = "c", returntype = uint8)
   arg is (char16) as "/home2/wolf/WB/PG/pg_uint8.o"
 
define function uint8_out (language = "c", returntype = char16)
   arg is (uint8) as "/home2/wolf/WB/PG/pg_uint8.o"
 
define type uint8 (internallength = 8, input = uint8_in, output = uint8_out)
 
define function uint8_lt ( language = "c", returntype = bool)
   arg is (uint8, uint8) as "/home2/wolf/WB/PG/pg_uint8.o"
 
define operator < (arg1 = uint8, arg2 = uint8, procedure = uint8_lt,
   restrict = intltsel, join = intltjoinsel, associativity = left)
 
define function uint8_le ( language = "c", returntype = bool)
   arg is (uint8, uint8) as "/home2/wolf/WB/PG/pg_uint8.o"
 
define operator <= (arg1 = uint8, arg2 = uint8, procedure = uint8_le,
   restrict = intltsel, join = intltjoinsel, associativity = left)
 
define function uint8_eq ( language = "c", returntype = bool)
   arg is (uint8, uint8) as "/home2/wolf/WB/PG/pg_uint8.o"
 
define operator = (arg1 = uint8, arg2 = uint8, procedure = uint8_eq,
   restrict = eqsel, join = eqjoinsel, associativity = left)
 
define function uint8_ge ( language = "c", returntype = bool)
   arg is (uint8, uint8) as "/home2/wolf/WB/PG/pg_uint8.o"

define operator >= (arg1 = uint8, arg2 = uint8, procedure = uint8_ge,
   restrict = intgtsel, join = intgtjoinsel, associativity = left)
 
define function uint8_gt ( language = "c", returntype = bool)
   arg is (uint8, uint8) as "/home2/wolf/WB/PG/pg_uint8.o"
 
define operator > (arg1 = uint8, arg2 = uint8, procedure = uint8_gt,
   restrict = intgtsel, join = intgtjoinsel, associativity = left)
 
define function uint8_neq ( language = "c", returntype = bool)
   arg is (uint8, uint8) as "/home2/wolf/WB/PG/pg_uint8.o"
 
define operator != (arg1 = uint8, arg2 = uint8, procedure = uint8_neq)

retrieve (pg_am.oid) where pg_am.amname = "btree"

%btree_oid:oid

append pg_opclass (opcname = "uint8_ops")

retrieve (pg_opclass.oid) where pg_opclass.opcname = "uint8_ops"
 
%opc_oid:oid
 
retrieve (o.oid) from o in pg_operator, t in pg_type
   where o.oprleft = t.oid and o.oprright = t.oid and t.typname = "uint8"
 
%opr_oid:oid
 
append pg_amop
   ( amopid = "%btree_oid"::oid,
     amopclaid = "%opc_oid"::oid,
     amopopr = "%opr_oid(1)"::oid,
     amopstrategy = "1"::int2,
     amopselect = "btreesel"::regproc,
     amopnpages = "btreenpage"::regproc)

append pg_amop
   ( amopid = "%btree_oid"::oid,
     amopclaid = "%opc_oid"::oid,
     amopopr = "%opr_oid(2)"::oid,
     amopstrategy = "2"::int2,
     amopselect = "btreesel"::regproc,
     amopnpages = "btreenpage"::regproc)
 
append pg_amop
   ( amopid = "%btree_oid"::oid,
     amopclaid = "%opc_oid"::oid,
     amopopr = "%opr_oid(3)"::oid,
     amopstrategy = "3"::int2,
     amopselect = "btreesel"::regproc,
     amopnpages = "btreenpage"::regproc)
 
append pg_amop
   ( amopid = "%btree_oid"::oid,
     amopclaid = "%opc_oid"::oid,
     amopopr = "%opr_oid(4)"::oid,
     amopstrategy = "4"::int2,
     amopselect = "btreesel"::regproc,
     amopnpages = "btreenpage"::regproc)
 
append pg_amop
   ( amopid = "%btree_oid"::oid,
     amopclaid = "%opc_oid"::oid,
     amopopr = "%opr_oid(5)"::oid,
     amopstrategy = "5"::int2,
     amopselect = "btreesel"::regproc,
     amopnpages = "btreenpage"::regproc)
 
------
The lines beginning with '%' save the retrieval result into a variable.
The variable values are expanded in the pg_amop section,
%btree_oid is replace by the oid of btrees (403, as in the manual
where it is mentioned first, afterwards inconsistently the value 400
is assumed), %opr_oid(5) is the 5th word (i.e. the > operatir
on the uint8 type). The 6th operator on uint8 is !=, which is discarded
here. When the script file is read, an empty line triggers the
variable substitution and sends the command to libpq. I looked into
all the relevant tables, everything looks logical, sane and like in
the documented example. 

All help is appreciated.

WDI

---

From: Michael Ubell <[email-masked]>
Date: Thu, 06 May 93 08:20:44 -0700

I believe you are missing an entry in the amproc table.  The btree acess method
requires 1 support routine.