はじめに

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文を使った方が良さそうです。

参考