Re: How to recover from : "Cache lookup failed for rela - Mailing list pgsql-general

You are perfectly right, the file still exists and here are the last lines
in the Server log before the crash occured.
Sorry for the length of this mail. Tell me if you prefer an attached file
for such traces next time...



ERROR:  duplicate key violates unique constraint
"pg_class_relname_nsp_index"
CONTEXT:  SQL statement "CREATE TEMP TABLE Tmp_MsgNeInfo ( NeRef int4 NOT
NULL, Otid int4 NULL, NbrMsgMax int4 NULL DEFAULT(0), ProtocolRef int4 NULL,
TechnoRef int4 NULL, FirstInteIndex int4 NULL DEFAULT(0), MaxInte int4 NULL
DEFAULT(1024), Destinataire varchar(60) NULL, NbrInteNe int4 NULL,
NbrFailedAttempts int4 NULL, DefaultMsgParam varchar(200) NULL )"
    PL/pgSQL function "msg_adddelget_inte" line 33 at SQL statement
    SQL statement "SELECT  ALIS.Msg_AddDelGet_Inte( 0 )"
    PL/pgSQL function "msg_adddelget_inte" line 5 at select into
variables
    SQL statement "SELECT  ALIS.MSG_ADDDELGET_INTE( )"
    PL/pgSQL function "adm_inte" line 584 at select into variables
STATEMENT:  SELECT ALIS.ADM_INTE()
ERROR:  relation "tmp_auditneactivecc" already exists
CONTEXT:  SQL statement "CREATE TEMP TABLE Tmp_AuditNeActiveCC AS SELECT
DISTINCT T1.OBJ_REF AS NeRef, T1.OBJ_ID AS NeId, T1.OBJ_REF_PARENT AS
TechnoRef, T3.OBJ_REF AS ProtocolRef, REPEAT(' ', 5) AS ParamTag, 100 AS
AuditPriority, 0 AS AuditWaitDelay, REPEAT(' ', 200) AS DefaultParam, 0 AS
FirstInteIndex, 1024 AS MaxInte FROM EASYSHARE.D_OBJECT T1,
EASYSHARE.D_PARAMETER T2, EASYSHARE.D_OBJECT T3, EASYSHARE.D_PARAMETER T4
WHERE (T1.OBJ_CLASS_TAG = '-4741') AND (SUBSTRING(T1.OBJ_STATES, 1, 2) =
'22') AND (T1.OBJ_STATUS LIKE '__11%') AND (T2.OBJ_REF = T1.OBJ_REF) AND
(T2.PARAM_TAG = '23') AND (T2.PARAM_VALUE IS NULL) AND (T3.OBJ_REF_PARENT =
T1.OBJ_REF_PARENT) AND (T3.OBJ_CLASS_TAG = '42') AND (T4.OBJ_REF =
T1.OBJ_REF) AND (T4.PARAM_TAG = '21') AND (to_timestamp(T4.PARAM_VALUE,
'yyyy/mm/dd hh24:mi:ss') - CAST( $1  || ' minutes' AS interval) < NOW())"
    PL/pgSQL function "cc_audit_ne" line 174 at SQL statement
    SQL statement "SELECT  ALIS.CC_AUDIT_NE ()"
    PL/pgSQL function "adm_check" line 101 at select into variables
STATEMENT:  SELECT ALIS.ADM_CHECK()
ERROR:  duplicate key violates unique constraint
"pg_class_relname_nsp_index"
CONTEXT:  SQL statement "CREATE TEMPORARY TABLE Tmp_AdmAlarmMsg AS SELECT *
FROM EASYSHARE.D_FLUX WHERE ( CHARINDEX(  $1 , DESTINATION ) <> 0 ) AND
(COMPONENTYPE = '91') AND ( STATUS >= 0 ) AND ( DT_TIMEOUT >= NOW() )"
    PL/pgSQL function "adm_alarm" line 132 at SQL statement
