如果不存在，则创建模式会引发重复的键错误 [英] CREATE SCHEMA IF NOT EXISTS raises duplicate key error
问题描述
为了提供一些上下文信息，该命令在一个任务内发出，许多任务可能同时从多个工作人员发出同一命令。
To give some context, the command is issued inside a task, and many task might issue the same command from multiple workers at the same time.
每个任务都会尝试创建一个postgres模式。我经常遇到以下错误：
Each tasks tries to create a postgres schema. I often get the following error:
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中的错误吗？
Postgres version is PostgreSQL 9.4rc1.
Is it a bug in Postgres?
推荐答案
这是一个表和模式的 IF NOT EXISTS 的实现有点麻烦。基本上，这是一次尝试，而PostgreSQL不能干净地处理竞争条件。这是安全的，但是很丑。
This is a bit of a wart in the implementation of IF NOT EXISTS for tables and schemas. Basically, they're an upsert attempt, and PostgreSQL doesn't handle the race conditions cleanly. It's safe, but ugly.
如果架构是在另一个会话中并发创建的，但尚未提交，那么它既存在又不存在，具体取决于谁你是谁，你看起来如何。其他事务无法提交系统目录中的新架构，因为它尚未提交，因此 pg_namespace 中的条目对其他事务不可见。所以 CREATE SCHEMA / CREATE TABLE 试图创建它，因为就其而言，该对象不存在
If the schema is being concurrently created in another session but isn't yet committed, then it both exists and does not exist, depending on who you are and how you look. It's not possible for other transactions to "see" the new schema in the system catalogs because it's uncommitted, so it's entry in pg_namespace is not visible to other transactions. So CREATE SCHEMA / CREATE TABLE tries to create it because, as far as it's concerned, the object doesn't exist.
但是，这会在具有唯一约束的表中插入一行。唯一约束必须能够查看未提交的行才能起作用。因此，插入块（停止）直到执行 CREATE 的第一个事务提交或回滚为止。如果提交，第二个事务将中止，因为它试图插入违反唯一约束的行。 创建模式不足以捕获这种情况并重试。
However, that inserts a row into a table with a unique constraint. Unique constraints must be able to see uncommitted rows in order to function. So the insert blocks (stops) until the first transaction that did the CREATE either commits or rolls back. If it commits, the second transaction aborts, because it tried to insert a row that violates a unique constraint. CREATE SCHEMA isn't smart enough to catch this case and re-try.
正确修复此PostgreSQL可能会可能需要谓词锁定，从而可以锁定潜在的行。这可能会作为实现 UPSERT 的当前工作的一部分而添加。
To properly fix this PostgreSQL would probably need predicate locking, where it could lock the potential for a row. This might get added as part of the current work going on for implementing UPSERT.
对于这些特定命令，PostgreSQL可能会对系统目录进行脏读，从而可以看到未提交的更改。然后，它可以等待未提交的事务提交或回滚，重新执行脏读以查看是否有人在等待，然后重试。但这会产生争用情况，在您进行读取以检查它与尝试创建它之间，别人可能会创建该模式。
For these particular commands, PostgreSQL could probably do a dirty read of the system catalogs, where it can see uncommitted changes. Then it could wait for the uncommitted transaction to commit or roll back, re-do the dirty read to see if someone else is waiting, and retry. But this would have a race condition where someone else might create the schema between when you do the read to check for it and when you try to create it.
所以如果不存在，则必须：
- 检查架构是否存在；
- 尝试创建表
- 如果由于唯一约束错误而导致创建失败，请从头开始重试
- 如果表创建成功，请完成
- Check to see if the schema exists; if it does, finish without doing anything.
- Attempt to create the table
- If creation fails due to a unique constraint error, retry at the start
- If table creation succeeds, finish
据我所知，没有人实现过，或者他们尝试了，但是没有被接受。
As far as I know nobody's implemented that, or they tried and it wasn't accepted. There would be possible issues with transaction ID burn rate, etc, with this approach.
我认为这是一个错误，但这是是的，我们知道一种错误，而不是我们将正确解决这种错误。随时发布到pgsql-bugs上；至少在文档中应该提到有关如果不存在的警告。
I think this is a bug of sorts, but it's a "yeah, we know" kind of bug, not a "we'll get right on fixing that" kind of bug. Feel free to post to pgsql-bugs about it; at the very least the documentation should mention this caveat about IF NOT EXISTS.
我不建议执行DDL与此同时。
I don't recommend doing DDL concurrently like that.
这篇关于如果不存在，则创建模式会引发重复的键错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
