ON CONFLICT DO NOTHING を指定して PostgreSQL の RETURNING 句を確実に使用する方法

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 が完了する前に別のトランザクションが行を変更すると、不整合が発生する可能性があります。

これを軽減するには、既存の行を早めにロックすることを検討してください。 2 番目の 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>

これらのアプローチは、同時書き込み圧力下でも、挿入された行と既存の行の両方のデータを確実に返します。

以上がON CONFLICT DO NOTHING を指定して PostgreSQL の RETURNING 句を確実に使用する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。