STATEMENT:  SELECT ALARM.ADM_ALARM()
ERROR:  relation 463410 deleted while still in use
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()
ERROR:  duplicate key violates unique constraint
"pg_class_relname_nsp_index"
CONTEXT:  SQL statement "CREATE TEMP TABLE Tmp_MsgNeInfo ( NeRef int4 NOT
NULL, Otid int4 NULL, NbrMsgMax int4 NULL DEFAULT(0), ProtocolRef int4 NULL,
TechnoRef int4 NULL, FirstInteIndex int4 NULL DEFAULT(0), MaxInte int4 NULL
DEFAULT(1024), Destinataire varchar(60) NULL, NbrInteNe int4 NULL,
NbrFailedAttempts int4 NULL, DefaultMsgParam varchar(200) NULL )"
    PL/pgSQL function "msg_adddelget_inte" line 33 at SQL statement
    SQL statement "SELECT  ALIS.Msg_AddDelGet_Inte( 0 )"
    PL/pgSQL function "msg_adddelget_inte" line 5 at select into
variables
    SQL statement "SELECT  ALIS.MSG_ADDDELGET_INTE( )"
    PL/pgSQL function "adm_inte" line 584 at select into variables
STATEMENT:  SELECT ALIS.ADM_INTE()
ERROR:  relation 463438 deleted while still in use
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()
ERROR:  duplicate key violates unique constraint
"pg_class_relname_nsp_index"
CONTEXT:  SQL statement "CREATE TEMP TABLE Tmp_MsgNeInfo ( NeRef int4 NOT
NULL, Otid int4 NULL, NbrMsgMax int4 NULL DEFAULT(0), ProtocolRef int4 NULL,
TechnoRef int4 NULL, FirstInteIndex int4 NULL DEFAULT(0), MaxInte int4 NULL
DEFAULT(1024), Destinataire varchar(60) NULL, NbrInteNe int4 NULL,
NbrFailedAttempts int4 NULL, DefaultMsgParam varchar(200) NULL )"
    PL/pgSQL function "msg_adddelget_inte" line 33 at SQL statement
    SQL statement "SELECT  ALIS.Msg_AddDelGet_Inte( 0 )"
    PL/pgSQL function "msg_adddelget_inte" line 5 at select into
variables
    SQL statement "SELECT  ALIS.MSG_ADDDELGET_INTE( )"
    PL/pgSQL function "adm_inte" line 584 at select into variables
STATEMENT:  SELECT ALIS.ADM_INTE()
ERROR:  duplicate key violates unique constraint
"pg_class_relname_nsp_index"
CONTEXT:  SQL statement "CREATE TEMPORARY TABLE Tmp_AdmAlarmMsg AS SELECT *
FROM EASYSHARE.D_FLUX WHERE ( CHARINDEX(  $1 , DESTINATION ) <> 0 ) AND
(COMPONENTYPE = '91') AND ( STATUS >= 0 ) AND ( DT_TIMEOUT >= NOW() )"
    PL/pgSQL function "adm_alarm" line 132 at SQL statement
STATEMENT:  SELECT ALARM.ADM_ALARM()
ERROR:  duplicate key violates unique constraint
"pg_class_relname_nsp_index"
CONTEXT:  SQL statement "CREATE TEMP TABLE Tmp_MsgNeInfo ( NeRef int4 NOT
NULL, Otid int4 NULL, NbrMsgMax int4 NULL DEFAULT(0), ProtocolRef int4 NULL,
TechnoRef int4 NULL, FirstInteIndex int4 NULL DEFAULT(0), MaxInte int4 NULL
DEFAULT(1024), Destinataire varchar(60) NULL, NbrInteNe int4 NULL,
NbrFailedAttempts int4 NULL, DefaultMsgParam varchar(200) NULL )"
    PL/pgSQL function "msg_adddelget_inte" line 33 at SQL statement
    SQL statement "SELECT  ALIS.Msg_AddDelGet_Inte( 0 )"
    PL/pgSQL function "msg_adddelget_inte" line 5 at select into
variables
    SQL statement "SELECT  ALIS.MSG_ADDDELGET_INTE( )"
    PL/pgSQL function "adm_inte" line 584 at select into variables
STATEMENT:  SELECT ALIS.ADM_INTE()
ERROR:  relation with OID 463479 does not exist
CONTEXT:  SQL statement "INSERT INTO Tmp_AuditInte(InteRef, InteRefParent,
DynamicFlag, SessionFlag, InteStatus, InteStart,
                                     InteStop, InteDelete, Liid,
WarrantId, OwnerRef, SessionNumber,
                                     GetInfoServerList,
