I still can't get this type creation working.  I get the subject error
whenever I try to select on the new type if it is indexed.  Here is a sample.

darcy=> create table x (g glaccount, i int);
CREATE
darcy=> insert into x values ('12345-0000', 1);
INSERT 29124 1
darcy=> select * from x where g = '12345-0000';
         g|i
----------+-
12345-0000|1
(1 row)

darcy=> create unique index y on x (g);
CREATE
darcy=> select * from x where g = '12345-0000';
ERROR:  fmgr_info: function 0: cache lookup failed

As you can see, the select worked until I added the index.  Here is the
SQL that created the glaccount type.  I hope to rewrite the documentation
based on this but I need to get it working first.  Any ideas?

--
--	PostgreSQL code for GLACCOUNTs.
--
--	$Id$
--

load '/usr/local/pgsql/modules/glaccount.so';

--
--	Input and output functions and the type itself:
--

create function glaccount_in(opaque)
	returns opaque
	as '/usr/local/pgsql/modules/glaccount.so'
	language 'c';

create function glaccount_out(opaque)
	returns opaque
	as '/usr/local/pgsql/modules/glaccount.so'
	language 'c';

create type glaccount (
	internallength = 16,
	externallength = 13,
	input = glaccount_in,
	output = glaccount_out
);

--
--  Some extra functions
--

create function glaccount_major(glaccount)
	returns int
	as '/usr/local/pgsql/modules/glaccount.so'
	language 'c';

create function glaccount_minor(glaccount)
	returns int
	as '/usr/local/pgsql/modules/glaccount.so'
	language 'c';

create function glaccount_cmp(glaccount, glaccount)
	returns int
	as '/usr/local/pgsql/modules/glaccount.so'
	language 'c';

--
--	The various boolean tests:
--

create function glaccount_eq(glaccount, glaccount)
	returns bool
	as '/usr/local/pgsql/modules/glaccount.so'
	language 'c';

create function glaccount_ne(glaccount, glaccount)
	returns bool
	as '/usr/local/pgsql/modules/glaccount.so'
	language 'c';

create function glaccount_lt(glaccount, glaccount)
	returns bool
	as '/usr/local/pgsql/modules/glaccount.so'
	language 'c';

create function glaccount_gt(glaccount, glaccount)
	returns bool
	as '/usr/local/pgsql/modules/glaccount.so'
	language 'c';

create function glaccount_le(glaccount, glaccount)
	returns bool
	as '/usr/local/pgsql/modules/glaccount.so'
	language 'c';

create function glaccount_ge(glaccount, glaccount)
	returns bool
	as '/usr/local/pgsql/modules/glaccount.so'
	language 'c';

--
--	Now the operators.  Note how some of the parameters to some
--	of the 'create operator' commands are commented out.  This
--	is because they reference as yet undefined operators, and
--	will be implicitly defined when those are, further down.
--

create operator < (
	leftarg = glaccount,
	rightarg = glaccount,
--	negator = >=,
	procedure = glaccount_lt
);

create operator <= (
	leftarg = glaccount,
	rightarg = glaccount,
--	negator = >,
	procedure = glaccount_le
);

create operator = (
	leftarg = glaccount,
	rightarg = glaccount,
	commutator = =,
--	negator = <>,
	procedure = glaccount_eq
);

create operator >= (
	leftarg = glaccount,
	rightarg = glaccount,
	negator = <,
	procedure = glaccount_ge
);

create operator > (
	leftarg = glaccount,
	rightarg = glaccount,
	negator = <=,
	procedure = glaccount_gt
);

create operator <> (
	leftarg = glaccount,
	rightarg = glaccount,
	negator = =,
	procedure = glaccount_ne
);

-- Now, let's see if we can set it up for indexing

INSERT INTO pg_opclass (opcname, opcdeftype) 
	SELECT 'glaccount_ops', oid FROM pg_type WHERE typname = 'glaccount';

