當 PostgreSQL 中的計數器達到零時，如何有效地刪除記錄？

考慮一個帶有計數器欄位的記錄，該欄位將被遞減。當它的值達到零（這是常見情況）時，我希望刪除記錄。在 PostgreSQL 中執行此操作的最有效方法是什麼？

天真的方式涉及到兩個 SQL 語句和兩個在表中的搜尋：aSELECT獲取計數器值，然後是 aDELETE或 an UPDATE。

一種替代方法涉及a ，僅當計數器的返回值為零時才UPDATE … RETURNING跟 a 。DELETE但是，這會在計數器值為 1 的情況下執行不需要的記錄更改，以犧牲預期的常見情況為代價優化不常見的情況（計數器的值大於 1）。

另一種確實優化了預期的常見情況的替代方案涉及 a DELETE … WHERE … AND counter = 1，然後是UPDATE當沒有刪除發生時的 a 。

兩種選擇都可能需要對錶中的記錄進行浪費的第二次搜尋。

是否可以通過使用游標始終避免兩次表搜尋並提高操作效率？我在 PostgreSQL 文件中沒有看到這個案例的範例。

針對單行，這避免了“不需要的記錄更改”，即無需編寫新的行版本：

DO
$$
BEGIN
  DELETE FROM tbl WHERE … AND counter = 1;  -- common case first!

  IF NOT FOUND THEN
     UPDATE tbl SET counter = counter - 1 WHERE …;
  END IF;
END
$$;

還應該比觸發器解決方案便宜，觸發器解決方案為每個受影響的行執行觸發器功能（並且可能會或可能不會干擾）。

但是，這不會與可能的並發寫入操作一起飛行。當兩個或多個事務幾乎同時嘗試相同的事務時，邏輯可能會中斷。READ COMMITTED或者在預設事務隔離中的兩個命令之間可能會看到額外的行。

這是問題本身固有的，而不是我的解決方案。（同樣適用於其他解決方案。）在並發寫入負載下，您必須至少對該行進行寫鎖定以避免競爭條件，因此您將返回兩次查找該行。不過，在任何情況下，您都可以避免編寫不需要的行（例如UPDATE+ ）。DELETE

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