TriggerServerList)
             SELECT T1.INTE_REF, 0,
                    CASE
                      WHEN T1.INTE_TYPE = 'DYNAMIC' THEN 2
                      WHEN T1.INTE_TYPE = 'STATIC' THEN 1
                      ELSE 0
                    END,
                    0, SUBSTRING(T1.INTE_STATUS, 2, 3), to_timestamp(
T1._24, 'yyyy/mm/dd hh24:mi:ss' ),
                    to_timestamp( T1._25, 'yyyy/mm/dd hh24:mi:ss' ),
T1.DT_DELETE, T1._21, T1._20,
                    T1.OWNER_REF, T1.ACTIVE_SESSION, T2.GetInfoServers,
T2.TriggerServers
             FROM ALIS.T_INTERCEPTION   T1,
                  ALIS.R_INTERCEPTION   T2
             WHERE ( ( T1.INTE_STATUS SIMILAR TO '___[26]_' AND
T1.DT_CHECKING + interval '1 minute'  < NOW())
                  OR ( T1.DT_CHECKING + CAST( 15 || ' minutes' AS
interval )  < NOW()) )
               AND (T2.R_Inte_Ref = T1.R_INTE_REF)
             ORDER BY DT_CHECKING
             LIMIT 100"
    PL/pgSQL function "audit_inte" line 228 at execute statement
    SQL statement "SELECT  ALIS.Audit_Inte( 0, 0 )"
    PL/pgSQL function "audit_inte" line 5 at select into variables
    SQL statement "SELECT  ALIS.AUDIT_INTE( )"
    PL/pgSQL function "adm_inte" line 578 at select into variables
STATEMENT:  SELECT ALIS.ADM_INTE()
ERROR:  duplicate key violates unique constraint
"pg_class_relname_nsp_index"
CONTEXT:  SQL statement "CREATE TEMPORARY TABLE Tmp_AdmAlarmMsg AS SELECT *
FROM EASYSHARE.D_FLUX WHERE ( CHARINDEX(  $1 , DESTINATION ) <> 0 ) AND
(COMPONENTYPE = '91') AND ( STATUS >= 0 ) AND ( DT_TIMEOUT >= NOW() )"
    PL/pgSQL function "adm_alarm" line 132 at SQL statement
STATEMENT:  SELECT ALARM.ADM_ALARM()
ERROR:  duplicate key violates unique constraint
"pg_class_relname_nsp_index"
CONTEXT:  SQL statement "CREATE TEMP TABLE Tmp_MsgNeInfo ( NeRef int4 NOT
NULL, Otid int4 NULL, NbrMsgMax int4 NULL DEFAULT(0), ProtocolRef int4 NULL,
TechnoRef int4 NULL, FirstInteIndex int4 NULL DEFAULT(0), MaxInte int4 NULL
DEFAULT(1024), Destinataire varchar(60) NULL, NbrInteNe int4 NULL,
NbrFailedAttempts int4 NULL, DefaultMsgParam varchar(200) NULL )"
    PL/pgSQL function "msg_adddelget_inte" line 33 at SQL statement
    SQL statement "SELECT  ALIS.Msg_AddDelGet_Inte( 0 )"
    PL/pgSQL function "msg_adddelget_inte" line 5 at select into
variables
    SQL statement "SELECT  ALIS.MSG_ADDDELGET_INTE( )"
    PL/pgSQL function "adm_inte" line 584 at select into variables
STATEMENT:  SELECT ALIS.ADM_INTE()
ERROR:  duplicate key violates unique constraint
"pg_class_relname_nsp_index"
CONTEXT:  SQL statement "CREATE TEMP TABLE Tmp_MsgNeInfo ( NeRef int4 NOT
NULL, Otid int4 NULL, NbrMsgMax int4 NULL DEFAULT(0), ProtocolRef int4 NULL,
TechnoRef int4 NULL, FirstInteIndex int4 NULL DEFAULT(0), MaxInte int4 NULL
DEFAULT(1024), Destinataire varchar(60) NULL, NbrInteNe int4 NULL,
NbrFailedAttempts int4 NULL, DefaultMsgParam varchar(200) NULL )"
    PL/pgSQL function "msg_adddelget_inte" line 33 at SQL statement
    SQL statement "SELECT  ALIS.Msg_AddDelGet_Inte( 0 )"
    PL/pgSQL function "msg_adddelget_inte" line 5 at select into