SELECT o.oid AS opoid, o.oprname
	INTO TEMP TABLE glaccount_ops_tmp
	FROM pg_operator o, pg_type t
	WHERE o.oprleft = t.oid AND
		o.oprright = t.oid AND
		t.typname = 'glaccount';

INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
			amopselect, amopnpages)
	SELECT am.oid, opcl.oid, c.opoid, 1,
			'btreesel'::regproc, 'btreenpage'::regproc
	FROM pg_am am, pg_opclass opcl, glaccount_ops_tmp c
	WHERE amname = 'btree' AND
		opcname = 'glaccount_ops' AND
		c.oprname = '<';

INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
			amopselect, amopnpages)
	SELECT am.oid, opcl.oid, c.opoid, 2,
			'btreesel'::regproc, 'btreenpage'::regproc
	FROM pg_am am, pg_opclass opcl, glaccount_ops_tmp c
	WHERE amname = 'btree' AND
		opcname = 'glaccount_ops' AND
		c.oprname = '<=';

INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
			amopselect, amopnpages)
	SELECT am.oid, opcl.oid, c.opoid, 3,
			'btreesel'::regproc, 'btreenpage'::regproc
	FROM pg_am am, pg_opclass opcl, glaccount_ops_tmp c
	WHERE amname = 'btree' AND
		opcname = 'glaccount_ops' AND
		c.oprname = '=';

INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
			amopselect, amopnpages)
	SELECT am.oid, opcl.oid, c.opoid, 4,
			'btreesel'::regproc, 'btreenpage'::regproc
	FROM pg_am am, pg_opclass opcl, glaccount_ops_tmp c
	WHERE amname = 'btree' AND
		opcname = 'glaccount_ops' AND
		c.oprname = '>=';

INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
			amopselect, amopnpages)
	SELECT am.oid, opcl.oid, c.opoid, 5,
			'btreesel'::regproc, 'btreenpage'::regproc
	FROM pg_am am, pg_opclass opcl, glaccount_ops_tmp c
	WHERE amname = 'btree' AND
		opcname = 'glaccount_ops' AND
		c.oprname = '>';

INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum)
	SELECT a.oid, b.oid, c.oid, 1
		FROM pg_am a, pg_opclass b, pg_proc c
		WHERE a.amname = 'btree' AND
			b.opcname = 'glaccount_ops' AND
			c.proname = 'glaccount_cmp';

INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
			amopselect, amopnpages)
	SELECT am.oid, opcl.oid, c.opoid, 1,
			'hashsel'::regproc, 'hashnpage'::regproc
	FROM pg_am am, pg_opclass opcl, glaccount_ops_tmp c
	WHERE amname = 'hash' AND
		opcname = 'glaccount_ops' AND
		c.oprname = '=';

INSERT INTO pg_description (objoid, description)
	SELECT oid, 'Two part G/L account'
		FROM pg_type WHERE typname = 'glaccount';

--
--	eof
--

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Wed, 26 May 1999 08:08:47 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Help: fmgr_info: function 0: cache lookup failed
>
> I still can't get this type creation working.  I get the subject error
> whenever I try to select on the new type if it is indexed.  Here is a sample.
>
> darcy=> create table x (g glaccount, i int);
> CREATE
> darcy=> insert into x values ('12345-0000', 1);
> INSERT 29124 1
> darcy=> select * from x where g = '12345-0000';
>          g|i
> ----------+-
> 12345-0000|1
> (1 row)
>
> darcy=> create unique index y on x (g);
> CREATE
> darcy=> select * from x where g = '12345-0000';
> ERROR:  fmgr_info: function 0: cache lookup failed
>
> As you can see, the select worked until I added the index.  Here is the
> SQL that created the glaccount type.  I hope to rewrite the documentation
> based on this but I need to get it working first.  Any ideas?

    I can only guess - in contrast to the builtin operators, user
    created ones don't specify the index  selectivity  functions.
    Maybe you need to manipulate the pg_operator entries manually
    to be able to create indices too. AFAICS there  is  no  check
    made on the fmgr call in selfuncs.c.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Wed, 26 May 1999 15:48:52 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Help: fmgr_info: function 0: cache lookup failed
