ERROR: cache lookup failed for relation 438095645

Hey list,
I am getting this error "ERROR: cache lookup failed for relation
438095645" when i try to run "drop table
pl_inventory_analyzer_files_table ;" from psql on the database server.
I've already REINDEX'd the database system tables, and all the indexes
on that table, and on all the indexes on any tables referenced by
foreign keys. OS and Version info below, more info available upon
request. Does anyone have any suggestions on how to fix this?

Thanks,
Joe

select version();
version
--------------------------------------------------------------------------------------------
PostgreSQL 7.4.6 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
3.2.2 (SuSE Linux)
(1 row)

uname -a
Linux mammon 2.4.21-273-smp #1 SMP Mon Jan 17 12:31:48 UTC 2005 x86_64
unknown

Joe Markwardt <joe(at)avsupport(dot)com> writes:
> I am getting this error "ERROR: cache lookup failed for relation
> 438095645" when i try to run "drop table
> pl_inventory_analyzer_files_table ;" from psql on the database server.

Look through the system catalogs to see where that OID appears.
pg_index.indexrelid, pg_trigger.tgconstrrelid, pg_depend.objid,
pg_depend.refobjid are likely places.

regards, tom lane

On Wed, 2005-07-06 at 11:20 -0400, Tom Lane wrote:
> Joe Markwardt <joe(at)avsupport(dot)com> writes:
> > I am getting this error "ERROR: cache lookup failed for relation
> > 438095645" when i try to run "drop table
> > pl_inventory_analyzer_files_table ;" from psql on the database server.
>
> Look through the system catalogs to see where that OID appears.
> pg_index.indexrelid, pg_trigger.tgconstrrelid, pg_depend.objid,
> pg_depend.refobjid are likely places.
>
> regards, tom lane
>
Tom,
I found the attached entries in the pg_trigger table, but I'm not quite
sure how to read them, or what to do about them.

Thanks
Joe

> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Joe Markwardt <joe(at)avsupport(dot)com> writes:
>>> I am getting this error "ERROR: cache lookup failed for relation
>>> 438095645" when i try to run "drop table
>>> pl_inventory_analyzer_files_table ;" from psql on the database server.
>>
>> Look through the system catalogs to see where that OID appears.

> I found the attached entries in the pg_trigger table, but I'm not quite
> sure how to read them, or what to do about them.

They appear to be triggers for a foreign-key constraint between
pl_inventory_analyzer_files_table and pl02_status_table ... so I guess
the question is what happened to the latter?

regards, tom lane

On Wed, 2005-07-06 at 14:04 -0400, Tom Lane wrote:
> Joe Markwardt <joe(at)avsupport(dot)com> writes:
> >>> I am getting this error "ERROR: cache lookup failed for relation
> >>> 438095645" when i try to run "drop table
> >>> pl_inventory_analyzer_files_table ;" from psql on the database server.
> >>
> >> Look through the system catalogs to see where that OID appears.
>
> > I found the attached entries in the pg_trigger table, but I'm not quite
> > sure how to read them, or what to do about them.
>
> They appear to be triggers for a foreign-key constraint between
> pl_inventory_analyzer_files_table and pl02_status_table ... so I guess
> the question is what happened to the latter?
>

I'm not sure. As far as I can tell nothing has happened to the
pl02_status_table, nor should anything have happened to it recently.
Its just a lookup table relating status ID's to descriptive names, and
it only has about 8 records in it, and the last change was several
months ago. I've attached the \d output of both tables, and everything
appears kosher to me. Is there something in particular I should look
for?

Thanks
Joe

> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

Joe Markwardt <joe(at)avsupport(dot)com> writes:
> On Wed, 2005-07-06 at 14:04 -0400, Tom Lane wrote:
>> They appear to be triggers for a foreign-key constraint between
>> pl_inventory_analyzer_files_table and pl02_status_table ... so I guess
>> the question is what happened to the latter?

> I'm not sure. As far as I can tell nothing has happened to the
> pl02_status_table, nor should anything have happened to it recently.

Well, that's just plain bizarre...

The next thing to check is whether the OID mentioned in pg_trigger
(which I forgot already) is the same as the OID shown in pg_class, ie
select oid from pg_class where relname = 'pl02_status_table'. I can
think of a few different possible pathologies here, but there's no point
speculating in advance of the data.

regards, tom lane

On Wed, 2005-07-06 at 23:36 -0400, Tom Lane wrote:
> Joe Markwardt <joe(at)avsupport(dot)com> writes:
> > On Wed, 2005-07-06 at 14:04 -0400, Tom Lane wrote:
> >> They appear to be triggers for a foreign-key constraint between
> >> pl_inventory_analyzer_files_table and pl02_status_table ... so I guess
> >> the question is what happened to the latter?
>
> > I'm not sure. As far as I can tell nothing has happened to the
> > pl02_status_table, nor should anything have happened to it recently.
>
> Well, that's just plain bizarre...
>
> The next thing to check is whether the OID mentioned in pg_trigger
> (which I forgot already) is the same as the OID shown in pg_class, ie
> select oid from pg_class where relname = 'pl02_status_table'.

oid
-----------
438094756
(1 row)

The oid that shows up in the error is 438095645.

the oid for the pl02_status_table does not show up in any of the rows in
pg_trigger that have the oid from the error in them. The oid for the
status table does show up in a few other rows in pg_trigger (attached
file), which makes sense since its referenced in at least 2 other tables
with foreign keys, and it still is being replicated by slony so it has
that trigger on it as well.

Thanks
Joe

Joe Markwardt <joe(at)avsupport(dot)com> writes:
>> The next thing to check is whether the OID mentioned in pg_trigger
>> (which I forgot already) is the same as the OID shown in pg_class, ie
>> select oid from pg_class where relname = 'pl02_status_table'.

> oid
> -----------
> 438094756
> (1 row)

> The oid that shows up in the error is 438095645.

> the oid for the pl02_status_table does not show up in any of the rows in
> pg_trigger that have the oid from the error in them.

Weird. You sure you didn't drop and recreate pl02_status_table? Table
oids don't change by themselves.

> The oid for the
> status table does show up in a few other rows in pg_trigger (attached
> file), which makes sense since its referenced in at least 2 other tables
> with foreign keys, and it still is being replicated by slony so it has
> that trigger on it as well.

Slony eh? You didn't mention that before; it does a few weird things IIRC.
You might try asking on the slony list whether anyone has any ideas.

regards, tom lane