postgresql 追踪“无法打开与 OID 的关系”错误的原因

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

提问by Jacek Prucia

Quite recently my PostgreSQL 8.2.4 logs such errors:

最近我的 PostgreSQL 8.2.4 记录了这样的错误：

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.

该错误总是由相同的场景引起的：对表 A 的更新导致触发器触发，该触发器将数据插入到表 B，然后触发另一个触发器，该触发器（以及许多其他事情）确实在表 C 上选择。在表 C 上选择然后报告为上述问题的上下文。导致错误消息出现的查询序列每天都会执行，并且每天都会抱怨缺少相同的 OID。

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.

很自然地，在查询 pg_class 时，错误消息中提到的 OID 不存在。执行有问题的 SQL（即在表 C 上选择）不会导致任何问题。我试图找出所有涉及的表之间的 OID 和连接，找出对不存在的 OID 的引用在哪里，但我失败了。我从表 A 开始，获取了它的 OID (pg_class.reltype) 并验证了它是否附加了触发器。当我使用 pg_trigger.tgrelid = pg_class.reltype 作为条件查询 pg_trigger 时，问题就开始了。查询 ylds 0 行，但是当我仅通过 relname/tgname 查询表时，我得到不同的 OID，就像触发器在不同的表上一样。我做了一个快速测试，看起来，创建一个带有触发器的简单表会产生相同的结果。

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?

1. 当我可以在 pg_class 中找到表时，如何导航 pg_trigger（以及其他 pg 表，如 pg_attribute、pg_shdepend）表？

2. 如果我设法以某种方式找到对有问题的 OID 的引用，我是否可以安全地通过对 pg_class 表进行直接更新/删除来简单地删除引用？

采纳答案by araqnid

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 \dor select *output, you need to select it explicitly).

请注意，'reltype' 是表的行类型的 OID - 表本身的 OID 是pg_class.oid（这是一个系统列，因此不会出现在\d或select *输出中，您需要明确选择它）。

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.

希望这将解决目录表如何相互关联的一些谜团！使用 oid 作为主键的许多其他表重复了相同的模式。

It looks like quite a serious problem, possibly indicating some sort of catalogue corruption? You can modify pg_classet 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.

这看起来是一个很严重的问题，可能表明某种目录损坏？您可以pg_class直接修改et al，但显然这样做存在一些风险。我想不出很多通用的建议在这里给出 - 做什么会因你发现的不同而有很大差异。

回答by alexkovelsky

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.

如果在 SQL 函数内执行语句时出现这种情况，请将语言从 SQL 更改为 plpgsql。原因可能是缓存计划。plpgsql 函数使运行之间的计划无效，而 sql 函数似乎跳过了这一步。

回答by Amit Banger

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

在后端验证您的表，PostgreSQL为每个创建的表分配一个唯一的OID。

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

尝试在数据库端插入数据，然后通过应用程序进行。

I was facing the same issue and struggled for long.

我面临同样的问题并挣扎了很长时间。