13

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?

Improve this question
1
  • 1
    Version 8.2.4 is very old and outdated, you're (at this moment) 16 patch rounds behind: 8.2.20 is the latest. That is 4 years without any maintenance... Digg into all the release notes to see if this issue has been fixed. Commented Feb 17, 2011 at 15:17

5 Answers 5

Reset to default
14

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

That is exactly what i needed. After all the problem turned to be trival. One of the functions was depending on table that some time ago was removed & recreated. Somehow that function was using OID of table that was removed. Simply recreating function and trigger (causing them to get current OID) resolved the problem.
3

If this appears while executing statements inside SQL function, then change the language from SQL to plpgsql. The cause can be a cached plan. plpgsql function invalidates plan between runs, while sql function seems to skip this step.

Improve this answer

Comments

0

Verify your tables at backend, PostgreSQL assigns an unique OID for each table created.

Try to insert data at data base side then do it through application.

I was facing the same issue and struggled for long.

Improve this answer

Comments

0

I ended up in similar situation while working on with pglogical replication. The last workaround in the link https://github.com/2ndQuadrant/pglogical/issues/347 resolved my issue.

Another workaround -> is to reconnect to DB on the node host. It should solve the issue.

Improve this answer

Comments

0

I received this error when I was trying to delete from a view. Changing my sql to use the table instead of the view helped.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.