CODEWITHSUNDEEP
postgresql
RootOfProblem
RootOfProblem

Reputation: 377

Postgres drop type XX000 "cache lookup failed for type"

I'm currently working on a PostgreSQL 9.2.x Database with a lots of Clients, and tables and functions. We deploy code constantly and sometimes it is necessary to even drop a type or a function due this deployment.

Example:

1.Script to create the needed functions in the first place

CREATE TYPE tmp._myEnum AS ENUM ('OLD', 'NEW', 'BOTH'); 

CREATE OR REPLACE FUNCTION tmp._get_status()
RETURNS tmp._myEnum AS
$BODY$
BEGIN
    RETURN 'OLD'::tmp._myEnum;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 10;


CREATE OR REPLACE FUNCTION tmp._my_testfunction()
RETURNS VOID AS
$BODY$
BEGIN
    CASE tmp._get_status()
        WHEN 'OLD'::tmp._myEnum THEN 
            RAISE INFO 'myEnum is OLD';
        WHEN 'NEW'::tmp._myEnum THEN 
            RAISE INFO 'myEnum is NEW';
        WHEN 'BOTH'::tmp._myEnum THEN 
            RAISE INFO 'myEnum is BOTH';
        ELSE
            RAISE INFO 'myEnum has an unexpected value';
    END CASE;

    FOR i IN 1..10 LOOP
        RAISE INFO 'Step [%]',i;
    END LOOP;
    RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 10;

2.Scenario that leads to the exception:

a)One Client is constantly using tmp._my_testfunction() like this

SELECT tmp._my_testfunction()

b)In order to deploy a change to the Composite type i execute in another session 

DROP FUNCTION IF EXISTS tmp._get_status();
DROP TYPE IF EXISTS tmp._myEnum;

CREATE TYPE tmp._myEnum AS ENUM ('OLD', 'NEW', 'BOTH','NOTHING'); 

CREATE OR REPLACE FUNCTION tmp._get_status()
RETURNS tmp._myEnum AS
$BODY$
BEGIN
    RETURN 'OLD'::tmp._myEnum;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 10;

c)The Client that is constantly using tmp._my_testfunction() imidiatly throws

ERROR:  cache lookup failed for type 386318
CONTEXT:  PL/pgSQL function tmp._my_testfunction() line 3 at CASE

How can I prevent that?

Upvotes: 6

Views: 15250

Answers (3)

Mark
Mark

Reputation: 6445

A pretty untechnical solution...

I had my app deployed on AWS and remotely logged into PSQL, dropped the database, reimported and was presented with the above.

Redeploying the application resolved it, why I'm not sure.

Upvotes: 1

RootOfProblem
RootOfProblem

Reputation: 377

Since we couldn't find another way to work around this Problem we've implemented a solution that notifies all other connections to refresh itself by using LISTEN and NOTIFY commands. So all Clients are listening on specific channels for a command that tells them to reconnect. The command ist then send in the same Transaction that dropes the type.

Upvotes: 1

Patrick
Patrick

Reputation: 32161

What seems to be the case here is a race condition. Your clients calls tmp._my_test_function() and while that is being set up by interpreting the source code of the function, you drop the function and the enum type and then re-create them. Internally, virtually all objects are referenced by their oid (with a value of 386318 for the type in your case) so the function and the enum in your client function resolve to oids. If the interpreter resolves the function and the enum to two oids and you then drop the function and the enum and re-create them, the old oids disappear and your newly created function and enum get different oids. New invocations of the function will work because the interpreter finds the new oids for the function and the enum.

A solution in a scenario like this would be to REVOKE EXECUTE on the client function, make the changes and then GRANT EXECUTE the privileges again. On the client side you would have to deal with privilege errors by retrying the function call until you succeed.

In general, you would deploy new code on a production server during low-traffic periods and/or through session management if you want to avoid the overhead of retrying function calls.

Upvotes: 2

Related Questions