variables
    SQL statement "SELECT  ALIS.MSG_ADDDELGET_INTE( )"
    PL/pgSQL function "adm_inte" line 584 at select into variables
STATEMENT:  SELECT ALIS.ADM_INTE()
ERROR:  duplicate key violates unique constraint
"pg_class_relname_nsp_index"
CONTEXT:  SQL statement "CREATE TEMP TABLE Tmp_AuditNeActiveCC AS SELECT
DISTINCT T1.OBJ_REF AS NeRef, T1.OBJ_ID AS NeId, T1.OBJ_REF_PARENT AS
TechnoRef, T3.OBJ_REF AS ProtocolRef, REPEAT(' ', 5) AS ParamTag, 100 AS
AuditPriority, 0 AS AuditWaitDelay, REPEAT(' ', 200) AS DefaultParam, 0 AS
FirstInteIndex, 1024 AS MaxInte FROM EASYSHARE.D_OBJECT T1,
EASYSHARE.D_PARAMETER T2, EASYSHARE.D_OBJECT T3, EASYSHARE.D_PARAMETER T4
WHERE (T1.OBJ_CLASS_TAG = '-4741') AND (SUBSTRING(T1.OBJ_STATES, 1, 2) =
'22') AND (T1.OBJ_STATUS LIKE '__11%') AND (T2.OBJ_REF = T1.OBJ_REF) AND
(T2.PARAM_TAG = '23') AND (T2.PARAM_VALUE IS NULL) AND (T3.OBJ_REF_PARENT =
T1.OBJ_REF_PARENT) AND (T3.OBJ_CLASS_TAG = '42') AND (T4.OBJ_REF =
T1.OBJ_REF) AND (T4.PARAM_TAG = '21') AND (to_timestamp(T4.PARAM_VALUE,
'yyyy/mm/dd hh24:mi:ss') - CAST( $1  || ' minutes' AS interval) < NOW())"
    PL/pgSQL function "cc_audit_ne" line 174 at SQL statement
    SQL statement "SELECT  ALIS.CC_AUDIT_NE ()"
    PL/pgSQL function "adm_check" line 101 at select into variables
STATEMENT:  SELECT ALIS.ADM_CHECK()
ERROR:  duplicate key violates unique constraint
"pg_class_relname_nsp_index"
CONTEXT:  SQL statement "CREATE TEMPORARY TABLE Tmp_AdmAlarmMsg AS SELECT *
FROM EASYSHARE.D_FLUX WHERE ( CHARINDEX(  $1 , DESTINATION ) <> 0 ) AND
(COMPONENTYPE = '91') AND ( STATUS >= 0 ) AND ( DT_TIMEOUT >= NOW() )"
    PL/pgSQL function "adm_alarm" line 132 at SQL statement
STATEMENT:  SELECT ALARM.ADM_ALARM()
ERROR:  duplicate key violates unique constraint
"pg_class_relname_nsp_index"
CONTEXT:  SQL statement "CREATE TEMP TABLE Tmp_MsgNeInfo ( NeRef int4 NOT
NULL, Otid int4 NULL, NbrMsgMax int4 NULL DEFAULT(0), ProtocolRef int4 NULL,
TechnoRef int4 NULL, FirstInteIndex int4 NULL DEFAULT(0), MaxInte int4 NULL
DEFAULT(1024), Destinataire varchar(60) NULL, NbrInteNe int4 NULL,
NbrFailedAttempts int4 NULL, DefaultMsgParam varchar(200) NULL )"
    PL/pgSQL function "msg_adddelget_inte" line 33 at SQL statement
    SQL statement "SELECT  ALIS.Msg_AddDelGet_Inte( 0 )"
    PL/pgSQL function "msg_adddelget_inte" line 5 at select into
variables
    SQL statement "SELECT  ALIS.MSG_ADDDELGET_INTE( )"
    PL/pgSQL function "adm_inte" line 584 at select into variables
