为什么此Postgres查询抛出“重复键值违反唯一约束”？ [英] Why is this Postgres query throwing "duplicate key value violates unique constraint"?

问题描述

我已经实现了一个简单的更新/插入查询，如下所示：

I've implemented simple update/insert query like this:

-- NOTE: :time is replaced in real code, ids are placed statically for example purposes
-- set status_id=1 to existing rows, update others
UPDATE account_addresses
SET status_id = 1, updated_at = :time
WHERE account_id = 1
  AND address_id IN (1,2,3)
  AND status_id IN (2);

-- filter values according to what that update query returns, i.e. construct query like this to insert remaining new records:
INSERT INTO account_addresses (account_id, address_id, status_id, created_at, updated_at)
SELECT account_id, address_id, status_id, created_at::timestamptz, updated_at::timestamptz
FROM (VALUES (1,1,1,:time,:time),(1,2,1,:time,:time)) AS sub(account_id, address_id, status_id, created_at, updated_at)
WHERE NOT EXISTS (
  SELECT 1 FROM account_addresses AS aa2
  WHERE aa2.account_id = sub.account_id AND aa2.address_id = sub.address_id
)
RETURNING id;

-- throws:
-- PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "..."
-- DETAIL:  Key (account_id, address_id)=(1, 1) already exists.

我这样做的原因是：记录可能与<$ c $存在c> status_id = 2 。如果是这样，请设置 status_id = 1 。
然后插入新记录。如果已经存在，但不受第一个UPDATE查询的影响，请忽略它（即具有 status_id = 3 的行）。

The reason why I'm doing it this way is: the record MAY exist with status_id=2. If so, set status_id=1. Then insert new records. If it already exists, but was not affected by first UPDATE query, ignore it (i.e. rows with status_id=3).

这很好用，但同时执行，在竞争条件下重复键崩溃。
但是，如果我要自动执行插入不存在操作，为什么会发生竞争条件？

This works nicely, but doing it concurrently, it crashes on duplicate key in race condition. But why is race condition occurring, if I'm trying to do that "insert-where-not-exists" atomically?

推荐答案

Ah。我只是搜索了一点，然后插入不存在的地方不是原子的。

Ah. I just searched a little more and insert where not exists is not atomic.

从 http://www.postgresql.org/message-id/[e-mail] ：

法师写道：

Mage writes:

主要问题是不是插入...选择...哪里不存在
是原子的？

The main question is that isn't "insert into ... select ... where not exists" atomic?

不，不是：它会在其他事务
进行相同操作的情况下 失败，因为EXISTS测试将仅看到
在命令启动之前提交的行。您可能需要阅读
手册中有关并发的章节：
http://www.postgresql.org/docs/9.0/static/mvcc.html

No, it isn't: it will fail in the presence of other transactions doing the same thing, because the EXISTS test will only see rows that committed before the command started. You might care to read the manual's chapter about concurrency: http://www.postgresql.org/docs/9.0/static/mvcc.html

这篇关于为什么此Postgres查询抛出“重复键值违反唯一约束”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！