MERGE behavior with REPEATABLE READ isolation level
Alexander Korotkov <aekorotkov@gmail.com>
From: Alexander Korotkov <aekorotkov@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2026-02-15T01:20:49Z
Lists: pgsql-hackers
Hi hackers,
I found it strange that ExecMergeMatched() checks for
IsolationUsesXactSnapshot() in the TM_Deleted case, but not in the
TM_Updated case. Indeed, EPQ works on the repeatable read isolation level!
s1# create table test (id int primary key, val int);
s1# insert into test values (1,0);
s2# begin;
s2# update test set val = val + 100;
s1# MERGE INTO test t USING (VALUES (1, 100)) AS s (id, inc)
ON t.id = s.id
WHEN MATCHED THEN
UPDATE SET val = t.val + s.inc
WHEN NOT MATCHED THEN
INSERT (id, val) VALUES (s.id, s.inc);
(waiting ...)
s2# commit;
s1# MERGE 1
s1# select * from test;
id | val
----+-----
1 | 200
(1 row)
Quick search didn't give an explanation of this to me. I also don't see
this covered by an isolation test. Is it an intended behavior or a bug?
(Sorry for buzz if this was discussed before)
------
Regards,
Alexander Korotkov
Supabase
Commits
-
Fix handling of updated tuples in the MERGE statement
- f6e63d4b8b2e 16.14 landed
- 8bfaae6fb20c 15.18 landed
- 2dcac93c0065 17.10 landed
- 13fab378e630 18.4 landed
- 177037341a44 19 (unreleased) landed