STATEMENT:  SELECT ALIS.ADM_INTE()
ERROR:  duplicate key violates unique constraint
"pg_class_relname_nsp_index"
CONTEXT:  SQL statement "CREATE TEMP TABLE Tmp_MsgNeInfo ( NeRef int4 NOT
NULL, Otid int4 NULL, NbrMsgMax int4 NULL DEFAULT(0), ProtocolRef int4 NULL,
TechnoRef int4 NULL, FirstInteIndex int4 NULL DEFAULT(0), MaxInte int4 NULL
DEFAULT(1024), Destinataire varchar(60) NULL, NbrInteNe int4 NULL,
NbrFailedAttempts int4 NULL, DefaultMsgParam varchar(200) NULL )"
    PL/pgSQL function "msg_adddelget_inte" line 33 at SQL statement
    SQL statement "SELECT  ALIS.Msg_AddDelGet_Inte( 0 )"
    PL/pgSQL function "msg_adddelget_inte" line 5 at select into
variables
    SQL statement "SELECT  ALIS.MSG_ADDDELGET_INTE( )"
    PL/pgSQL function "adm_inte" line 584 at select into variables
STATEMENT:  SELECT ALIS.ADM_INTE()
ERROR:  duplicate key violates unique constraint
"pg_class_relname_nsp_index"
CONTEXT:  SQL statement "CREATE TEMP TABLE Tmp_AuditTechnoActiveCC (
TechnoRef int4 NOT NULL, AuditPeriod int4 NOT NULL, AuditBeginRange
varchar(30) NOT NULL, AuditEndRange varchar(30) NOT NULL, AuditPriority int4
NOT NULL, AuditLimitDate varchar(30) NOT NULL )"
    PL/pgSQL function "cc_audit_ne" line 93 at SQL statement
    SQL statement "SELECT  ALIS.CC_AUDIT_NE ()"
    PL/pgSQL function "adm_check" line 101 at select into variables
STATEMENT:  SELECT ALIS.ADM_CHECK()
FATAL:  cache lookup failed for relation 463558
LOG:  server process (PID 25237) exited with exit code 1
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2005-05-20 19:04:07 CEST
LOG:  checkpoint record is at 0/A7110300
LOG:  redo record is at 0/A7110300; undo record is at 0/0; shutdown FALSE
LOG:  next transaction ID: 727695; next OID: 468543
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  redo starts at 0/A7110340
FATAL:  the database system is starting up
LOG:  record with zero length at 0/A77BF950
LOG:  redo done at 0/A77BF918
LOG:  database system is ready
ERROR:  cache lookup failed for relation 463558
CONTEXT:  SQL statement "CREATE TEMP TABLE Tmp_AuditInte ( InteRef int4 NOT
NULL, InteRefParent int4 NOT NULL, DynamicFlag int4 NOT NULL DEFAULT (0),
SessionFlag int4 NOT NULL DEFAULT (0), InteStatus varchar(10) NULL,
MinInteNeState int4 NULL, MaxInteNeState int4 NULL, MinSessionNeState int4
NULL, MaxSessionNeState int4 NULL, InteStart timestamp NULL, InteStop
timestamp NULL, InteDelete timestamp NULL, InteStatusValue char(60) NULL,
Liid varchar(100) NULL, WarrantId varchar(100) NULL, NeId varchar(60) NULL,
OwnerRef int4 NULL, SessionNumber int4 NOT NULL DEFAULT(0),
GetInfoServerList varchar(60) NOT NULL DEFAULT (''), TriggerServerList
varchar(60) NOT NULL DEFAULT ('') )"
    PL/pgSQL function "audit_inte" line 141 at SQL statement
    SQL statement "SELECT  ALIS.Audit_Inte( 0, 0 )"
    PL/pgSQL function "audit_inte" line 5 at select into variables
    SQL statement "SELECT  ALIS.AUDIT_INTE( )"
    PL/pgSQL function "adm_inte" line 578 at select into variables
STATEMENT:  SELECT ALIS.ADM_INTE()
----------------------------------------------------------------------------
---------------
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 17:39
To: Patrick.FICHE@AQSACOM.COM
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to recover from : "Cache lookup failed for
rela tion "


Patrick.FICHE@AQSACOM.COM writes:
> Do you have any idea on the reason of this situation ?

No, I was hoping we might see some clues but there wasn't much.  Both
the pg_depend entries and the pg_type row should have been deleted by
the same transaction that deleted the pg_class row.  Partially applied
transactions aren't supposed to happen of course :-(

Oh, btw: is there a physical file for 463558?  As long as you didn't
TRUNCATE the temp table, its name should still be 463558.  If it is
there, you might want to note the file mod time before you remove it ---
that would give us a good fix on when this happened.

Did you have any system crashes or other odd behavior around the time
of the error?

            regards, tom lane