如何保證 PostgreSQL UPSERT 操作與 RETURNING 的結果一致？

PostgreSQL UPSERT 操作與 RETURNING 子句：處理衝突

PostgreSQL 的 INSERT ... ON CONFLICT 提供 upsert 功能，將插入和更新結合。 但是，將 DO NOTHING 與 RETURNING 子句一起使用可能會導致並發場景中的結果不完整。

併發問題 1：缺少回傳行

如果另一個交易在 INSERT ... ON CONFLICT 完成之前修改了目標行，則更新插入可能無法偵測到衝突，從而導致 RETURNING 輸出中遺失行。

解：

有幾種方法可以緩解這個問題：

• 行計數驗證： 將輸入行計數與 RETURNING 計數進行比較。 差異表示缺少行，提示重新執行語句。
• 強制覆蓋：單獨的CTE（公共表表達式）可以插入任何遺失的行。 雖然有效，但此方法存在因多個重疊事務而導致死鎖的風險。

併發問題2：行鎖

對於需要行鎖定的事務，請使用 ON CONFLICT DO UPDATE 和 WHERE FALSE。這會鎖定行而不改變它們。 將此與 SELECT ... FOR UPDATE 結合使用以實現額外鎖定。

資料型態處理與穩健性轉換

現有的解決方案不足以應付所有並發場景。 更全面的方法包括：

低併發：

• 使用帶有 INSERT 和 SELECT UNION 的 CTE 來區分插入的行和選定的行。
• 使用 CTE 定義輸入資料類型，將它們與目標表架構對齊。

高併發：

• 使用低併發法。
• 包含 CTE，以便在需要時強制插入缺少的行。
• 鎖定INSERT ... ON CONFLICT CTE 中的相關行以管理並發。

避免死鎖：

一致的插入順序可以最大限度地減少死鎖的發生。

資料型別管理：

• 利用現有關係自動決定輸入資料類型。
• 省略列名稱以進行自動列對應。

以上是如何保證 PostgreSQL UPSERT 操作與 RETURNING 的結果一致？的詳細內容。更多資訊請關注PHP中文網其他相關文章！