Re: How to recover from : "Cache lookup failed for rela

The error happens when I try to create other temporary tables.
Here is a part of my Server Log :

ERROR: cache lookup failed for relation 463558
CONTEXT: SQL statement "CREATE TEMP TABLE Tmp_MsgRxOrder ( EventId int4,
CodeRef int4 )"
PL/pgSQL function "adm_ne" line 248 at SQL statement
STATEMENT: SELECT ALIS.ADM_NE()

From what I understand, PostgreSQL is creating new temporary schemas or
using existing one when I try to create a temporary table. The problem
occurs when PostrgreSQL tries to use the pg_temp_4 schema.
The oid of the tmp_d_alarm type is 463559.

The pg_type table contains a record with relid = 463558 :

typname | typnamespace | typowner | typlen | typbyval | typtype |
typisdefined | typdelim | typrelid | typelem | typinput | typoutput |
typreceive | typsend | typanalyze | typalign | typstorage | typnotnull
| typbasetype | typtypmod | typndims | typdefaultbin | typdefault
-------------+--------------+----------+--------+----------+---------+------
--------+----------+----------+---------+-----------+------------+----------
---+-------------+------------+----------+------------+------------+--------
-----+-----------+----------+---------------+------------
tmp_d_alarm | 440012 | 104 | -1 | f | c | t
| , | 463558 | 0 | record_in | record_out | record_recv |
record_send | - | d | x | f | 0
| -1 | 0 | |

This table was created inside a function with the following SQL :
CREATE TEMPORARY TABLE Tmp_D_Alarm
(
D_AlarmID int4 NOT NULL,
D_RAlarmID int4 NOT NULL,
D_PerceivedSeverity int4 NOT NULL,
D_ObjRef int4 NOT NULL,
D_ObjOwnerRef int4 NOT NULL,
D_ObjClassTag varchar(5) NOT NULL,
L_LogRecId int4 NULL
);

There is no DROP command as this function is invoked from a connexion which
is closed after calling this function. I guess that the temp table will be
deleted as this is a temporary table.

I tried the following :
- REINDEX DATABASE alis;
- DROP SCHEMA pg_temp_4 ( CASCADE )
- from the shell : reindexdb alis
- Tried to stop/restart PostgreSQL,
- vacuum

Patrick

----------------------------------------------------------------------------
---------------
Patrick Fiche
email : patrick(dot)fiche(at)aqsacom(dot)com
tél : 01 69 29 36 18
----------------------------------------------------------------------------
---------------

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: lundi 23 mai 2005 15:18
To: Patrick(dot)FICHE(at)AQSACOM(dot)COM
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] How to recover from : "Cache lookup failed for
relation "

Patrick(dot)FICHE(at)AQSACOM(dot)COM writes:
> I have a server installed with Postgres 8.0.2 on Solaris.
> Since two days, I get the error : "ERROR: cache lookup failed for =
> relation
> 463558"

What SQL command(s) produce that error, exactly?

> When I look with the pgAdmin at my database, I see that I have in =
> pg_temp_4
> schema, a type called "tmp_d_alarm" which is the name of a temporary =
> table
> that I'm using in functions....
> The table of the same name is not in the schema and I suspect that this
> remaining type is causing the trouble.

Possibly. Does the pg_type.typrelid field of that entry contain 463558?
How are you creating and dropping these temporary tables, exactly?

> How to get read of this type. I'm not able to drop it, neither the =
> schema...

What did you try, and what were the results?

regards, tom lane