While I was running some isolation tests for MERGE, I noticed one issue when MERGE tries to UPDATE rows that are concurrently updated by another session.
Below is the test case for the same.
==================== TEST CASE START =============================
DROP TABLE target;
DROP TABLE source;
CREATE TABLE source (id int primary key, balance int);
INSERT INTO source VALUES (1, 100);
INSERT INTO source VALUES (2, 200);
CREATE TABLE target (id int primary key, balance int);
INSERT INTO target VALUES (1, 10);
INSERT INTO target VALUES (2, 20);
Session 1:
begin;
UPDATE target SET balance = balance + 1;
select * from target;
Session 2:
begin;
MERGE INTO target t
USING (SELECT * from source) s
ON (s.id = t.id)
WHEN MATCHED THEN
UPDATE SET balance = t.balance + s.balance
WHEN NOT MATCHED THEN
INSERT (id, balance) VALUES (s.id, s.balance);
< MERGE will wait because the rows are locked by Session 1 >
Session 1:
commit;
Session 2:
SELECT * FROM target;
commit;
================================ TEST CASE END =================================
The MERGE fails with the error :
ERROR: duplicate key value violates unique constraint "target_pkey"
DETAIL: Key (id)=(2) already exists.
However, the above test case works fine when the target table has only one matching row with the source table. When there are multiple matching rows and those rows are concurrently updated, only the first record gets updated in MERGE. The subsequent records fail to update and return from ExecMergeMatched( ) from the below place and enter into the WHEN NOT MATCHED INSERT flow.