Help: fmgr_info: function 0: cache lookup failed

Started by J.M.over 26 years ago11 messages

J.M.

darcy@druid.net

over 26 years ago

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.

Noname

wieck@debis.com

over 26 years ago

In reply to: J.M. (#1)

Re: [HACKERS] 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) #

Tom Lane

tgl@sss.pgh.pa.us

over 26 years ago

In reply to: Noname (#2)

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

Import Notes

Reply to msg id not found: Yourmessageof26May1999080855-0400m10mcUJ-0000bIC@druid.net | Resolved by subject fallback

J.M.

darcy@druid.net

over 26 years ago

In reply to: Noname (#2)

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.

J.M.

darcy@druid.net

over 26 years ago

In reply to: Tom Lane (#3)

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.

Tom Lane

tgl@sss.pgh.pa.us

over 26 years ago

In reply to: J.M. (#5)

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

Import Notes

Reply to msg id not found: YourmessageofWed26May1999222056-0400m10mpmz-0000bIC@druid.net | Resolved by subject fallback

Noname

wieck@debis.com

over 26 years ago

In reply to: J.M. (#4)

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

J.M.

darcy@druid.net

over 26 years ago

In reply to: Noname (#7)

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.

Tom Lane

tgl@sss.pgh.pa.us

over 26 years ago

In reply to: J.M. (#8)

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

Import Notes

Reply to msg id not found: YourmessageofThu27May1999071644-0400m10my9V-0000bIC@druid.net | Resolved by subject fallback

#10

Bruce Momjian

maillist@candle.pha.pa.us

over 26 years ago

In reply to: Tom Lane (#9)

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

#11

Tom Lane

tgl@sss.pgh.pa.us

over 26 years ago

In reply to: Bruce Momjian (#10)

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

Show quoted text

"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

Import Notes

Reply to msg id not found: YourmessageofWed7Jul1999173245-0400199907072132.RAA11182@candle.pha.pa.us | Resolved by subject fallback