PostgreSQL error cache lookup failed for relation - what causes it and why?

Question

I have a query that is erroring out when attempting to create a temporary table. The query is:

CREATE TEMPORARY TABLE temp_table_t (LIKE original_table INCLUDING INDEXES)

The error being returned is:

postgres7 error: [-1: ERROR: cache lookup failed for relation 14026125] in EXECUTE("CREATE TEMPORARY TABLE temp_table_t (LIKE original_table INCLUDING INDEXES)")

I found two threads on the PostgreSQL forums but they didn't explain much, unfortunately:

http://archives.postgresql.org/pgsql-performance/2010-04/msg00026.php

http://archives.postgresql.org/pgsql-performance/2010-04/msg00028.php

This query runs on other servers just fine, it is failing specifically on only one of our servers. Is there any insight you can give me as to what is causing the error and how to fix it? I first thought that it was the result of creating a temporary table name with the same name of an existing temporary table. I updated my code to create a randomly named temporary table each time, and that did not work either.

Answer 1

I was dropping a schema and I had this error "lookup failed for relation xxxx". There was the name of a table also. It seemed some tables from the schema I was deleting, were in a strange state. At the end we did this third steps.

1. We obtain the C.oid of the table.

   select c.oid
   from pg_class  c
   join pg_namespace n on n.oid=c.relnamespace
   where c.relname = 'MY_TABLE_NAME'
       and n.nspname='MY_SCHEMA_NAME';
   

2. Next:

   delete from pg_class where oid = MY_OID;
   delete from pg_depend where objid = MY_OID;
   delete from pg_constraint where conrelid = MY_OID;
   

3. Finally:

   drop schema MY_SCHEMA_NAME cascade
   

Then appeared another similar error with another table and we repeated steps 1 to 3 until we deleted the schema.

Answer 2

I ran into the same issue when creating a temporary table from a view, similar to what you are doing here:

create temporary table temp_table_t (LIKE original_table including indexes);

I also had the same weird experience that it was only occurring with on only one of our servers.

For me it was as simple as replacing the original_table (which was a view for me) with the

create or replace view my_view as (  
    // my view definition
);

syntax. After that, the process that I was using to create temporary tables did not give any errors.

I don't know what you can do with your table, as it is not as simple as re-defining a view. If you can afford to lose the data, you can try dropping the table and re-creating it.