如何以原子方式替換錶數據的子集

在 PostgreSQL 9.6 我有一個T這樣的表

category | id | data
---------+----+------
A        | 1  | foo
A        | 2  | bar
A        | 3  | baz
B        | 4  | eh
B        | 5  | whatcomesafterfoobarbaz

有一個視圖V給我的數據T，所以它有列category, id, data。T本質上是 的物化視圖V，除了我需要以比“刷新所有內容”更精細的方式刷新它。

所以我會選擇V例如

SELECT * FROM V WHERE category = 'A';

要麼

SELECT * FROM V WHERE category = 'A' AND id = 2;

並用給我T的任何內容替換相關行。data V不幸的是我不能做一個簡單的UPDATE：問V例如。forWHERE category = 'A'可能會給我一組與以前完全不同的行。因此我需要做這個序列：

DELETE FROM T WHERE <condition>;
INSERT INTO T (SELECT FROM V WHERE <condition>);

<condition>要麼WHERE category = ?要麼WHERE category = ? AND id = ?。

我該怎麼做才能滿足以下條件？

• 從不令人滿意的行中讀取<condition>應該不受影響。
• 更改應該是原子的，這意味著從滿足的行中讀取<condition>應該看到舊行集或新行集，而不是混合。

注意：與這個問題不同，我不想一次替換整個表 - 只有受影響的行。

添加了詳細資訊

• 讀比寫多，大約是 10-100 倍。每次寫入後，都會讀取相鄰類別。該應用程序正在查看一組categories，ids並一次data更新data一個或多個categories。之後它將重新獲取categories並顯示它們，並且它必須看到新鮮的data. 所有的ids 總是用 “their” 獲取category。
• 每個category都會有 1-10 ids 之類的東西，會有數万個categories.

第一次回答後的更多細節

• 事務可以同時執行。肯定有兩個事務以 . 開頭的情況DELETE FROM T WHERE category = 'A';。
• 有一個categories可以鎖定行的表FOR UPDATE。還有一個id可以鎖定 s的表FOR UPDATE。
• RETURNING在這裡沒有多大意義，因為我需要獲取的不僅僅是更改的行。因此，使用單獨的SELECT.

並發讀取不是問題。READ COMMITTED在預設隔離級別下，寫入器不會阻塞讀取器，反之亦然。將DELETE和INSERT包含在單個事務中以使操作原子化（全部應用或不應用）。

如果可以同時嘗試寫入多個事務，那將改變遊戲規則。單個事務可以保護您免受不一致的更新，但它不能保護您免受並發事務之間的競爭條件：死鎖。

假設我們有兩個交易T1和T2，類別“A”有 10 個 ID：

T1: DELETE FROM T WHERE category = 'A';
-- starts taking row locks in arbitrary order: id 1,2,3,4,5,6,7 ...
                   T2: DELETE FROM T WHERE category = 'A';
                   -- starts taking row locks in arbitrary order: id 10, 9, 8, ...
T1: wait for T2 to release lock on id 8
                   T2: wait for T1 to release lock on id 7

DEADLOCK.

Postgres 在一段時間後檢測到死鎖並終止兩個事務之一。（報告死鎖錯誤。）

您可以切換到SERIALIZABLE事務隔離。但這要貴得多，在這種情況下，您需要為序列化失敗做好準備並重試。

或者，您可以通過始終以相同的確定順序刪除行來避免該問題。喜歡：

WITH del AS (
  SELECT category, id
  FROM   T
  WHERE  category = 'A'
  ORDER  BY category, id  -- enforce this order in *all* writing queries
  FOR    UPDATE
  )
DELETE FROM T 
USING  del
WHERE  T.category = del.category
AND    T.id = del.id;

但通常，有一個更方便的選擇。如果您有一個單獨的表，其中包含名為的唯一類別，例如cat，您可以使用以下命令鎖定單個父行cat：

SELECT * FROM cat WHERE category = 'A' FOR UPDATE;

然後（在同一個事務中）隨意寫入“A”類行T（仍然封裝在單個事務中以避免中間的、不一致的狀態可見）。當然，所有的寫作查詢都必須遵循相同的協議。cat然後，並發事務將在寫入之前等待鎖定，T並且一切都很正常……

在 Postgres 9.4或更高版本中，請考慮FOR NO KEY UPDATE：

• 如何根據行數執行條件插入？

---

關於：

每次寫入後，都會讀取相鄰類別。

你知道這個RETURNING條款，對吧？如果您只是插入給定類別的所有行，則無需單獨讀取。例子：

• PostgreSQL multi INSERT…RETURNING 多列

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