Re: [ADMIN] Cache lookup error

> -----Original Message-----
> From: Marc Mitchell [mailto:marcm(at)eisolution(dot)com]
> Sent: 05 June 2002 23:44
> To: Dave Page; Ramya Asokan; pgsql-admin(at)postgresql(dot)org;
> pgadmin-support(at)postgresql(dot)org
> Subject: Re: [ADMIN] [pgadmin-support] Cache lookup error
>
>
> 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?!?

The plpgsql_call_handler function is the interpreter for the language
and *must* be created before the language itself.

Regards

Dave.