同時トランザクションを伴う 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 中国語 Web サイトの他の関連記事を参照してください。