postgresql - CREATE SCHEMA IF NOT EXISTS 引发重复键错误

Question

为了提供一些上下文，命令是在任务内部发出的，许多任务可能同时从多个工作人员发出相同的命令。

每个任务都尝试创建一个 postgres 模式。我经常收到以下错误:

IntegrityError: (IntegrityError) duplicate key value violates unique constraint "pg_namespace_nspname_index"
DETAIL:  Key (nspname)=(9621584361) already exists.
 'CREATE SCHEMA IF NOT EXISTS "9621584361"'

Postgres 版本为 PostgreSQL 9.4rc1。
这是 Postgres 中的错误吗？

Answer 1

这在表和模式的 IF NOT EXISTS 实现中有点瑕疵。基本上，它们是一种 upsert 尝试，而 PostgreSQL 没有干净地处理竞争条件。它很安全，但很丑。

如果模式正在另一个 session 中同时创建但尚未提交，那么它既存在又不存在，这取决于你是谁以及你的样子。其他事务不可能“看到”系统目录中的新模式，因为它未提交，所以它在 pg_namespace 中的条目对其他事务不可见。所以 CREATE SCHEMA/CREATE TABLE 尝试创建它，因为就它而言，该对象不存在。

但是，这会将一行插入到具有唯一约束的表中。唯一约束必须能够看到未提交的行才能起作用。因此，插入 block (停止)直到执行 CREATE 的第一个事务提交或回滚。如果它提交，则第二个事务中止，因为它试图插入违反唯一约束的行。 CREATE SCHEMA 不够聪明，无法捕获这种情况并重试。

要正确修复此 PostgreSQL 可能需要谓词锁定，它可以锁定行的潜力。这可能会作为当前正在进行的实现 UPSERT 工作的一部分添加。

对于这些特定的命令，PostgreSQL 可能会对系统目录进行脏读，它可以在其中看到未提交的更改。然后它可以等待未提交的事务提交或回滚，重新执行脏读以查看是否有其他人在等待，然后重试。但这会产生竞争条件，其他人可能会在您进行读取以检查它和您尝试创建它之间创建模式。

因此 IF NOT EXISTS 变体必须:

• 检查架构是否存在；如果是，什么也不做就结束。
• 尝试创建表
• 如果由于唯一约束错误导致创建失败，则在开始时重试
• 如果建表成功，结束

据我所知，没有人实现过，或者他们尝试过但没有被接受。使用这种方法可能会出现事务 ID 消耗率等问题。

我认为这是某种错误，但它是一种“是的，我们知道”的错误，而不是“我们会尽快修复该错误”的错误。请随时将其发布到 pgsql-bugs；至少文档应该提到关于 IF NOT EXISTS 的警告。

我不建议像那样并发执行 DDL。