Bug #608: cache lookup failed
Laurent Faillie (l_faillie@yahoo.com) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
cache lookup failed
Long Description
Hi all,
I have just upgraded my 1.7.3 database to 7.2, following documentation's instruction (pg_dumpall + psql < dumpfile).
All is working Ok but a call to an PL/SQL function which fails w/ the following code.
ERROR: fmgr_info: function 16594: cache lookup failed
I have tryed to drop and recreate this function but it fails again.
I need some help because this problem blocks all my production as most of my application depend on it :-(
I'm working on an HP-UX 11.0 box and postgresql was compiled using GCC
2.95.3
Thank for your help
- Laurent
Sample Code
CREATE TABLE disk_toppage_stats (
machine TEXT,
jour DATE,
pourcent SMALLINT DEFAULT 0,
level SMALLINT DEFAULT 0
);
CREATE FUNCTION upd_disk_toppage_stats() RETURNS text AS '
DECLARE
res RECORD;
res2 RECORD;
res_lmt RECORD;
nv_vert CONSTANT SMALLINT := 0;
nv_jaune CONSTANT SMALLINT := 1;
nv_rouge CONSTANT SMALLINT := 2;
-- Default value for levels
def_lmt_jaune CONSTANT SMALLINT := 80;
def_lmt_rouge CONSTANT SMALLINT := 90;
lmt_jaune SMALLINT;
lmt_rouge SMALLINT;
level SMALLINT;
BEGIN
FOR res IN select * from disk_toppage_stats LOOP
-- RAISE NOTICE ''machine = %, jour = %'',res.machine,res.jour;
--
-- Update the maximum percentage for each machine.
--
EXECUTE ''UPDATE disk_toppage_stats SET pourcent=(select max(pourcent) FROM disk_space WHERE machine='' || quote_literal(res.machine) || '' AND jour='' || quote_literal(res.jour) || '') WHERE machine='' || quote_literal(res.machine);
-- RAISE NOTICE ''------ machine=% -----'', res.machine;
level := nv_vert;
FOR res2 IN
select * from disk_space
where machine=res.machine
and jour=(select jour from disk_toppage_stats where machine=res.machine)
LOOP
-- RAISE NOTICE ''fs = %, prc = %'',res2.fs,res2.pourcent;
lmt_jaune := def_lmt_jaune;
lmt_rouge := def_lmt_rouge;
SELECT INTO res_lmt * FROM disk_limit
WHERE machine=res.machine AND fs=res2.fs;
IF FOUND THEN
-- RAISE NOTICE ''****** TROUVE ! ******'';
lmt_jaune := res_lmt.limite_jaune;
lmt_rouge := res_lmt.limite_rouge;
END IF;
IF level = nv_vert THEN
IF res2.pourcent >= lmt_jaune THEN
level := nv_jaune;
END IF;
END IF;
IF res2.pourcent >= lmt_rouge THEN
level := nv_rouge;
EXIT;
END IF;
END LOOP;
EXECUTE ''UPDATE disk_toppage_stats SET level='' || quote_literal(level) || '' WHERE machine='' || quote_literal(res.machine);
--RAISE NOTICE ''on est sortie avec un niveau %'',level;
END LOOP;
RETURN ''ok'';
END;
' LANGUAGE 'plpgsql';
No file was uploaded with this report
pgsql-bugs@postgresql.org writes:
All is working Ok but a call to an PL/SQL function which fails w/ the following code.
ERROR: fmgr_info: function 16594: cache lookup failed
Most likely this means something happened to the call handler function
for plpgsql. Look in pg_language to see the "lanplcallfoid" value for
plpgsql; is it 16594? Do you have an entry in pg_proc for
"plpgsql_call_handler", and if so what does it look like?
regards, tom lane
Hi all,
Oups, I forgot to put the ML in copy of my reply :-(
[...]
ERROR: fmgr_info: function 16594: cache lookup
failed
Most likely this means something happened to the
call handler function
for plpgsql. Look in pg_language to see the
"lanplcallfoid" value for
plpgsql; is it 16594?
Yes :
scheduling=# select lanplcallfoid from pg_language
where lanname = 'plpgsql';
lanplcallfoid
---------------
16594
(1 row)
Do you have an entry in
pg_proc for
"plpgsql_call_handler", and if so what does it look
like?
scheduling=# select * from pg_proc where
proname='plpgsql_call_handler';
proname | proowner | prolang | proisinh
| proistrusted | proiscachable | proisstrict |
pronargs | proretset | prorettype | proargtypes |
probyte_pct | pro
perbyte_cpu | propercall_cpu | prooutin_ratio |
prosrc | probin
----------------------+----------+---------+----------+--------------+---------------+-------------+----------+-----------+------------+-------------+-------------+----
------------+----------------+----------------+----------------------+---------------------------------
plpgsql_call_handler | 1 | 13 | f
| t | f | f |
0 | f | 0 | | 100
|
0 | 0 | 100 |
plpgsql_call_handler | /usr/local/pgsql/lib/plpgsql.sl
Please note I have remove the old binary and stop all
processes using it (Apache/PHP) before doing the
installation of the new release.
regards, tom lane
Regards, Laurent
PS: Sorry Tom for my duplicate sending
=====
The misspelling master is on the Web.
_________ 100 % Dictionnary Free !
/ /(
/ Dico / / Pleins d'autres fautes sur
/________/ / http://go.to/destroyedlolo
(#######( /
Quoi, des fautes d'orthographe! Pas possible ;-D.
___________________________________________________________
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en fran�ais !
Yahoo! Mail : http://fr.mail.yahoo.com
=?iso-8859-1?q?Laurent=20FAILLIE?= <l_faillie@yahoo.com> writes:
scheduling=# select * from pg_proc where
proname='plpgsql_call_handler';
proname | proowner | prolang | proisinh
| proistrusted | proiscachable | proisstrict |
pronargs | proretset | prorettype | proargtypes |
probyte_pct | pro
perbyte_cpu | propercall_cpu | prooutin_ratio |
prosrc | probin
----------------------+----------+---------+----------+--------------+---------------+-------------+----------+-----------+------------+-------------+-------------+----
------------+----------------+----------------+----------------------+---------------------------------
plpgsql_call_handler | 1 | 13 | f
| t | f | f |
0 | f | 0 | | 100
|
0 | 0 | 100 |
plpgsql_call_handler | /usr/local/pgsql/lib/plpgsql.sl
Well, that looks reasonable, but what's its OID? (should've asked for
select oid,* from ...)
The easiest way to get back to a working database is to UPDATE the
pg_language row with the correct OID of the call handler function.
I'd be interested to know how you got into this state, though.
I have to think that you dropped and recreated the handler function
without going through the full 'droplang'/'createlang' cycle.
regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> a �crit�:Well, that looks reasonable, but what's its OID?
(should've asked for
select oid,* from ...)
scheduling=# select oid, * from pg_proc where
proname='plpgsql_call_handler';
oid | proname | proowner | prolang |
proisinh | proistrusted | proiscachable | proisstrict
| pronargs | proretset | prorettype | proargtypes |
probyte_
pct | properbyte_cpu | propercall_cpu | prooutin_ratio
| prosrc | probin
--------+----------------------+----------+---------+----------+--------------+---------------+-------------+----------+-----------+------------+-------------+---------
----+----------------+----------------+----------------+----------------------+---------------------------------
374578 | plpgsql_call_handler | 1 | 13 |
f | t | f | f
| 0 | f | 0 | |
100 | 0 | 0 | 100
| plpgsql_call_handler |
/usr/local/pgsql/lib/plpgsql.sl
The easiest way to get back to a working database is
to UPDATE the
pg_language row with the correct OID of the call
handler function.
Hum, so I may try
update pg_language set lanplcallfoid=374578 where
lanname='plpgsql';
Is it correct ?
I'd be interested to know how you got into this
state, though.
I have to think that you dropped and recreated the
handler function
without going through the full
'droplang'/'createlang' cycle.
Hum ... no.
As first step, I have removed my data directory (rm
-rf /postgres/scheduling), the init the new database
(initdb -D /postgres/scheduling) and then, reimport
old data.
As I saw this error, I've removed PLSQL (drop
procedural language plsql;) and I've recreate it ...
but w/ the same result.
That seems very strange to me, is I've done the same
steps on my test invironment (under HP-UX 10.20 this
time) w/o any problem :-(
I'm hoping it's only a mistake on my own and not an
HP-UX 11.0 issu ...
regards, tom lane
Bye,
Laurent
=====
The misspelling master is on the Web.
_________ 100 % Dictionnary Free !
/ /(
/ Dico / / Pleins d'autres fautes sur
/________/ / http://go.to/destroyedlolo
(#######( /
Quoi, des fautes d'orthographe! Pas possible ;-D.
___________________________________________________________
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en fran�ais !
Yahoo! Mail : http://fr.mail.yahoo.com
=?iso-8859-1?q?Laurent=20FAILLIE?= <l_faillie@yahoo.com> writes:
The easiest way to get back to a working database is
to UPDATE the
pg_language row with the correct OID of the call
handler function.
Hum, so I may try
update pg_language set lanplcallfoid=374578 where
lanname='plpgsql';
Is it correct ?
Right.
regards, tom lane
update pg_language set lanplcallfoid=374578 where
lanname='plpgsql';Is it correct ?
Right.
Ok, it's working fine now.
Thanks you very very much.
Bye
Laurent
=====
The misspelling master is on the Web.
_________ 100 % Dictionnary Free !
/ /(
/ Dico / / Pleins d'autres fautes sur
/________/ / http://go.to/destroyedlolo
(#######( /
Quoi, des fautes d'orthographe! Pas possible ;-D.
___________________________________________________________
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en fran�ais !
Yahoo! Mail : http://fr.mail.yahoo.com
I don't know about details, but in the 7.2 PostgreSQL
documentation, at Schema Manipulation topic, says:
New CREATE OR REPLACE FUNCTIONS to alter existing
function (preserving the OID) (by Gavin Sherry)
I think that you need to use this new resource.
Juliano S. Ignacio
jsignacio@hotmail.com
--- pgsql-bugs@postgresql.org wrote:Laurent Faillie (l_faillie@yahoo.com) reports a bug
with a severity of 2
The lower the number the more severe it is.Short Description
cache lookup failed
__________________________________________________
Do You Yahoo!?
Try FREE Yahoo! Mail - the world's greatest free email!
http://mail.yahoo.com/
Hello!
On Thu, 7 Mar 2002, Tom Lane wrote:
[skip]
The easiest way to get back to a working database is to UPDATE the
pg_language row with the correct OID of the call handler function.
I'd be interested to know how you got into this state, though.
pg_dumpall|psql -p5454 ?8) typical mistake, should be in FAQ mentioned
below. IMHO.
--
WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group.
Phone: +7 (3832) 106228, ext.140, E-mail: byg@center-f1.ru.
Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.