postgresql - PostgreSQL外键违规的原因？

Question

我的 PostgreSQL (9.2) 数据库包含两个具有外键约束的表 registrations 和 attributes:

postgres=# \d+ registrations;
                Table "public.registrations"
 Column  | Type  | Modifiers | Storage  | Stats target | Description
---------+-------+-----------+----------+--------------+-------------
 name    | text  | not null  | extended |              |
 parent  | text  |           | extended |              |
 storage | bytea |           | extended |              |
Indexes:
    "registrations_pkey" PRIMARY KEY, btree (name)
Referenced by:
    TABLE "attributes" CONSTRAINT "attributes_cname_fkey" FOREIGN KEY (cname) REFERENCES registrations(name) ON DELETE CASCADE
Has OIDs: no

postgres=# \d+ attributes;
                 Table "public.attributes"
 Column | Type  | Modifiers | Storage  | Stats target | Description
--------+-------+-----------+----------+--------------+-------------
 cname  | text  | not null  | extended |              |
 aname  | text  | not null  | extended |              |
 tags   | text  |           | extended |              |
 value  | bytea |           | extended |              |
Indexes:
    "attributes_pkey" PRIMARY KEY, btree (cname, aname)
Foreign-key constraints:
    "attributes_cname_fkey" FOREIGN KEY (cname) REFERENCES registrations(name) ON DELETE CASCADE
Has OIDs: no

在某些时候我意识到有些行违反了外键约束:

postgres=# SELECT COUNT(*) FROM attributes LEFT JOIN registrations ON attributes.cname=registrations.name WHERE registrations.name IS NULL;
 count
-------
    71
(1 row)

你能帮我理解这种腐败是如何发生的吗？

Answer 1

标记为 NOT VALID 的约束是您可能会看到违规的一种情况，但是 NOT VALID 子句会出现在 psql \d+ 输出。 (我相信可以在目录中手动更新此标志，但为了您的利益，我希望这不是问题...)

据我所知，唯一支持的绕过外键检查的方法是在修改数据之前SET session_replication_role TO replica。这是为了复制过程的好处，在假设约束已经在主服务器上得到验证的情况下运行——尽管如果您的复制器有问题或配置错误，这肯定会出错。

super 用户也可以手动禁用约束的底层触发器(对于试图加速批量导入的人来说，这通常很诱人)。以下将告诉您触发器当前是否处于事件状态(tgenabled 应为 'O'):

SELECT *
FROM pg_trigger
WHERE tgname ~ '^RI_ConstraintTrigger'
  AND tgrelid IN ('registrations'::regclass, 'attributes'::regclass)

我认为没有任何方法可以知道这在过去是否被临时更改过，但如果您启用了语句日志记录，您可能会在在某处。

还有at least one loophole在外键执行中，当然，您总是有可能发现错误...