如何使用並發事務處理 PostgreSQL UPSERT 中的缺失行？

PostgreSQL UPSERT作業中處理缺失結果行

在PostgreSQL 9.5中，結合使用RETURNING和ON CONFLICT有時會在並發事務更新指定的衝突目標時導致行缺失。

目前已接受答案的缺點

目前已接受的答案在並發交易更新目標表多行時容易出現問題。雖然它可以防止行缺失，但它會引入其他副作用和與空更新相關的效能損失。

替代方案

無併發寫入負載：

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- 第一行中的类型转换
    , ('foo2', 'bar2', 'bob2')
    -- 更多？
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id  --, usr, contact              -- 返回更多列？
   )
SELECT 'i' AS source                           -- 'i' 代表'插入'
     , id  --, usr, contact                    -- 返回更多列？
FROM   ins
UNION  ALL
SELECT 's' AS source                           -- 's' 代表'选择'
     , c.id  --, usr, contact                  -- 返回更多列？
FROM   input_rows
JOIN   chats c USING (usr, contact);           -- 唯一索引的列

有並發寫入負載

為了解決多個並發事務的競爭條件：

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 NOTHING
   RETURNING id, usr, contact                   -- 我们需要唯一的列来进行后续连接
   )
, sel AS (
   SELECT 'i'::"char" AS source                 -- 'i' 代表'插入'
        , id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's'::"char" AS source                 -- 's' 代表'选择'
        , c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (                                      -- 罕见的极端情况
   INSERT INTO chats AS c (usr, contact, name)  -- 另一个UPSERT，不仅仅是UPDATE
   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'::"char" AS source              -- 'u' 代表更新
           , id  --, usr, contact               -- 返回更多列？
   )
SELECT source, id FROM sel
UNION  ALL
TABLE  ups;

關鍵點：

• sel CTE 傳回插入的行和選取的行，提供完整的輸出結果集。
• ups CTE 處理缺少的行，用現有值更新它們。
• 輸出結果集排除了並發事務更新的任何行，除非ups處理它們。

以上是如何使用並發事務處理 PostgreSQL UPSERT 中的缺失行？的詳細內容。更多資訊請關注PHP中文網其他相關文章！