postgres.git / summary / log / commit / refs
commit 85f55534e80b633bdb7bfc91eaa5793021d6bf66
Author: Dean Rasheed <dean.a.rasheed@gmail.com>
Date: Wed Sep 23 08:39:04 2026 +0000
Fix concurrent delete handling in MERGE ... WHEN NOT MATCHED BY SOURCE.
When executing a WHEN NOT MATCHED BY SOURCE action in MERGE, if the
tuple from the target table was concurrently deleted, the retry code
would incorrectly execute a WHEN NOT MATCHED BY TARGET action for the
row instead, giving incorrect results.
Fix in ExecMergeMatched() -- when handling a TM_Deleted failure
status, it should only set *matched to false (telling the caller to
execute any WHEN NOT MATCHED BY TARGET actions) if the failed action
was a WHEN MATCHED action. For a failed WHEN NOT MATCHED BY SOURCE
action, the source tuple did not exist, and the target tuple has just
been deleted, so it should do nothing.
Back-patch to v17, where support for WHEN NOT MATCHED BY SOURCE
actions was introduced.
Reported-by: Jeff Davis <pgsql@j-davis.com>
Author: Tender Wang <tndrwang@gmail.com>
Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>
Discussion: https://postgr.es/m/ccdab5ba02c65af195b5a6d2d744a01d9de47cd3.camel@j-davis.com
Backpatch-through: 17
src/backend/executor/nodeModifyTable.c | 35 ++++--
src/test/isolation/expected/merge-delete.out | 158 +++++++++++++++++++++++++++
src/test/isolation/specs/merge-delete.spec | 37 +++++++
3 files changed, 220 insertions(+), 10 deletions(-)
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 2632431ea05..558ceece9bb 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -3261,10 +3261,11 @@ ExecMerge(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
* Otherwise, we execute the qualifying action and return its RETURNING
* result, if any, or NULL.
*
- * On entry, "*matched" is assumed to be true. If a concurrent update or
- * delete is detected that causes the join quals to no longer pass, we set it
- * to false, indicating that the caller should process any NOT MATCHED [BY
- * TARGET] actions.
+ * On entry, "*matched" is assumed to be true. If the join quals originally
+ * passed (MATCHED case) and a concurrent update or delete is detected that
+ * causes the join quals to no longer pass, we set "*matched" to false,
+ * indicating that the caller should process any NOT MATCHED [BY TARGET]
+ * actions.
*
* After a concurrent update, we restart from the first action to look for a
* new qualifying action to execute. If the join quals originally passed, and
@@ -3561,10 +3562,16 @@ lmerge_matched:
errmsg("could not serialize access due to concurrent delete")));
/*
- * If the tuple was already deleted, set matched to false to
- * let caller handle it under NOT MATCHED [BY TARGET] clauses.
+ * The target tuple was concurrently deleted by some other
+ * transaction. If this was a MATCHED action, the source row
+ * still exists, so set *matched to false, to let the caller
+ * handle it using any NOT MATCHED [BY TARGET] actions.
+ * Otherwise, for a NOT MATCHED BY SOURCE action, neither the
+ * source row nor the target row now exists, so there is no
+ * futher action to execute.
*/
- *matched = false;
+ if (relaction->mas_action->matchKind == MERGE_WHEN_MATCHED)
+ *matched = false;
goto out;
case TM_Updated:
@@ -3717,10 +3724,18 @@ lmerge_matched:
case TM_Deleted:
/*
- * tuple already deleted; tell caller to run NOT
- * MATCHED [BY TARGET] actions
+ * The target tuple was concurrently deleted by
+ * some other transaction. If this was a MATCHED
+ * action, the source row still exists, so set
+ * *matched to false, to let the caller handle it
+ * using any NOT MATCHED [BY TARGET] actions.
+ * Otherwise, for a NOT MATCHED BY SOURCE action,
+ * neither the source row nor the target row now
+ * exists, so there is no futher action to
+ * execute.
*/
- *matched = false;
+ if (was_matched)
+ *matched = false;
goto out;
case TM_SelfModified:
diff --git a/src/test/isolation/expected/merge-delete.out b/src/test/isolation/expected/merge-delete.out
index 897b9351355..c0e3b2b5e7f 100644
--- a/src/test/isolation/expected/merge-delete.out
+++ b/src/test/isolation/expected/merge-delete.out
@@ -234,3 +234,161 @@ key|val
(1 row)
step c2: COMMIT;
+
+starting permutation: delete merge_wnmbs_src2 c1 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step merge_wnmbs_src2:
+ MERGE INTO target t
+ USING (SELECT 2 AS key, 'merge2' AS val) s ON t.key = s.key
+ WHEN MATCHED THEN UPDATE SET key = t.key + 1, val = t.val || ' match updated by ' || s.val
+ WHEN NOT MATCHED BY SOURCE THEN UPDATE SET key = t.key + 1, val = t.val || ' not matched by source'
+ WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val)
+ RETURNING merge_action(), t.*;
+ <waiting ...>
+step c1: COMMIT;
+step merge_wnmbs_src2: <... completed>
+merge_action|key|val
+------------+---+------
+INSERT | 2|merge2
+(1 row)
+
+step select2: SELECT * FROM target;
+key|val
+---+------
+ 2|merge2
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete_pa merge_wnmbs_src2_pa c1 select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
+step merge_wnmbs_src2_pa:
+ MERGE INTO target_pa t
+ USING (SELECT 2 AS key, 'merge2_pa' AS val) s ON t.key = s.key
+ WHEN MATCHED THEN UPDATE SET key = t.key + 1, val = t.val || ' match updated by ' || s.val
+ WHEN NOT MATCHED BY SOURCE THEN UPDATE SET key = t.key + 1, val = t.val || ' not matched by source'
+ WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val)
+ RETURNING merge_action(), t.*;
+ <waiting ...>
+step c1: COMMIT;
+step merge_wnmbs_src2_pa: <... completed>
+merge_action|key|val
+------------+---+---------
+INSERT | 2|merge2_pa
+(1 row)
+
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---------
+ 2|merge2_pa
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg merge_wnmbs_src2_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step merge_wnmbs_src2_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 2 AS key, 'merge2_tg' AS val) s ON t.key = s.key
+ WHEN MATCHED THEN UPDATE SET key = t.key + 1, val = t.val || ' match updated by ' || s.val
+ WHEN NOT MATCHED BY SOURCE THEN UPDATE SET key = t.key + 1, val = t.val || ' not matched by source'
+ WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val)
+ RETURNING merge_action(), t.*;
+ <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: Insert: (2,merge2_tg)
+step merge_wnmbs_src2_tg: <... completed>
+merge_action|key|val
+------------+---+---------
+INSERT | 2|merge2_tg
+(1 row)
+
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+---------
+ 2|merge2_tg
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: update delete merge_wnmbs_src2 c1 select2 c2
+step update: UPDATE target t SET val = t.val || ' update1' WHERE t.key = 1;
+step delete: DELETE FROM target t WHERE t.key = 1;
+step merge_wnmbs_src2:
+ MERGE INTO target t
+ USING (SELECT 2 AS key, 'merge2' AS val) s ON t.key = s.key
+ WHEN MATCHED THEN UPDATE SET key = t.key + 1, val = t.val || ' match updated by ' || s.val
+ WHEN NOT MATCHED BY SOURCE THEN UPDATE SET key = t.key + 1, val = t.val || ' not matched by source'
+ WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val)
+ RETURNING merge_action(), t.*;
+ <waiting ...>
+step c1: COMMIT;
+step merge_wnmbs_src2: <... completed>
+merge_action|key|val
+------------+---+------
+INSERT | 2|merge2
+(1 row)
+
+step select2: SELECT * FROM target;
+key|val
+---+------
+ 2|merge2
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: update_pa delete_pa merge_wnmbs_src2_pa c1 select2_pa c2
+step update_pa: UPDATE target_pa t SET val = t.val || ' update1' WHERE t.key = 1;
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
+step merge_wnmbs_src2_pa:
+ MERGE INTO target_pa t
+ USING (SELECT 2 AS key, 'merge2_pa' AS val) s ON t.key = s.key
+ WHEN MATCHED THEN UPDATE SET key = t.key + 1, val = t.val || ' match updated by ' || s.val
+ WHEN NOT MATCHED BY SOURCE THEN UPDATE SET key = t.key + 1, val = t.val || ' not matched by source'
+ WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val)
+ RETURNING merge_action(), t.*;
+ <waiting ...>
+step c1: COMMIT;
+step merge_wnmbs_src2_pa: <... completed>
+merge_action|key|val
+------------+---+---------
+INSERT | 2|merge2_pa
+(1 row)
+
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---------
+ 2|merge2_pa
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: update_tg delete_tg merge_wnmbs_src2_tg c1 select2_tg c2
+s1: NOTICE: Update: (1,setup1) -> (1,"setup1 update1")
+step update_tg: UPDATE target_tg t SET val = t.val || ' update1' WHERE t.key = 1;
+s1: NOTICE: Delete: (1,"setup1 update1")
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step merge_wnmbs_src2_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 2 AS key, 'merge2_tg' AS val) s ON t.key = s.key
+ WHEN MATCHED THEN UPDATE SET key = t.key + 1, val = t.val || ' match updated by ' || s.val
+ WHEN NOT MATCHED BY SOURCE THEN UPDATE SET key = t.key + 1, val = t.val || ' not matched by source'
+ WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val)
+ RETURNING merge_action(), t.*;
+ <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: Insert: (2,merge2_tg)
+step merge_wnmbs_src2_tg: <... completed>
+merge_action|key|val
+------------+---+---------
+INSERT | 2|merge2_tg
+(1 row)
+
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+---------
+ 2|merge2_tg
+(1 row)
+
+step c2: COMMIT;
diff --git a/src/test/isolation/specs/merge-delete.spec b/src/test/isolation/specs/merge-delete.spec
index ba5f70e53dc..833ce1fea65 100644
--- a/src/test/isolation/specs/merge-delete.spec
+++ b/src/test/isolation/specs/merge-delete.spec
@@ -50,6 +50,9 @@ setup
step "delete" { DELETE FROM target t WHERE t.key = 1; }
step "delete_pa" { DELETE FROM target_pa t WHERE t.key = 1; }
step "delete_tg" { DELETE FROM target_tg t WHERE t.key = 1; }
+step "update" { UPDATE target t SET val = t.val || ' update1' WHERE t.key = 1; }
+step "update_pa" { UPDATE target_pa t SET val = t.val || ' update1' WHERE t.key = 1; }
+step "update_tg" { UPDATE target_tg t SET val = t.val || ' update1' WHERE t.key = 1; }
step "c1" { COMMIT; }
session "s2"
@@ -65,6 +68,30 @@ step "merge2_pa" { MERGE INTO target_pa t USING (SELECT 1 as key, 'merge2_pa' as
step "merge2_tg" { MERGE INTO target_tg t USING (SELECT 1 as key, 'merge2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
step "merge_delete2" { MERGE INTO target t USING (SELECT 1 as key, 'merge_delete2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; }
step "merge_delete2_tg" { MERGE INTO target_tg t USING (SELECT 1 as key, 'merge_delete2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; }
+step "merge_wnmbs_src2" {
+ MERGE INTO target t
+ USING (SELECT 2 AS key, 'merge2' AS val) s ON t.key = s.key
+ WHEN MATCHED THEN UPDATE SET key = t.key + 1, val = t.val || ' match updated by ' || s.val
+ WHEN NOT MATCHED BY SOURCE THEN UPDATE SET key = t.key + 1, val = t.val || ' not matched by source'
+ WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val)
+ RETURNING merge_action(), t.*;
+}
+step "merge_wnmbs_src2_pa" {
+ MERGE INTO target_pa t
+ USING (SELECT 2 AS key, 'merge2_pa' AS val) s ON t.key = s.key
+ WHEN MATCHED THEN UPDATE SET key = t.key + 1, val = t.val || ' match updated by ' || s.val
+ WHEN NOT MATCHED BY SOURCE THEN UPDATE SET key = t.key + 1, val = t.val || ' not matched by source'
+ WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val)
+ RETURNING merge_action(), t.*;
+}
+step "merge_wnmbs_src2_tg" {
+ MERGE INTO target_tg t
+ USING (SELECT 2 AS key, 'merge2_tg' AS val) s ON t.key = s.key
+ WHEN MATCHED THEN UPDATE SET key = t.key + 1, val = t.val || ' match updated by ' || s.val
+ WHEN NOT MATCHED BY SOURCE THEN UPDATE SET key = t.key + 1, val = t.val || ' not matched by source'
+ WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val)
+ RETURNING merge_action(), t.*;
+}
step "select2" { SELECT * FROM target; }
step "select2_pa" { SELECT * FROM target_pa; }
step "select2_tg" { SELECT * FROM target_tg; }
@@ -94,3 +121,13 @@ permutation "delete_pa" "merge2_pa" "c1" "select2_pa" "c2"
permutation "delete_tg" "merge2_tg" "c1" "select2_tg" "c2"
permutation "delete" "merge_delete2" "c1" "select2" "c2"
permutation "delete_tg" "merge_delete2_tg" "c1" "select2_tg" "c2"
+
+# Concurrent DELETE vs MERGE ... WHEN NOT MATCHED BY SOURCE
+permutation "delete" "merge_wnmbs_src2" "c1" "select2" "c2"
+permutation "delete_pa" "merge_wnmbs_src2_pa" "c1" "select2_pa" "c2"
+permutation "delete_tg" "merge_wnmbs_src2_tg" "c1" "select2_tg" "c2"
+
+# Concurrent UPDATE then DELETE vs MERGE ... WHEN NOT MATCHED BY SOURCE
+permutation "update" "delete" "merge_wnmbs_src2" "c1" "select2" "c2"
+permutation "update_pa" "delete_pa" "merge_wnmbs_src2_pa" "c1" "select2_pa" "c2"
+permutation "update_tg" "delete_tg" "merge_wnmbs_src2_tg" "c1" "select2_tg" "c2"