PostgreSQL 相對更新打破了唯一約束

我在 UPSERT 的列上有唯一索引。當我嘗試使用v = v + 1表達式更新列時，我的唯一索引會中斷。

SQL

CREATE TABLE test(v bigint, data jsonb DEFAULT '{}'::jsonb);
INSERT INTO test(v) SELECT vv FROM generate_series(0, 10000) as vv;
CREATE UNIQUE INDEX uniq_ind ON test(v);

UPDATE test SET v = v + 1;

我試過的：

1. 使用延遲約束，但它不適用於 UPSERT。
2. 用於對磁碟上的行進行排序，因此更新將CLUSTER具有特定的順序並且不會破壞索引。問題是我必須CLUSTER在每個查詢之前打電話，這非常昂貴。
3. 手動實現 UPSERT 似乎很複雜且性能不佳。（postgres wiki 同意我的觀點https://wiki.postgresql.org/wiki/UPSERT#PostgreSQL_.28today.29）
4. 使用多列唯一索引(v, flag)。為此，我需要添加標誌列並替換索引

ALTER TABLE test ADD COLUMN flag bool DEFAULT false;
CREATE UNIQUE INDEX uniq_ind ON test(v, flag);

然後 UPDATE 和 UPSERT 看起來像

-- UPDATE
UPDATE test SET v = v + 1, flag = true;
UPDATE test SET flag = false;
--UPSERT
INSERT INTO test(v) VALUES (123) ON CONFLICT (v, flag) DO UPDATE SET v = EXCLUDED.v;

但與簡單更新相比，它具有 x2 成本。目前它是最合適的解決方案。

UPDATE 案例或 UPSERT 案例的替代方案是什麼，所以我可以：

1. 有效地 UPSERT 行。（此操作佔優）
2. 使用諸如 之類的表達式更新許多記錄v = v + 1。

每次更新的規模約為 1k-10k 行。表中大約有 1m-10m 條記錄。

我贊同 Laurenz 的評論：通常最好避免在一UNIQUE開始就對專欄進行此類更新。

如果這不可能，一種解決方法是在子查詢中對行進行排序並自連接：

UPDATE test t
SET    v = t.v + 1
FROM  (SELECT * FROM test ORDER BY v DESC) t_ordered  -- additional  WHERE clauses?
WHERE  t_ordered.v = t.v;

db<>在這裡擺弄

這通常有效。但是子查詢中沒有鎖定任何行，因此該命令對於並發寫入不是防彈的。如果你願意，你必須寫鎖整個表，或者使用SERIALIZABLE事務隔離。兩者對於並發訪問來說都是昂貴的。

有關的：

• Postgres 更新…限制 1

另外，您提到：

每次更新 1k-10k 行。表中大約有 1m-10 行。

所以UPDATE仍然會與未更新的行發生衝突。

要更新整個表，請考慮在更新之前刪除UNIQUE約束，然後在同一事務中重新創建它。當然，這需要一個獨占的寫鎖。但這在更新整個表格時似乎沒問題。無論如何，重新創建索引比增量更新所有行要便宜，並且您會得到一個原始的（去膨脹的、重新索引的）唯一索引作為副作用。

與指向該UNIQUE列的 FK 約束衝突，但……

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