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

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

"Chris Fischer" <Chris(dot)Fischer(at)channeladvisor(dot)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