tracing the cause of "could not open relation with OID" error

Question

Quite recently my PostgreSQL 8.2.4 logs such errors:

ERROR:  could not open relation with OID nnnnnnnnn
CONTEXT: SELECT a,b,c FROM table_C

The error is always caused by the same scenario: an update to table A causes trigger to fire, which inserts data to table B, which fires another trigger, which (among many other things) does select on table C. That select on table C is then reported as CONTEXT of the problem above. The sequence of queries that cause the error message to appear is executed every day, and everyday it complains about the same OID missing.

Quite naturally the OID mentioned in error message doesn't exists when querying pg_class. Executing problematic SQL (that is, select on table C) doesn't cause any problems. I've tried to figure out the OIDs and connections between all the tables involved, to figure out where that reference to non-existent OID is, but i have failed. I started with table A and got its OID (pg_class.reltype) and verified, that it has trigger attached. The problems start when i query pg_trigger using pg_trigger.tgrelid = pg_class.reltype as a condition. The query yelds 0 rows, but when i query tables just by relname/tgname i get different OIDs, just like the trigger is on a different table. I did a quick test and it appears, that creating a simple table with a trigger on it produces the same result.

So my questions are:

1. How do i navigate pg_trigger (and other pg tables like pg_attribute, pg_shdepend) tables when i can locate table in pg_class?

2. If somehow I manage to find a reference to problematic OID, am I safe to simple remove the reference by doing direct updates/deletes on pg_class tables?

Answer 1

Note that 'reltype' is the OID of the table's rowtype- the OID of the table itself is pg_class.oid (which is a system column, so doesn't show up in \d or select * output, you need to select it explicitly).

Hopefully that will solve some mysteries of how the catalogue tables relate to each other! The same pattern is repeated with quite a few other tables using oid as their primary key.

It looks like quite a serious problem, possibly indicating some sort of catalogue corruption? You can modify pg_class et al directly, but obviously there is some risk involved in doing that. I can't think of much generic advice to give here- what to do will vary greatly depending on what you find.