1

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.