Postgres drop type XX000 "cache lookup failed for type"
postgresql
Solution
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.
Problem
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?