Horton

Thread

  1. Bug #608: cache lookup failed

    PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2002-03-06T14:33:10Z

    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

  2. Re: Bug #608: cache lookup failed

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-03-06T18:35:49Z

    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

  3. Re: Bug #608: cache lookup failed

    Laurent FAILLIE <l_faillie@yahoo.com> — 2002-03-07T10:47:08Z

    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

  4. Re: Bug #608: cache lookup failed

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-03-07T15:05:42Z

    =?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

  5. Re: Bug #608: cache lookup failed

    Laurent FAILLIE <l_faillie@yahoo.com> — 2002-03-07T16:00:14Z

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

  6. Re: Bug #608: cache lookup failed

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-03-07T16:07:57Z

    =?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

  7. Re: Bug #608: cache lookup failed

    Laurent FAILLIE <l_faillie@yahoo.com> — 2002-03-07T16:36:33Z

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

  8. Re: Bug #608: cache lookup failed

    Juliano Ignacio <jsignacio@yahoo.com> — 2002-03-08T17:44:40Z

    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/

  9. Re: Bug #608: cache lookup failed

    Yury Bokhoncovich <byg@center-f1.ru> — 2002-03-11T14:23:20Z

    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.


> http://www.postgresql.org/users-lounge/docs/faq.html



-- 
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.