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.fiche@aqsacom.com
tél : 01 69 29 36 18
----------------------------------------------------------------------------
---------------




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


Patrick.FICHE@AQSACOM.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