追踪“无法打开与 OID 的关系”的原因错误
最近，我的 PostgreSQL 8.2.4 记录了这样的错误：
ERROR: could not open relation with OID nnnnnnnnn
CONTEXT: SELECT a,b,c FROM table_C
该错误总是由相同的场景引起：表 A 的更新导致触发器触发，该触发器将数据插入到表 B，这触发另一个触发器，其中（以及其他许多事情）然后在表 C 上进行选择。然后将表 C 上的选择报告为上述问题的上下文。导致错误消息出现的查询序列每天都会执行，并且每天都会抱怨缺少相同的 OID。
很自然地，当查询 pg_class 时，错误消息中提到的 OID 并不存在。执行有问题的 SQL（即对表 C 进行选择）不会导致任何问题。我试图找出 OID 和所有涉及的表之间的连接，以找出对不存在的 OID 的引用在哪里，但我失败了。我从表 A 开始，获取其 OID (pg_class.reltype) 并验证它是否附加了触发器。当我使用 pg_trigger.tgrelid = pg_class.reltype 作为条件查询 pg_trigger 时，问题就开始了。查询产生 0 行，但是当我仅通过 relname/tgname 查询表时，我得到不同的 OID，就像触发器位于不同的表上一样。我做了一个快速测试，发现创建一个带有触发器的简单表会产生相同的结果。
所以我的问题是：
当我可以在 pg_class 中找到表时，如何导航 pg_trigger（以及其他 pg 表，如 pg_attribute、pg_shdepend）表？
我如何导航 pg_trigger（以及其他 pg 表，如 pg_attribute、pg_shdepend如果我设法找到对有问题的 OID 的引用，我可以通过在 pg_class 表上直接更新/删除来简单地删除该引用吗？
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:
How do i navigate pg_trigger (and other pg tables like pg_attribute, pg_shdepend) tables when i can locate table in pg_class?
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
请注意，“reltype”是表行类型的 OID - 表本身的 OID 是 pg_class.oid（它是系统列，因此不会显示在
\d< 中） /code> 或select *输出，您需要明确选择它）。希望这将解决目录表如何相互关联的一些谜团！许多其他使用 oid 作为主键的表也重复了相同的模式。
这看起来是一个相当严重的问题，可能表明存在某种目录损坏？您可以直接修改 pg_class 等，但显然这样做存在一些风险。我想不出太多通用的建议可以在这里给出——做什么会根据你发现的内容而有很大不同。
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\dorselect *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_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.如果在 SQL 函数内执行语句时出现此情况，请将语言从 SQL 更改为 plpgsql。原因可能是缓存的计划。 plpgsql 函数使运行之间的计划无效，而 sql 函数似乎跳过此步骤。
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.
在后端验证您的表，PostgreSQL 为创建的每个表分配唯一的OID。
尝试在数据库端插入数据，然后通过应用程序执行。
我面临着同样的问题并挣扎了很长时间。
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.
我在使用 pglogic 复制时遇到了类似的情况。链接中的最后一个解决方法 https://github.com/2ndQuadrant/pgological/issues/347< /a> 解决了我的问题。
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.
当我尝试从视图中删除时收到此错误。更改我的 sql 以使用表而不是视图有帮助。
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.