PostgreSQL で ON CONFLICT と RETURNING をどのように組み合わせて使用​​するか? 質問する

このsource列は、これがどのように機能するかを示すためにオプションで追加されています。実際には、両方のケースを区別するために必要になる場合があります (空の書き込みに対するもう 1 つの利点)。

最終的にJOIN chats機能するのは、添付のデータ変更CTE基になるテーブルにはまだ表示されません。(同じ SQL ステートメントのすべての部分は、基になるテーブルの同じスナップショットを参照します。)

VALUES式は独立型 ( に直接接続されていない) であるため、INSERTPostgres はターゲット列からデータ型を派生できず、明示的な型キャストを追加する必要がある場合があります。マニュアル:

VALUESが で使用される場合INSERT、値はすべて対応する宛先列のデータ型に自動的に強制変換されます。 他のコンテキストで使用される場合は、正しいデータ型を指定する必要がある場合があります。 エントリがすべて引用符で囲まれたリテラル定数である場合、最初のエントリを強制変換するだけで、すべてに想定される型を決定できます。

クエリ自体 (副作用は除く) は、CTE のオーバーヘッドと追加(定義により完全なインデックスが存在するため、これは安価であるはずで、一意の制約はインデックスを使用して実装されます) により、重複SELECTが少数の場合、少し高価になる可能性があります。

重複が多い場合は (はるかに) 高速になる可能性があります。追加書き込みの実際のコストは、多くの要因によって異なります。

しかし、いずれにしても副作用や隠れたコストは少なくなります。全体的にはおそらく安くなります。

競合をテストする前にデフォルト値が入力されるため、添付されたシーケンスは依然として高度です。

CTE について:

• ネストできるのは SELECT タイプのクエリだけですか?
• リレーショナル分割におけるSELECT文の重複排除

同時書き込み負荷あり

デフォルトを想定READ COMMITTEDトランザクション分離。 関連している：

• 同時トランザクションにより、挿入時に一意制約による競合状態が発生します。

競合状態を防ぐための最善の戦略は、正確な要件、テーブルと UPSERT 内の行の数とサイズ、同時トランザクションの数、競合の可能性、使用可能なリソース、その他の要因によって異なります...

同時実行の問題 1

同時実行トランザクションが、現在 UPSERT しようとしている行に書き込んでいる場合、トランザクションは他のトランザクションが終了するまで待機する必要があります。

他のトランザクションがROLLBACK（または何らかのエラー、つまり自動ROLLBACK）で終了した場合、トランザクションは正常に続行されます。軽微な副作用が発生する可能性があります: 連番にギャップが生じます。ただし、行が欠落することはありません。

他のトランザクションが正常に終了した場合 (暗黙的または明示的COMMIT)、 はINSERT競合 (UNIQUEインデックス/制約が絶対的)を検出するため、行を返しません。 (また、行は表示されていないため、以下の同時実行の問題 2DO NOTHINGで示すように行をロックすることもできません。) は、クエリの開始から同じスナップショットを参照するため、まだ非表示の行を返すこともできません。SELECT

このような行は、結果セットに存在しません (基になるテーブルに存在している場合でも)。

これは、そのままでも問題ないかもしれません。特に、例のように行を返さず、行が存在することを知っていれば十分である場合はそうです。それでも十分でない場合は、さまざまな回避策があります。

出力の行数をチェックし、入力の行数と一致しない場合はステートメントを繰り返すことができます。まれなケースではこれで十分かもしれません。ポイントは、新しいクエリ (同じトランザクション内でもよい) を開始することです。これにより、新しくコミットされた行が表示されます。

または、同じクエリ内で欠落している結果行をチェックし、それをブルートフォーストリックで上書きします。アレクストニの答え。

WITH input_rows(usr, contact, name) AS ( ... )  -- see above
, ins AS (
   INSERT INTO chats AS c (usr, contact, name) 
   SELECT * FROM input_rows
   ON     CONFLICT (usr, contact) DO NOTHING
   RETURNING id, usr, contact                   -- we need unique columns for later join
   )
, sel AS (
   SELECT 'i'::"char" AS source                 -- 'i' for 'inserted'
        , id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's'::"char" AS source                 -- 's' for 'selected'
        , c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (                                      -- RARE corner case
   INSERT INTO chats AS c (usr, contact, name)  -- another UPSERT, not just UPDATE
   SELECT i.*
   FROM   input_rows i
   LEFT   JOIN sel   s USING (usr, contact)     -- columns of unique index
   WHERE  s.usr IS NULL                         -- missing!
   ON     CONFLICT (usr, contact) DO UPDATE     -- we've asked nicely the 1st time ...
   SET    name = c.name                         -- ... this time we overwrite with old value
   -- SET name = EXCLUDED.name                  -- alternatively overwrite with *new* value
   RETURNING 'u'::"char" AS source              -- 'u' for updated
           , id  --, usr, contact               -- return more columns?
   )
SELECT source, id FROM sel
UNION  ALL
TABLE  ups;

...
ON CONFLICT (usr, contact) DO UPDATE
SET name = name WHERE FALSE  -- never executed, but still locks the row
...

WITH input_rows AS (
  (SELECT usr, contact, name FROM chats LIMIT 0)  -- only copies column names and types
   UNION ALL
   VALUES
      ('foo1', 'bar1', 'bob1')  -- no type casts here
    , ('foo2', 'bar2', 'bob2')
   )
   ...

WITH input_rows AS (
   SELECT * FROM (
      VALUES
      ((NULL::chats).*)         -- copies whole row definition
      ('foo1', 'bar1', 'bob1')  -- no type casts needed
    , ('foo2', 'bar2', 'bob2')
      ) sub
   OFFSET 1
   )
   ...