darcy@druid.net ("D'Arcy" "J.M." Cain) writes:
> darcy=> select * from x where g = '12345-0000';
> ERROR:  fmgr_info: function 0: cache lookup failed

> As you can see, the select worked until I added the index.

This is a bit of a reach, but maybe it would work if you added
commutator links to your operator definitions?  You should add 'em
anyway on general principles.

If that *does* fix it, I'd say it's still a bug; index operators
should not have to have commutator links.

Next step would be to burrow in with a debugger and figure out what
function the thing thinks it's trying to call.  A backtrace from
the call to elog() would help here.

			regards, tom lane

Wed, 26 May 1999 11:06:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Help: fmgr_info: function 0: cache lookup failed 
Thus spake Jan Wieck
> > darcy=> select * from x where g = '12345-0000';
> > ERROR:  fmgr_info: function 0: cache lookup failed
> >
> > As you can see, the select worked until I added the index.  Here is the
> > SQL that created the glaccount type.  I hope to rewrite the documentation
> > based on this but I need to get it working first.  Any ideas?
> 
>     I can only guess - in contrast to the builtin operators, user
>     created ones don't specify the index  selectivity  functions.
>     Maybe you need to manipulate the pg_operator entries manually
>     to be able to create indices too. AFAICS there  is  no  check
>     made on the fmgr call in selfuncs.c.

I tried just setting oprcanhash to true but that didn't do it.  Can
you suggest what fields I need to look at in pg_operator?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Wed, 26 May 1999 22:19:42 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] Help: fmgr_info: function 0: cache lookup failed
Thus spake Tom Lane
> darcy@druid.net ("D'Arcy" "J.M." Cain) writes:
> > darcy=> select * from x where g = '12345-0000';
> > ERROR:  fmgr_info: function 0: cache lookup failed
> 
> > As you can see, the select worked until I added the index.
> 
> This is a bit of a reach, but maybe it would work if you added
> commutator links to your operator definitions?  You should add 'em
> anyway on general principles.

What are commutator links and how do I add them?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Wed, 26 May 1999 22:20:56 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] Help: fmgr_info: function 0: cache lookup failed
"D'Arcy" "J.M." Cain <darcy@druid.net> writes:
>> This is a bit of a reach, but maybe it would work if you added
>> commutator links to your operator definitions?  You should add 'em
>> anyway on general principles.

> What are commutator links and how do I add them?

There's some doco in xoper.sgml now...

			regards, tom lane

Thu, 27 May 1999 00:10:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Help: fmgr_info: function 0: cache lookup failed 
> 
> Thus spake Jan Wieck
> > > darcy=> select * from x where g = '12345-0000';
> > > ERROR:  fmgr_info: function 0: cache lookup failed
> > >
> > > As you can see, the select worked until I added the index.  Here is the
> > > SQL that created the glaccount type.  I hope to rewrite the documentation
> > > based on this but I need to get it working first.  Any ideas?
> > 
> >     I can only guess - in contrast to the builtin operators, user
> >     created ones don't specify the index  selectivity  functions.
> >     Maybe you need to manipulate the pg_operator entries manually
> >     to be able to create indices too. AFAICS there  is  no  check
> >     made on the fmgr call in selfuncs.c.
> 
> I tried just setting oprcanhash to true but that didn't do it.  Can
> you suggest what fields I need to look at in pg_operator?

    oprrest and oprjoin


Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #


Thu, 27 May 1999 10:34:53 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Help: fmgr_info: function 0: cache lookup failed
Thus spake Jan Wieck
> > I tried just setting oprcanhash to true but that didn't do it.  Can
> > you suggest what fields I need to look at in pg_operator?
> 
>     oprrest and oprjoin

