Postgres Pro
Downloads
Home   >   mailing lists

Thread: "Cache lookup failed for function" when recreating procs

"Cache lookup failed for function" when recreating procs

From
"Chris Fischer"
Date:
Env: Windows XP sp2
Version: 8.2.1
 
I'm attempting to write a function which produces a script.  The script will contain steps necessary to drop/recreate all the functions.  I've got a helper function called 'dropprocsbyname' which takes a schema and a proc name, finds all matching pg_proc rows and executes 'drop function xyz' on them.
 
So the output of my 'recreate all procs' function looks like this:
 
select * from common.dropprocsbyname('common','proc1');
create or replace function common.proc1() returns bool as
$$body$$
etc
$$body$$
language 'plpgsql' volatile;
 
etc.. for each proc in the common schema.
 
I get many, but not all, errors like this:
 
"psql:e:/pgla_export.txt:1208: ERROR:  cache lookup failed for function 22542
CONTEXT:  PL/pgSQL function "dropprocsbyname" line 23 at execute statement"
 
with the same function oid each time.
 
When I rerun the script, the oid in the error changes.  So, I'm guessing that it has to do with dropping/recreating my "dropprocsbyname" function, but I cannot figure out when its being cached and how to overcome the error.
 

Chris Fischer
Database Engineer

 

Re: "Cache lookup failed for function" when recreating procs

From
Tom Lane
Date:
"Chris Fischer" <Chris.Fischer@channeladvisor.com> writes:
> I'm attempting to write a function which produces a script.  The script
> will contain steps necessary to drop/recreate all the functions.

If you're trying to modify functions that are in live use, don't drop
them.  Just apply CREATE OR REPLACE FUNCTION.  This avoids breaking
cached plans that refer to the function(s) by OID.

            regards, tom lane