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
