如何可靠地使用 PostgreSQL 的 RETURNING 子句和 ON CONFLICT DO NOTHING？

PostgreSQL 的 RETURNING 子句和 ON CONFLICT DO NOTHING

PostgreSQL 的 RETURNING 子句對於在 INSERT 操作後從新插入的行中檢索資料非常有用。這對於使用 ON CONFLICT 的 UPSERT（插入或更新）特別有用。 使用 ON CONFLICT DO NOTHING 時會出現一個常見的挑戰：如果沒有衝突，RETURNING 不會產生任何結果。

雖然嘗試更新匹配行以觸發 RETURNING 似乎是一個解決方案，但強烈建議不要這樣做。 潛在的缺點包括：

• 不必要的觸發活化
• 無意的行寫鎖定
• 將現有行錯誤地表示為新行
• 由於行版本控製過多而導致效能下降

存在更有效率、更強大的替代方案。

處理沒有並發寫入的場景：

對於沒有大量並發寫入活動的環境，CTE（通用表表達式）與最終的 JOIN 相結合提供了一個優雅的解決方案。 這可確保插入的行和現有的行都包含在輸出中：

<code class="language-sql">WITH input_rows(usr, contact, name) AS (
   VALUES
      ('foo1', 'bar1', 'bob1')
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id
   )
SELECT 'i' AS source, id
FROM   ins
UNION  ALL
SELECT 's' AS source, c.id
FROM   input_rows
JOIN   chats c USING (usr, contact);</code>

解決並發寫入負載：

並發寫入操作會帶來複雜性。 如果另一個事務在您的 INSERT 完成之前修改了一行，則可能會出現不一致。

為了緩解這種情況，請考慮儘早鎖定現有行。 第二個 UPSERT 步驟可以處理任何遺失的行：

<code class="language-sql">WITH input_rows(usr, contact, name) AS ( ... )
, ins AS (
   INSERT INTO chats AS c (usr, contact, name) 
   SELECT * FROM input_rows
   ON     CONFLICT (usr, contact) DO UPDATE
   SET    name = name WHERE FALSE  -- Locks the row without updating
   RETURNING id, usr, contact
   )
, sel AS (
   SELECT 'i' AS source, id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's' AS source, c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (
   INSERT INTO chats AS c (usr, contact, name)
   SELECT i.*
   FROM   input_rows i
   LEFT   JOIN sel   s USING (usr, contact)
   WHERE  s.usr IS NULL
   ON     CONFLICT (usr, contact) DO UPDATE
   SET    name = c.name
   RETURNING 'u' AS source, id
   )
SELECT source, id FROM sel
UNION  ALL
TABLE  ups;</code>

即使在同時寫入壓力下，這些方法也能可靠地傳回插入行和現有行的資料。

以上是如何可靠地使用 PostgreSQL 的 RETURNING 子句和 ON CONFLICT DO NOTHING？的詳細內容。更多資訊請關注PHP中文網其他相關文章！