Thread: 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?

--
--    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 opaqueas '/usr/local/pgsql/modules/glaccount.so'language 'c';

create function glaccount_out(opaque)returns opaqueas '/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 intas '/usr/local/pgsql/modules/glaccount.so'language 'c';

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

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

--
--    The various boolean tests:
--

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

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

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

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

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

create function glaccount_ge(glaccount, glaccount)returns boolas '/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.oprnameINTO TEMP TABLE glaccount_ops_tmpFROM pg_operator o, pg_type tWHERE 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'::regprocFROM pg_am am, pg_opclass opcl, glaccount_ops_tmp cWHERE
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'::regprocFROM pg_am am, pg_opclass opcl, glaccount_ops_tmp cWHERE
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'::regprocFROM pg_am am, pg_opclass opcl, glaccount_ops_tmp cWHERE
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'::regprocFROM pg_am am, pg_opclass opcl, glaccount_ops_tmp cWHERE
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'::regprocFROM pg_am am, pg_opclass opcl, glaccount_ops_tmp cWHERE
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_procc    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'::regprocFROM pg_am am, pg_opclass opcl, glaccount_ops_tmp cWHERE
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.

>
> 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) #

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

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.

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.

"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

>
> 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) #

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.

"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
operatorsthat have no oprrest). * Generate an error message along the lines of "index operators must   have a
restrictionselectivity 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

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,
Pennsylvania19026
 

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