Tracking the cause of the error "could not open communication with OID"

Question

Most recently, my PostgreSQL 8.2.4 logs these 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: updating table A triggers a trigger that inserts data into table B, which launches another trigger that (among many other things) makes a choice in table C. This choice on table C is then reported as CONTEXT the problems are higher. The sequence of requests that cause the error message to appear is executed every day, and every day he complains that the same OID is missing.

It is only natural that the OID mentioned in the error message does not exist when pg_class is requested. Running problematic SQL (i.e., a selection in table C) does not cause problems. I tried to figure out the OID and the connections between all the involved tables to find out where this link is to a non-existent OID, but I failed. I started from table A and got my OID (pg_class.reltype) and confirmed that it has a trigger. Problems arise when I request pg_trigger using pg_trigger.tgrelid = pg_class.reltype as a condition. The query sets 0 rows, but when I query the tables just relname / tgname, I get different OIDs, just like the trigger is in another table. I did a quick test, and it seems that creating a simple table with a trigger on it gives the same result.

So my questions are:

• How to navigate pg_trigger (and other pg tables like pg_attribute, pg_shdepend) when I can find the table in pg_class?

• If I can find the link to the problematic OID, can I just delete the link by doing direct updates / deletes in the pg_class tables?

Answer 1

Note that 'reltype' is the OID of the rowtype table - the OID of the pg_class.oid table pg_class.oid (which is a system column, therefore it does not appear in \d or select * outputs, you need to explicitly select it).

Hopefully this solves some of the mysteries of how catalog tables relate to each other! The same pattern is repeated with quite a few other tables using oid as the primary key.

This looks like a pretty serious problem, perhaps indicating some kind of corruption in the directory? You can directly modify pg_class et al, but obviously there is a certain risk to this. I cannot come up with a lot of general advice to give here - what to do will vary greatly depending on what you find.

Answer 2

If this appears when executing statements inside an SQL function, change the language from SQL to plpgsql. The reason may be a cached plan. The plpgsql function invalidates the plan between runs, while the sql function skips this step.