postgresql - 为什么这个查询会死锁？

Question

我有一个应用程序可以读取现有 PostgreSQL 9.1 数据库的结构，将其与“应该”状态进行比较并相应地更新数据库。在大多数情况下，这很好用。但是，我现在有几个实例在读取当前数据库结构时陷入僵局。负责的查询读取现有的外键:

SELECT tc.table_schema, tc.table_name, tc.constraint_name, kcu.column_name,
       ccu.table_schema, ccu.table_name, ccu.column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
     ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
     ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY'

在 pgAdmin 中查看服务器状态显示这是在服务器上运行的唯一事件查询/事务。尽管如此，查询仍未返回。

错误在某种程度上是可重现的:当我找到一个产生错误的数据库时，它每次都会产生错误。但并非所有数据库都会产生错误。这是一个神秘的错误，关于还有什么可以尝试或如何解决这个问题，我的选择和想法都用完了。因此，我们非常感谢任何意见或想法!

PS:我的一位同事刚刚报告说他在使用 PostgreSQL 8.4 时产生了同样的错误。

Answer 1

我测试并发现您的查询也非常慢。这个问题的根源在于information_schema中的“表”实际上是按照SQL标准提供目录的复杂 View 。在这种特殊情况下，事情变得更加复杂，因为外键可以建立在多个列上。对于那些我怀疑可能不受欢迎的情况，您的查询会产生重复行。

与 unnest 关联的子查询，提供给 ARRAY 构造函数避免了我的查询中的问题。

此查询产生相同的信息，只是没有重复的行并且速度提高了 100 倍。另外，我敢保证，没有死锁。

仅适用于 PostgreSQL，不能移植到其他 RDBMS。

SELECT c.conrelid::regclass AS table_name
     , c.conname AS fk_name
     , ARRAY(SELECT a.attname
             FROM   unnest(c.conkey) x
             JOIN   pg_attribute a
             ON     a.attrelid = c.conrelid AND a.attnum = x) AS fk_columns
     , c.confrelid::regclass AS ref_table
     , ARRAY(SELECT a.attname
             FROM   unnest(c.confkey) x
             JOIN   pg_attribute a
             ON     a.attrelid = c.confrelid AND a.attnum = x) AS ref_columns
FROM   pg_catalog.pg_constraint c
WHERE  c.contype = 'f';
-- ORDER  BY c.conrelid::regclass::text,2

cast to ::regclass生成与当前 search_path 相同的表名。可能是也可能不是你想要的。要使此查询包含每个表名的绝对路径(架构)，您可以 set the search_path like this :

SET search_path = pg_catalog;
SELECT ...

使用默认的 search_path 继续您的 session :

RESET search_path;

相关:

• Get column names and data types of a query, table or view