关系的 PostgreSQL 错误缓存查找失败 - 是什么原因造成的，为什么？

PostgreSQL error cache lookup failed for relation - what causes it and why?

提问by leftnode

I have a query that is erroring out when attempting to create a temporary table. The query is:

我有一个查询在尝试创建临时表时出错。查询是：

CREATE TEMPORARY TABLE temp_table_t (LIKE original_table INCLUDING INDEXES)

创建临时表 temp_table_t (LIKE original_table INCLUDING INDEXES)

The error being returned is:

返回的错误是：

postgres7 error: [-1: ERROR: cache lookup failed for relation 14026125] in EXECUTE("CREATE TEMPORARY TABLE temp_table_t (LIKE original_table INCLUDING INDEXES)")

postgres7 错误：[-1：错误：关系 14026125 的缓存查找失败] 在 EXECUTE("CREATE TEMPORARY TABLE temp_table_t (LIKE original_table INCLUDING INDEXES)")

I found two threads on the PostgreSQL forums but they didn't explain much, unfortunately:

我在 PostgreSQL 论坛上找到了两个主题，但不幸的是，它们没有解释太多：

http://archives.postgresql.org/pgsql-performance/2010-04/msg00026.php

http://archives.postgresql.org/pgsql-performance/2010-04/msg00026.php

http://archives.postgresql.org/pgsql-performance/2010-04/msg00028.php

http://archives.postgresql.org/pgsql-performance/2010-04/msg00028.php

This query runs on other servers just fine, it is failing specifically on only one of our servers. Is there any insight you can give me as to what is causing the error and how to fix it? I first thought that it was the result of creating a temporary table name with the same name of an existing temporary table. I updated my code to create a randomly named temporary table each time, and that did not work either.

这个查询在其他服务器上运行得很好，它只在我们的一台服务器上失败。关于导致错误的原因以及如何修复它，您是否可以给我任何见解？我一开始以为是创建了一个与现有临时表同名的临时表名的结果。我每次都更新我的代码以创建一个随机命名的临时表，但这也不起作用。

回答by Oscar Raig Colon

I was dropping a schema and I had this error "lookup failed for relation xxxx". There was the name of a table also. It seemed some tables from the schema I was deleting, were in a strange state. At the end we did this third steps.

我正在删除一个模式，我遇到了这个错误“关系 xxxx 的查找失败”。还有一张桌子的名字。我正在删除的模式中的一些表似乎处于一种奇怪的状态。最后我们做了这第三步。

1. We obtain the C.oid of the table.

   select c.oid
   from pg_class  c
   join pg_namespace n on n.oid=c.relnamespace
   where c.relname = 'MY_TABLE_NAME'
       and n.nspname='MY_SCHEMA_NAME';
   

2. Next:

   delete from pg_class where oid = MY_OID;
   delete from pg_depend where objid = MY_OID;
   delete from pg_constraint where conrelid = MY_OID;
   

3. Finally:

   drop schema MY_SCHEMA_NAME cascade
   

1. 我们得到表的 C.oid。

   select c.oid
   from pg_class  c
   join pg_namespace n on n.oid=c.relnamespace
   where c.relname = 'MY_TABLE_NAME'
       and n.nspname='MY_SCHEMA_NAME';
   

2. 下一个：

   delete from pg_class where oid = MY_OID;
   delete from pg_depend where objid = MY_OID;
   delete from pg_constraint where conrelid = MY_OID;
   

3. 最后：

   drop schema MY_SCHEMA_NAME cascade
   

Then appeared another similar error with another table and we repeated steps 1 to 3 until we deleted the schema.

然后另一个表出现了另一个类似的错误，我们重复了步骤 1 到 3，直到我们删除了架构。

回答by ntranq

I ran into the same issue when creating a temporary table from a view, similar to what you are doing here:

从视图创建临时表时，我遇到了同样的问题，类似于您在此处执行的操作：

create temporary table temp_table_t (LIKE original_table including indexes);

I also had the same weird experience that it was only occurring with on only one of our servers.

我也有同样奇怪的经历，它只发生在我们的一台服务器上。

For me it was as simple as replacing the original_table(which was a view for me) with the

对我来说是一样简单更换original_table与（这是一个视图对我来说）

create or replace view my_view as (  
    // my view definition
);

syntax. After that, the process that I was using to create temporary tables did not give any errors.

句法。之后，我用来创建临时表的过程没有出现任何错误。

I don't know what you can do with your table, as it is not as simple as re-defining a view. If you can afford to lose the data, you can try dropping the table and re-creating it.

我不知道你可以用你的表做什么，因为它不像重新定义一个视图那么简单。如果您能承受丢失数据的代价，您可以尝试删除该表并重新创建它。