OK, I did this and it worked.  I'll go work on the documentation now.
Thanks.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Thu, 27 May 1999 07:16:44 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] Help: fmgr_info: function 0: cache lookup failed
"D'Arcy" "J.M." Cain <darcy@druid.net> writes:
>>>> I tried just setting oprcanhash to true but that didn't do it.  Can
>>>> you suggest what fields I need to look at in pg_operator?
>> 
>> oprrest and oprjoin

> OK, I did this and it worked.  I'll go work on the documentation now.

OK, I see the problem: btreesel() and friends blithely assume that the
operator used in an index will have a selectivity function (oprrest).

I can see two reasonable fixes:
  * Default to an 0.5 estimate if no oprrest link (this is what the
    optimizer does for operators that have no oprrest).
  * Generate an error message along the lines of "index operators must
    have a restriction selectivity estimator", if we think that they
    really really oughta.

I'm not sure which way to jump.  The former would be more friendly for
people just starting to develop index support for a new data type ...
but then they might never realize that lack of an estimator is hurting
performance for them.  Comments?

			regards, tom lane

Thu, 27 May 1999 09:56:56 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Help: fmgr_info: function 0: cache lookup failed 

Tom, was this dealth with?

> "D'Arcy" "J.M." Cain <darcy@druid.net> writes:
> >>>> I tried just setting oprcanhash to true but that didn't do it.  Can
> >>>> you suggest what fields I need to look at in pg_operator?
> >> 
> >> oprrest and oprjoin
> 
> > OK, I did this and it worked.  I'll go work on the documentation now.
> 
> OK, I see the problem: btreesel() and friends blithely assume that the
> operator used in an index will have a selectivity function (oprrest).
> 
> I can see two reasonable fixes:
>   * Default to an 0.5 estimate if no oprrest link (this is what the
>     optimizer does for operators that have no oprrest).
>   * Generate an error message along the lines of "index operators must
>     have a restriction selectivity estimator", if we think that they
>     really really oughta.
> 
> I'm not sure which way to jump.  The former would be more friendly for
> people just starting to develop index support for a new data type ...
> but then they might never realize that lack of an estimator is hurting
> performance for them.  Comments?
> 
> 			regards, tom lane
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 7 Jul 1999 17:32:45 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Help: fmgr_info: function 0: cache lookup failed
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Tom, was this dealth with?

What I originally did was the second choice (generate an error message)
but I had to back off to using a default when we discovered that the
rtree index operators don't have oprrest links in 6.5 :-(.  I would
like to change it back after the rtree index entries are fixed, but
for the meanwhile you can mark this item done.

			regards, tom lane


>> "D'Arcy" "J.M." Cain <darcy@druid.net> writes:
>>>>>>> I tried just setting oprcanhash to true but that didn't do it.  Can
>>>>>>> you suggest what fields I need to look at in pg_operator?
>>>>> 
>>>>> oprrest and oprjoin
>> 
>>>> OK, I did this and it worked.  I'll go work on the documentation now.
>> 
>> OK, I see the problem: btreesel() and friends blithely assume that the
>> operator used in an index will have a selectivity function (oprrest).
>> 
>> I can see two reasonable fixes:
>> * Default to an 0.5 estimate if no oprrest link (this is what the
>> optimizer does for operators that have no oprrest).
>> * Generate an error message along the lines of "index operators must
>> have a restriction selectivity estimator", if we think that they
>> really really oughta.
>> 
>> I'm not sure which way to jump.  The former would be more friendly for
>> people just starting to develop index support for a new data type ...
>> but then they might never realize that lack of an estimator is hurting
>> performance for them.  Comments?
>> 
>> regards, tom lane

Wed, 07 Jul 1999 18:06:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Help: fmgr_info: function 0: cache lookup failed 