Postgres: 如果不存在则创建表 ⇒ 23505

Question

我同时启动多个程序，它们都或多或少地执行

CREATE TABLE IF NOT EXISTS log (...);

有时候这会完美地运行。但是大多数情况下，一个或多个程序会崩溃，并显示以下错误信息:

23505：重复键值违反唯一约束“pg_class_relname_nsp_index”。

有人能解释一下为什么实际的 CREATE TABLE IF NOT EXISTS 命令会给我关于表已经存在的错误消息吗？这不是整个命令的目的吗？发生了什么？更重要的是，如何使其正确工作？

在此命令之后，还有几个 CREATE INDEX IF NOT EXISTS 命令。这些也偶尔以类似的方式失败。但大多数情况下，是 CREATE TABLE 语句失败。

- MathematicalOrchid

Answer 1

您可以在两个并行会话中重现此问题：

第一个会话：

begin;
create table if not exists log(id bigint generated always as identity, t timestamp with time zone, message text not null);

注意第一个会话尚未提交，因此表格实际上并不存在。

第二个会话：

begin;
create table if not exists log(id bigint generated always as identity, t timestamp with time zone, message text not null);

第二个会话现在将被阻塞，因为第一个会话保留了“log”这个名称。但是目前还不知道保留它的事务是否会提交。

然后，当您提交第一个会话时，第二个会话会失败。

ERROR:  duplicate key value violates unique constraint "pg_class_relname_nsp_index"
DETAIL:  Key (relname, relnamespace)=(log_id_seq, 2200) already exists.

为避免此问题，您需要确保在获取某些常见咨询锁之后才执行检查表是否存在的操作：

begin;
select pg_advisory_xact_lock(12345);
-- any bigint value, but has to be the same for all parallel sessions
create table if not exists log(id bigint generated always as identity, t timestamp with time zone, message text not null);
commit;

- Tometzky

---

这似乎完全解决了问题。（就像间歇性问题可以被确认“已解决”一样。） - MathematicalOrchid