title: Postgres drop type XX000 "cache lookup failed for type"
body: <p>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. </p>
  
  <p>Example:</p>
  
  <p>1.Script to create the needed functions in the first place</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>2.Scenario that leads to the exception:</p>
  
  <p>a)One Client is constantly using tmp._my_testfunction() like this</p>
  
  <pre><code>SELECT tmp._my_testfunction()
  </code></pre>
  
  <p>b)In order to deploy a change to the Composite type i execute in another session </p>
  
  <pre><code>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;
  </code></pre>
  
  <p>c)The Client that is constantly using tmp._my_testfunction() imidiatly throws</p>
  
  <pre><code>ERROR:  cache lookup failed for type 386318
  CONTEXT:  PL/pgSQL function tmp._my_testfunction() line 3 at CASE
  </code></pre>
  
  <p>How can I prevent that?</p>