はじめに
PostgreSQLで、既存の行がなければINSERTし、既存行より新しいデータであれば更新、同じか古いデータであれば更新しない、という制御を行いたい場合についてです。
※ 楽観的並行性制御について：
https://dev.classmethod.jp/articles/elasticsearch-getting-started-03/
https://www.elastic.co/jp/blog/elasticsearch-versioning-support
基本的にはUPSERTの一種ですが、PostgreSQLではUPSERTは大まかに以下2種類の書き方ができます。INSERT ON CONFLICTでは、UPDATE内のWHERE句に、MERGEではWHEN MATCHEDのCONDITIONに、INSERTするか否か（ここでは、より新しいデータか否か）の判定条件を記述できます。
create table t (id int primary key, value text, date timestamp);
insert into t
(id,value,date) values (2,'A','2024-09-24')
on conflict (id)
do update set value=excluded.value, date=excluded.date
where t.date < excluded.date;
このdate列は、外部で発生したデータの発生時刻が格納されている列と考えて下さい。トリガーによるupdated_atカラムなどを用意して、DB内部の更新時刻で楽観的並行性制御を行う場合はwhere句が少し変わります。
merge into t
using (values (2,'A','2024-09-24'::timestamp)) as i (id, value, date)
on t.id = i.id
when matched and t.date < i.date then
update set value=i.value, date=i.date
when not matched then
insert (id,value,date) values (i.id,i.value,i.date);
これらは一見同じ結果が得られるように見えますが、ドキュメントには「2つの文タイプの間には様々な違いや制限があり、相互に交換することはできません。」と書かれています。
具体的な違いとして、PostgreSQL 「MERGE」と「INSERT ON CONFLICT」でのUPSERTの違いでも述べられているように、同時実行性に違いがあります。
単一SQL文として実行する場合は違いが見えにくいですが、何か他のテーブルへの操作を行うなどtransactionを用いる場合 は差が見えてきます。
検証
INSERT ON CONFLICT
--transaction T1
begin; --ISOLATION LEVELはREAD COMMITTEDとしています。
insert into t
(id,value,date) values (2,'A','2024-09-24')
on conflict (id)
do update set value=excluded.value, date=excluded.date
where t.date < excluded.date; --INSERT 0 1
--transaction T2
begin;
insert into t
(id,value,date) values (2,'A','2024-09-25')
on conflict (id)
do update set value=excluded.value, date=excluded.date
where t.date < excluded.date;
--T1のcommitまでblockされて待機
commit; --success
--INSERT 0 1
commit;--success
より新しいデータをinsertしようとすると、T1 commitまでupdateがブロックされ、T1 commit後にINSERT 0 1と1行insert on conflictの結果が返ってきます。INSERTとありますが、updateで影響された行数を示しています。
--transaction T1
begin;
insert into t
(id,value,date) values (3,'A','2024-09-24')
on conflict (id)
do update set value=excluded.value, date=excluded.date
where t.date < excluded.date; --INSERT 0 1
--transaction T2
begin;
insert into t
(id,value,date) values (3,'A','2024-09-23')
on conflict (id)
do update set value=excluded.value, date=excluded.date
where t.date < excluded.date;
--T1のcommitまでblockされて待機
commit; --success
--INSERT 0 0
commit;--success
より古いデータをinsertしようとした場合も、T1 commitまでinsertがブロックされ、T1 commit後にINSERT 0 0と、「INSERT/UPDATEされなかった」という結果が返ってきます。いずれにしても T2 transactionは成功します。
MERGE
次にMERGE文を使った場合を見ていきます。
--transaction T1
begin;
merge into t
using (values (5,'A','2024-09-24'::timestamp)) as i (id, value, date)
on t.id = i.id
when matched and t.date < i.date then
update set value=i.value, date=i.date
when not matched then
insert (id,value,date) values (i.id,i.value,i.date); --MERGE 1
--transaction T2
begin;
merge into t
using (values (5,'A','2024-09-25'::timestamp)) as i (id, value, date)
on t.id = i.id
when matched and t.date < i.date then
update set value=i.value, date=i.date
when not matched then
insert (id,value,date) values (i.id,i.value,i.date);
--T1のcommitまでblockされて待機
commit; --sucess
--ERROR: duplicate key value violates unique constraint "t3_pkey"
--DETAIL: Key (id)=(5) already exists.
commit;
--ERROR: current transaction is aborted,
--commands ignored until end of transaction block.
より新しいデータであっても、MERGEで検出されずにWHEN NOT MATCHED句のINSERTが実行され、blockされた上に最後は 一意性違反でabort してしまいます。なお、古いデータの場合も検出できずにINSERTされて一意性違反になります。
T1, T2より前に既存データが存在する場合の挙動については、気になる方は手元で確かめてみて下さい。
まとめ
INSERT ON CONFLICT文では、INSERTがブロックされて、T1 commit後にINSERTが成功したかどうか（duplicate keyが発生したか）が判定される一方、MERGE文ではあくまで見えているCOMMITに対してMERGEが実行され、MATCHED/NOT MATCHEDが判定された後のINSERTがブロックされている、と考えると、この挙動になることが理解できます。
INSERTの結果で分岐するのか、分岐してからINSERTするのかの違いに起因すると考えられます。
insertのボリュームやconflictの発生する頻度にも依りかもしれませんが、今回のようなケースでは基本的にはMERGE文よりもINSERT ON CONFLICT文を使った方が良さそうです。
参考