在 Postgres 中限制匹配條件的行數

假設我有這樣的數據庫架構

user_id text
photo_id text
date_created timestamp

我想強制執行“使用者只能擁有 30 張照片”的約束。實現這一點的一種天真的方法是

SELECT COUNT(*) FROM user_photos WHERE user_id='usr_123'

將結果與 30 進行比較，如果較低，則進行插入。天真，因為如果您可以在短時間內觸發多個請求，您就可以贏得一場比賽並最終寫入 30 多條記錄。

有沒有辦法在數據庫中強制執行這個約束？例如，創建某種索引，上面寫著“只能插入 30 行”，還是什麼？有沒有辦法讓count每個 userId 分別增加一個列，並且您可以將其範圍限制為0-29？

如果您使用事務，會鎖定您讀取的行嗎？

您的程序可以手動為每個使用者的記錄編號，然後您可以對 user_id 和 recordnumber 的組合設置唯一約束（或使其成為主鍵），並對 recordnumber 設置檢查約束，使其介於 1 到 30 之間。

類似於@Eelke和@Kassandry 的建議。但是您不需要計數器或觸發器。

CREATE TABLE t1 (
 user_id text NOT NULL  -- should probably be integer
, photo_nr int NOT NULL
, photo_id text NOT NULL
, date_created timestamptz NOT NULL DEFAULT now()
, CONSTRAINT t1_pkey PRIMARY KEY (user_id, photo_nr)
, CONSTRAINT max_30_photos CHECK (photo_nr BETWEEN 1 AND 30)
);

關鍵問題是如何便宜又安全地獲得下一個免費時段。我建議：

  SELECT photo_nr
  FROM   generate_series (1,30) photo_nr
  EXCEPT (SELECT photo_nr FROM t1 WHERE user_id = 'usr123') -- your user_id here
  ORDER  BY 1
  LIMIT  1;

細節：

• 選擇第一列中不存在第二列值的行

photo_nr這將為給定使用者返回下一個免費的。以此為基礎你的INSERT陳述：

INSERT INTO t1 (user_id, photo_nr, photo_id)
SELECT 'usr123', photo_nr, 'my_new_photo_id'  -- provide values here
FROM  (
  SELECT photo_nr
  FROM   generate_series (1,30) photo_nr
  EXCEPT (SELECT photo_nr FROM t1 WHERE user_id = 'usr123')  -- given user_id
  ORDER  BY 1
  LIMIT  1
  ) sub;

如果沒有空閒插槽，則不插入任何內容（如命令標記所報告的那樣）。

這對於並發請求是**不安全的。**下一個空閒photo_nr對於所有並發事務都是相同的。它可能導致並發的獨特違規行為INSERT。但是在那些非常罕見的情況下擷取該錯誤並重試通常更便宜，而不是鎖定整個表（您不能鎖定 Postgres 中尚不存在的行）或專門為給定使用者鎖定所有行。

您可以將其包裝INSERT在 PL/pgSQL 函式中以重試獨特的違規行為。程式碼範例：

• 函式中的 SELECT 或 INSERT 是否容易出現競爭條件？

如果這是一個問題，一個廉價的替代方法是鎖定users表中的父行以排除任何競爭條件。看：

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

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