はじめに
AWS Aurora PostgreSQLもv15.3がリリースされた。
15でリリースされたMERGEについて、9.5でリリースされた「INSERT ON CONFLICT」とUPSERTの違いについて調べてみた。
「MERGE」と「INSERT ON CONFLICT」でのUPSERTの違い
トランザクション処理時の同時実行性が違う。
検証
検証テーブル
PostgreSQL
CREATE TABLE sp_user.merge_test
(id integer,
value varchar(100),
constraint pk_merge_test primary key (id)
);
Oracle
CREATE TABLE sp_user.merge_test
(id NUMBER(10, 0) PRIMARY KEY,
value varchar2(100)
);
MERGE
トランザクション実行前
データ空
| id(PK) | value |
|---|---|
トランザクション実行
| トランザクションA | トランザクションB |
|---|---|
| begin | |
| begin | |
| insert into sp_user.merge_test (id, value) values(1, 'TEST1'); |
|
| MERGE INTO sp_user.merge_test t USING (SELECT 1 AS id, 'TEST2' AS value) i ON (t.id = i.id) WHEN MATCHED THEN UPDATE SET value = i.value WHEN NOT MATCHED THEN INSERT (id, value) VALUES (i.id, i.value); |
|
| commit | |
| commit |
トランザクションAで「commit」実行
INSERT 0 1
トランザクションBで「commit」実行
ERROR: duplicate key value violates unique constraint "pk_merge_test"
DETAIL: Key (id)=(1) already exists.
となった。
トランザクション実行後
| id(PK) | value |
|---|---|
| 1 | TEST1 |
INSERT ON CONFLICT
トランザクション実行前
データ空
| id(PK) | value |
|---|---|
トランザクション実行
| トランザクションA | トランザクションB |
|---|---|
| begin | |
| begin | |
| insert into sp_user.merge_test (id, value) values(1, 'TEST1'); |
|
| INSERT INTO sp_user.merge_test VALUES (1, 'TEST2') ON CONFLICT ON CONSTRAINT pk_merge_test DO UPDATE SET value=EXCLUDED.value; |
|
| commit | |
| commit |
トランザクションAで「commit」実行
INSERT 0 1
トランザクションBで「commit」実行
INSERT 0 1
となった。
トランザクション実行後
| id(PK) | value |
|---|---|
| 1 | TEST2 |
OracleでMERGE
トランザクション実行前
データ空
| id(PK) | value |
|---|---|
トランザクション実行
| トランザクションA | トランザクションB |
|---|---|
| insert into sp_user.merge_test (id, value) values(1, 'TEST1'); |
|
| MERGE INTO sp_user.merge_test t USING (SELECT 1 AS id, 'TEST2' AS value FROM dual) i ON (t.id = i.id) WHEN MATCHED THEN UPDATE SET t.value = i.value WHEN NOT MATCHED THEN INSERT (t.id, t.value) VALUES (i.id, i.value); |
|
| commit | |
| commit |
トランザクションAで「commit」実行
コミットが完了しました。
トランザクションBで「commit」実行
行1でエラーが発生しました。:
ORA-00001: 一意制約(SP_USER.SYS_C001846831)に反しています
となった。
トランザクション実行後
| id(PK) | value |
|---|---|
| 1 | TEST1 |
検証結果
- MERGEの場合、トランザクション開始時点のmerge_testテーブルのスナップショットを使ってINSERT, UPDATEの判断をする為に重複エラー（一意制約違反）となった。
- 同時実行時にどちらの挙動で実行されるほうがアプリにとって整合性が保てるのか確認して「MERGE」か「INSERT ON CONFLICT」かを選択したほうがよさそう。
- データメンテで同時実行を意識する必要がない場合はどっちでもよさそう。
- OracleとMERGEの挙動は同じだった。
トリガー？？
それについてはまたこんど。
おまけ
postgres15 merge vs insert-on-conflict
PostgresでINSERT ON CONFLICTコマンドがある理由
さて、私が面白いと思ったのは なぜPostgresにはずっと前からそれがなかったのか？Postgres 9.5では、INSERT ON CONFLICTという異なる構文が追加されました。MERGEのドキュメントを参照してください：
INSERT ON CONFLICTは、同時INSERTが発生した場合にUPDATEを実行する代替文として使用することもできます。この2つのステートメントタイプには違いや制限があり、互換性はありません。
この2つのステートメントにはトレードオフがあり、MERGEを使用したくない場合にINSERT ON CONFLICTを使用することがあります。
そのような状況とはどのようなものかと思うかもしれない。INSERT ON CONFLICT機能の最初の作者であるピーター・ゲーガン（Peter Geoghegan）は、人々がこのことについて話していたとき、Twitterで非常に参考になるコメントをした。
彼が本質的に述べたコメントは、MERGEの同時並行処理の欠点は、同時INSERT、つまりMERGEステートメントの実行と同時に別のINSERTが行われている場合、MERGEがそれに気づかない可能性があるということです。MERGEはINSERTロジックに入り、一意性違反を起こす。
彼が最初にINSERT ON CONFLICT機能を設計した時、MVCCがPostgresでどのように動作するかのこの制約のために、一般的な構文とオプションを持つMERGEを同じ信頼性のある方法で動作するように実装することはできないということを思い出しました。MERGEの汎用性を求めるのであれば、同時挿入がある場合、一意の制約違反が発生する可能性があるという事実を受け入れなければなりません。一方、INSERT ON CONFLICTでは、投機的挿入が設計されているため、INSERTまたはUPDATEのどちらかが保証され、同時挿入がある場合でもそれが当てはまります。この保証が必要な場合は、INSERT ON CONFLICTを選択することをお勧めします。
deeplで翻訳