Обсуждение: 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@avsupport.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@avsupport.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@avsupport.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@avsupport.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@postgresql.org so that your > message can get through to the mailing list cleanly >
Вложения
Joe Markwardt <joe@avsupport.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@avsupport.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@avsupport.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