更新期間意外的唯一約束違規

在foo具有兩列（id和seq）的表中，我想seq為所有帶有任意seq > 4738. 計劃是立即插入一條新記錄，seq=4739所有seq > 4738記錄都移動+1。

這是桌子。

CREATE TABLE foo
(
 id uuid NOT NULL,
 seq integer NOT NULL,
 CONSTRAINT seq_key UNIQUE (seq)
)

CREATE UNIQUE INDEX idx_id
 ON foo
 USING btree
 (id);

CREATE UNIQUE INDEX idx_seq
 ON foo
 USING btree
 (seq);

我嘗試通過以下查詢實現 +1 班次。請注意，我使用子查詢嘗試> 4738按降序更新記錄（即假設 max seq=10000 然後最後一條記錄首先更新（10000->10001），然後倒數第二個（seq=10000此時不存在，seq=9999-> seq=10000（沒有違反約束），然後是 9998 -> 9999，…以避免在任何時候發生唯一的約束違反）。但是，這假設更新查詢的順序執行，這不是看起來發生的事情。

跑步時

UPDATE foo SET seq=anon_1.new_seq FROM (
 SELECT foo.id AS id, foo.seq + 1 AS new_seq
 FROM foo
 WHERE foo.seq > 4738 ORDER BY foo.seq DESC
) AS anon_1 WHERE foo.id = anon_1.id

我收到以下錯誤。

重複鍵值違反唯一約束“seq_key” 詳細資訊：鍵 (seq)=(7334) 已存在。

顯然，這是出乎意料的（因為在 之前滿足了約束UPDATE）。有什麼我可以嘗試解決的問題（不同的索引類型，只有約束，只有索引）？我注意到這個錯誤很大程度上取決於更新記錄的數量。如果更新的記錄較少，則似乎不會發生此問題（這可能暗示一些並行執行，因為當有太多記錄要更新時可能會交錯並導致約束/索引不唯一的一些中間狀態更多的）。一些想法和想法將不勝感激。

您需要創建一個可延遲的約束而不是索引。

CREATE TABLE foo
(
 id uuid NOT NULL primary key
 seq integer NOT NULL,
 CONSTRAINT seq_key UNIQUE (seq)
   deferrable initially immediate --<< this
);

通過將約束聲明為可延遲，它將在 UPDATE 語句的末尾（更準確地說：在事務結束時）而不是逐行檢查。

引用自：https://dba.stackexchange.com/questions/280635