ホーム

[解決済み】PostgreSQLでUPSERT（MERGE、INSERT ... ON DUPLICATE UPDATE）する方法とは？

2022-05-02 15:13:38

質問

よくある質問は、アップサートを行う方法です。 INSERT ... ON DUPLICATE UPDATE の一部として、標準ではサポートされています。 MERGE 演算を行う。

PostgreSQLが（pg 9.5以前は）直接サポートしていないことを考えると、どのようにこれを行うのでしょうか？次のように考えてみてください。

CREATE TABLE testtable (
    id integer PRIMARY KEY,
    somedata text NOT NULL
);

INSERT INTO testtable (id, somedata) VALUES
(1, 'fred'),
(2, 'bob');

ここで、タプルを "upsert" したい場合を想像してください。 (2, 'Joe') , (3, 'Alan') ということで、新しいテーブルのコンテンツはこうなります。

(1, 'fred'),
(2, 'Joe'),    -- Changed value of existing tuple
(3, 'Alan')    -- Added new tuple

を議論するとき、人々はそれについて話しているのです。 upsert . 重要なのは、どのようなアプローチであっても 同じテーブルで作業している複数のトランザクションがあっても安全です。 - 明示的なロックをかけるか、レースコンディションを防ぐか、どちらかです。

このトピックは、以下のサイトで詳しく説明されています。 PostgreSQLで重複更新の挿入は可能ですか？しかし、それは MySQL 構文の代替案に関するものであり、時間の経過とともに関連性のない詳細がかなり増えています。私は決定的な答えに取り組んでいるところです。

これらのテクニックは、quot;insert if not exists, otherwise do nothing" つまり "insert ... on duplicate key ignore" にも有効です。

解決方法は？

9.5以降です。

PostgreSQL 9.5 およびそれ以降のサポート INSERT ... ON CONFLICT (key) DO UPDATE (そして ON CONFLICT (key) DO NOTHING )、すなわちアップサートを行います。

との比較 ON DUPLICATE KEY UPDATE .

簡単な説明 .

使用方法についてはマニュアル - 特に コンフリクトアクション という節があり、シンタックスダイアグラムでは説明文 .

以下に示す 9.4 以前の解決策とは異なり、この機能は競合する複数の行に対して動作し、排他ロックや再試行ループは必要ありません。

この機能を追加したコミットはこちらと開発に関するディスカッションはこちら .

9.5を使用していて、後方互換性が必要ない場合は、今すぐ読むのをやめてもかまいません。 .

9.4以前のバージョン。

PostgreSQLには組み込みの UPSERT (または MERGE そのため、同時使用に対して効率的に行うことは非常に困難です。

この記事では、この問題について有益な詳細を説明しています。 .

一般的には、2つの選択肢のどちらかを選ぶ必要があります。

再試行ループ内の個々の挿入/更新操作、または
テーブルをロックしてバッチマージする

個別行リトライループ

再試行ループで個別の行のアップサートを使用することは、挿入を実行するために多くの接続を同時に使用する場合、合理的なオプションです。

PostgreSQLのドキュメントには、これをデータベース内のループで実行させる便利なプロシジャが含まれています。 . これは、多くの素朴な解決策とは異なり、更新の喪失や挿入の競合から保護します。この方法は READ COMMITTED モードであり、トランザクションで行うことがこれだけである場合のみ安全ですが。この関数は、トリガーやセカンダリユニークキーがユニーク違反を引き起こす場合、正しく動作しません。

この戦略は非常に非効率的です。実用的な場合はいつでも、作業をキューに入れ、代わりに以下のようにバルクアップサートを行うべきです。

この問題に対する多くの解決策は、ロールバックを考慮していないため、不完全な更新になってしまう。2つのトランザクションが互いに競合し、そのうちの1つが正常に INSERT もう一方は重複キーエラーになり UPDATE の代わりにその UPDATE を待つブロックがあります。 INSERT 条件を再確認すると、0 行にマッチします。 UPDATE をコミットしても、実際には期待したアップサートは行われません。結果の行数をチェックし、必要な場合は再試行する必要があります。

また、いくつかの解決策は、SELECTレースについて考慮していません。もし、明白で単純なものを試すなら。

UPDATE

では、2つ同時に実行した場合、いくつかの失敗モードがあります。ひとつは、すでに述べた更新の再チェックの問題です。もうひとつは、両方の -- THIS IS WRONG. DO NOT COPY IT. It's an EXAMPLE. BEGIN; UPDATE testtable SET somedata = 'blah' WHERE id = 2; -- Remember, this is WRONG. Do NOT COPY IT. INSERT INTO testtable (id, somedata) SELECT 2, 'blah' WHERE NOT EXISTS (SELECT 1 FROM testtable WHERE testtable.id = 2); COMMIT; を同時に行い、0列をマッチングして続ける。次に二人は UPDATE テストが発生します。以前は EXISTS . どちらも0行になるので、どちらも INSERT . 一方は重複キーエラーで失敗します。

そのため、再試行ループが必要なのです。巧妙なSQLを使えば重複キーエラーや更新の失敗を防げると思うかもしれませんが、そうではありません。行数をチェックするか、重複キーエラーを処理するか（選択した方法による）、そして再試行が必要です。

これについては、自分で解決策を講じないでください。メッセージ・キューイングと同じで、おそらく間違っています。

ロック付き一括アップサート

新しいデータセットを古い既存のデータセットにマージしたい場合、一括アップサートを行いたいことがあります。これは 大いに は、個々の行のアップサートに比べてより効率的であり、実用的な場合は常に優先されるべきです。

この場合、一般的には以下のような流れになります。

INSERT a CREATE テーブル
TEMPORARY あるいは、新しいデータを temp テーブルに一括挿入します。
COPY ターゲットテーブル LOCK . これにより、他のトランザクションが IN EXCLUSIVE MODE しかし、テーブルには一切変更を加えません。
を実行します。 SELECT を、temp テーブルの値を使って既存のレコードに追加する。
を行う。 UPDATE ... FROM ターゲットテーブルにまだ存在しない行の
INSERT ロックを解除します。

例えば、質問で出された例では、多値の COMMIT を使用して、temp テーブルに入力します。

INSERT

についてはどうでしょうか？ `MERGE` ?

標準SQL MERGE 実際、同時実行のセマンティクスがうまく定義されておらず、最初にテーブルをロックせずにアップサートを行うのには適していません。

OLAPのデータマージにはとても便利な文ですが、実はコンカレンシーセーフなアップサートに役立つソリューションではありません。他の DBMS を使っている人には MERGE をアップサートに使用することはできますが、実はそれは間違いです。

その他のDB

MERGE MySQLで
INSERT ... ON DUPLICATE KEY UPDATE MS SQL Serverから (ただし、上記の MERGE の問題)
MERGE オラクルより (ただし、上記の MERGE の問題)