I've been experiencing this same problem and think I've finally determined
cause.
In our case, this happens after restoring a database with the script that
results from a pg_dumpall with "clean" option. If we have any triggers
that call functions, the tirggering SQL will always result in a "Cache
Lookup Error". Dropping and re-adding the triggers and/or called functions
did not seem to make a difference. However, finally looked deeper into the
schema creation script created by pg_dump:
\connect - postgres
--
-- TOC Entry ID 111 (OID 129228)
--
-- Name: "plpgsql_call_handler" () Type: FUNCTION Owner: postgres
--
CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS
'/usr/lib/pgsql//plp
gsql.so', 'plpgsql_call_handler' LANGUAGE 'C';
--
-- TOC Entry ID 112 (OID 129229)
--
-- Name: plpgsql Type: PROCEDURAL LANGUAGE Owner:
--
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler"
LANC
OMPILER 'PL/pgSQL';
What this shows is that when restoring to a clean database, the script
tries first to create a function and then declare the language used by the
function. Things shoudl be the other way around, should they not?!?
Thus it is not my own triggers and or functions that have broken references
but rather the underlying function handling components where the call
handler function has a broken reference to the language handler.
Would this not be a defect in pg_dump?
For us, the solution was to drop all triggers, functions and then the
language itself via cmdline droplang followed by a cmdline createlang.
With this done, we could add back our functions and triggers.
Marc Mitchell
Enterprise Information Solutions, Inc.
marcm@eisolution.com
----- Original Message -----
From: "Dave Page" <dpage@vale-housing.co.uk>
To: "Ramya Asokan" <ramya.asokan@wdc.greenpeace.org>;
<pgsql-admin@postgresql.org>; <pgadmin-support@postgresql.org>
Sent: Friday, May 24, 2002 4:27 PM
Subject: Re: [ADMIN] [pgadmin-support] Cache lookup error
>
>
> > -----Original Message-----
> > From: Ramya Asokan [mailto:ramya.asokan@wdc.greenpeace.org]
> > Sent: 24 May 2002 21:01
> > To: pgsql-admin@postgresql.org; pgadmin-support@postgresql.org
> > Subject: [pgadmin-support] Cache lookup error
> >
> >
> > Hi,
> > I created a function and a trigger - I then dropped them and
> > created the same function and trigger with a different name,
> > but i keep getting the error : cache lookup failed. I don't
> > know what this means, and would be very grateful if you can
> > help me out.
>
> This error may occur if you dropped your function, but not the trigger
> (or the trigger failed to drop for some reason). The trigger is still
> firing but the cache lookup of the function is failing because it has
> gone. This could also happen if your function is dependent on another
> function that has since been dropped.
>
> I would suggest first checking that the original trigger was dropped
> correctly(and dropping it if not), and if that doesn't solve the
> problem, dropping and recreating all the objects again, making sure that
> all the dependencies are satisfied.
>
> Regards, Dave.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
