pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedwrong results: merge when not matched by source
9+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2026-09-23 85f55534e8 Fix concurrent delete handling in MERGE ... WHEN NOT MATCHED BY SOURCE.
* wrong results: merge when not matched by source
@ 2026-09-17 19:17 Jeff Davis <pgsql@j-davis.com>
0 siblings, 1 reply; 9+ messages in thread
From: Jeff Davis @ 2026-09-17 19:17 UTC (permalink / raw)
To: pgsql-bugs; +Cc: Dean Rasheed <dean.a.rasheed@gmail.com>
AI-discovered bug report appended to this email.
Regards,
Jeff Davis
-- MERGE WHEN NOT MATCHED BY SOURCE + concurrent DELETE inserts a
-- null-source row. Two sessions, READ COMMITTED. Affects 17+.
--
-- Setup (either session):
DROP TABLE IF EXISTS target;
CREATE TABLE target (key int, val text);
INSERT INTO target VALUES (1, 'matched'), (2, 'nms');
-- Session 1:
BEGIN ISOLATION LEVEL READ COMMITTED;
DELETE FROM target WHERE key = 2; -- holds the NMS row
-- Session 2 (blocks on the NMS row):
BEGIN ISOLATION LEVEL READ COMMITTED;
MERGE INTO target t
USING (SELECT 1 AS key, 'src' AS val) s
ON t.key = s.key
WHEN MATCHED THEN UPDATE SET val = s.val
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET val = 'nms-action'
WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val)
RETURNING merge_action(), t.*;
-- Session 1:
COMMIT; -- unblocks session 2
-- Session 2 then returns:
--
-- merge_action | key | val
-- --------------+-----+-----
-- INSERT | |
-- UPDATE | 1 | src
--
-- Expected: only UPDATE of key=1; table is {(1, src)}.
-- Actual: also INSERT of (NULL, NULL).
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: wrong results: merge when not matched by source
@ 2026-09-18 02:02 Tender Wang <tndrwang@gmail.com>
parent: Jeff Davis <pgsql@j-davis.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tender Wang @ 2026-09-18 02:02 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: pgsql-bugs@postgresql.org, Dean Rasheed <dean.a.rasheed@gmail.com>
Jeff Davis <pgsql@j-davis.com> 于2026年9月18日周五 03:18写道：
>
> -- Expected: only UPDATE of key=1; table is {(1, src)}.
> -- Actual: also INSERT of (NULL, NULL).
I think the problem is that the TM_Deleted case in
ExecMergeMatched() unconditionally sets *matched to false.
This is correct when the candidate row was originally a MATCHED row:
the source row still exists after the target row is concurrently
deleted, so it should be processed by the NOT MATCHED BY TARGET
actions.
However, it is not correct when processing an original NOT MATCHED BY
SOURCE row. In that case, the source row was already absent, and after
the concurrent deletion of the target row, there is no candidate row
left to process. Setting *matched to false causes ExecMerge() to
call ExecMergeNotMatched(), which executes the NOT MATCHED BY TARGET
INSERT using the null source side of the original join row.
A tentative fix is to set *matched to false only if the current
action is a MATCHED action, as in the attached patch.
There is another TM_Deleted case below, reached while handling
TM_Updated and following the update chain:
...
case TM_Deleted:
*matched = false;
goto out;
...
I think that case may need similar treatment. The existing
was_matched variable could perhaps be used there:
...
if (was_matched)
*matched = false;
...
I have left that case unchanged for now because I would like to
confirm whether the same state distinction applies to all paths
through that code.
The attached patch is intended to show the proposed fix and does not
yet include a regression test.
--
Thanks,
Tender Wang
Attachments:
0001-Fix-merge-issue.patch (text/plain, 1.4K) download
From ce92b29829742dd8a8e094cb9f680d62659f20da Mon Sep 17 00:00:00 2001
From: Tender Wang <tndrwang@gmail.com>
Date: Fri, 18 Sep 2026 10:00:25 +0800
Subject: [PATCH] Fix merge issue
---
src/backend/executor/nodeModifyTable.c | 16 +++++++++++++---
1 file changed, 13 insertions(+), 3 deletions(-)
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 2632431ea05..fc993069d0e 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -3561,10 +3561,20 @@ 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.
+ * If this was a MATCHED case, the source row still exists, so
+ * let the caller handle it under NOT MATCHED [BY TARGET]
+ * clauses.
+ *
+ * If this was originally a NOT MATCHED BY SOURCE case, leave
+ * matched true. Since neither a source row nor a target row
+ * now exists, there is no further action to execute.
+ *
+ * If this was originally MATCHED but a concurrent update had
+ * already changed it to NOT MATCHED BY SOURCE, matched is
+ * already false and should remain so.
*/
- *matched = false;
+ if (relaction->mas_action->matchKind == MERGE_WHEN_MATCHED)
+ *matched = false;
goto out;
case TM_Updated:
--
2.43.0
* Re: wrong results: merge when not matched by source
@ 2026-09-18 11:03 Dean Rasheed <dean.a.rasheed@gmail.com>
parent: Tender Wang <tndrwang@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Dean Rasheed @ 2026-09-18 11:03 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: Jeff Davis <pgsql@j-davis.com>; pgsql-bugs
On Fri, 18 Sept 2026 at 03:02, Tender Wang <tndrwang@gmail.com> wrote:
>
> Jeff Davis <pgsql@j-davis.com> 于2026年9月18日周五 03:18写道：
> >
> > -- Expected: only UPDATE of key=1; table is {(1, src)}.
> > -- Actual: also INSERT of (NULL, NULL).
>
>
> I think the problem is that the TM_Deleted case in
> ExecMergeMatched() unconditionally sets *matched to false.
>
> This is correct when the candidate row was originally a MATCHED row:
> the source row still exists after the target row is concurrently
> deleted, so it should be processed by the NOT MATCHED BY TARGET
> actions.
>
> However, it is not correct when processing an original NOT MATCHED BY
> SOURCE row. In that case, the source row was already absent, and after
> the concurrent deletion of the target row, there is no candidate row
> left to process. Setting *matched to false causes ExecMerge() to
> call ExecMergeNotMatched(), which executes the NOT MATCHED BY TARGET
> INSERT using the null source side of the original join row.
Yes, that analysis is correct. If there was no source row, and the
target row is concurrently deleted, then there's nothing left to do.
It shouldn't execute a NOT MATCHED BY TARGET action if there was never
a source row to begin with.
> A tentative fix is to set *matched to false only if the current
> action is a MATCHED action, as in the attached patch.
That fix looks good to me. Nice and simple.
> There is another TM_Deleted case below, reached while handling
> TM_Updated and following the update chain:
> ...
> case TM_Deleted:
> *matched = false;
> goto out;
> ...
>
> I think that case may need similar treatment. The existing
> was_matched variable could perhaps be used there:
> ...
> if (was_matched)
> *matched = false;
Yes, that's basically the same. It can be triggered by a slight
modification to the original test case, making Session 1 do an UPDATE
before the DELETE:
-- Session 1:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE target SET val = 'nms-updated' WHERE key = 2; -- holds the NMS row
DELETE FROM target WHERE key = 2;
> The attached patch is intended to show the proposed fix and does not
> yet include a regression test.
Attached is an update with the fix for the second case, and tests for both.
Regards,
Dean
Attachments:
v2-fix-concurrent-delete-issue-with-merge-when-not-matched-by-source.patch (text/x-patch, 11.3K) download
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index 2632431..a0efe7e
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -3561,10 +3561,21 @@ 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 case, the source row
+ * still exists, so let the caller handle it using any NOT
+ * MATCHED [BY TARGET] actions.
+ *
+ * If this was originally a NOT MATCHED BY SOURCE case, leave
+ * matched true. Since neither a source row nor a target row
+ * now exists, there is no further action to execute.
+ *
+ * If this was originally MATCHED but a concurrent update had
+ * already changed it to NOT MATCHED BY SOURCE, matched is
+ * already false and should remain so.
*/
- *matched = false;
+ if (relaction->mas_action->matchKind == MERGE_WHEN_MATCHED)
+ *matched = false;
goto out;
case TM_Updated:
@@ -3717,10 +3728,24 @@ 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
+ * case, the source row still exists, so let the
+ * caller handle it using any NOT MATCHED [BY
+ * TARGET] actions.
+ *
+ * If this was originally a NOT MATCHED BY SOURCE
+ * case, leave matched true. Since neither a
+ * source row nor a target row now exists, there
+ * is no further action to execute.
+ *
+ * If this was originally MATCHED but a concurrent
+ * update had already changed it to NOT MATCHED BY
+ * SOURCE, matched is already false and should
+ * remain so.
*/
- *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
new file mode 100644
index 897b935..c0e3b2b
--- 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
new file mode 100644
index ba5f70e..833ce1f
--- 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
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"
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"
* Re: wrong results: merge when not matched by source
@ 2026-09-18 12:21 Dean Rasheed <dean.a.rasheed@gmail.com>
parent: Dean Rasheed <dean.a.rasheed@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Dean Rasheed @ 2026-09-18 12:21 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: Jeff Davis <pgsql@j-davis.com>; pgsql-bugs
On Fri, 18 Sept 2026, 12:03 Dean Rasheed, <dean.a.rasheed@gmail.com> wrote:
>
> That fix looks good to me. Nice and simple.
>
Actually, the last part of the new comment is wrong/unnecessary. If it
changes matched from true to false, retrying, it will have locked the
target tuple, and it can't be concurrently deleted when it retries. It
never retries more than once. So the comment can be simplified a bit.
Regards,
Dean
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: wrong results: merge when not matched by source
@ 2026-09-18 12:51 Tender Wang <tndrwang@gmail.com>
parent: Dean Rasheed <dean.a.rasheed@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Tender Wang @ 2026-09-18 12:51 UTC (permalink / raw)
To: Dean Rasheed <dean.a.rasheed@gmail.com>; +Cc: Jeff Davis <pgsql@j-davis.com>; pgsql-bugs
Dean Rasheed <dean.a.rasheed@gmail.com> 于2026年9月18日周五 20:22写道：
>
> On Fri, 18 Sept 2026, 12:03 Dean Rasheed, <dean.a.rasheed@gmail.com> wrote:
>>
>>
>> That fix looks good to me. Nice and simple.
>
>
> Actually, the last part of the new comment is wrong/unnecessary. If it changes matched from true to false, retrying, it will have locked the target tuple, and it can't be concurrently deleted when it retries. It never retries more than once. So the comment can be simplified a bit.
>
Thanks for confirming.
I also noticed the following part of the comment at the beginning of
`ExecMerge()`:
...
If the target tuple gets deleted or a concurrent update causes the join
quals to fail, it returns a matched status of false and we call
ExecMergeNotMatched().
...
With this change, perhaps this part of the comment should also be
adjusted to distinguish an original `WHEN MATCHED` case from an
original `WHEN NOT MATCHED BY SOURCE` case.
For the former, a concurrent delete leaves the source tuple to be
processed by `WHEN NOT MATCHED [BY TARGET]` actions, whereas for the
latter, neither a source tuple nor a target tuple remains, so there is
no further action to execute.
Do you think it's worth updating this comment as part of the patch?
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: wrong results: merge when not matched by source
@ 2026-09-18 13:08 Dean Rasheed <dean.a.rasheed@gmail.com>
parent: Tender Wang <tndrwang@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Dean Rasheed @ 2026-09-18 13:08 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: Jeff Davis <pgsql@j-davis.com>; pgsql-bugs
On Fri, 18 Sept 2026, 13:52 Tender Wang, <tndrwang@gmail.com> wrote:
> Dean Rasheed <dean.a.rasheed@gmail.com> 于2026年9月18日周五 20:22写道：
> >
> > On Fri, 18 Sept 2026, 12:03 Dean Rasheed, <dean.a.rasheed@gmail.com>
> wrote:
> >>
> >>
> >> That fix looks good to me. Nice and simple.
> >
> >
> > Actually, the last part of the new comment is wrong/unnecessary. If it
> changes matched from true to false, retrying, it will have locked the
> target tuple, and it can't be concurrently deleted when it retries. It
> never retries more than once. So the comment can be simplified a bit.
> >
>
> Thanks for confirming.
>
> I also noticed the following part of the comment at the beginning of
> `ExecMerge()`:
>
> ...
> If the target tuple gets deleted or a concurrent update causes the join
> quals to fail, it returns a matched status of false and we call
> ExecMergeNotMatched().
> ...
>
> With this change, perhaps this part of the comment should also be
> adjusted to distinguish an original `WHEN MATCHED` case from an
> original `WHEN NOT MATCHED BY SOURCE` case.
>
> For the former, a concurrent delete leaves the source tuple to be
> processed by `WHEN NOT MATCHED [BY TARGET]` actions, whereas for the
> latter, neither a source tuple nor a target tuple remains, so there is
> no further action to execute.
>
> Do you think it's worth updating this comment as part of the patch?
>
Sure. I'm AFK at the moment, so if you want to have a crack at that, I'll
take a look when I get back.
Regards,
Dean
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: wrong results: merge when not matched by source
@ 2026-09-19 08:43 Dean Rasheed <dean.a.rasheed@gmail.com>
parent: Tender Wang <tndrwang@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Dean Rasheed @ 2026-09-19 08:43 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: Jeff Davis <pgsql@j-davis.com>; pgsql-bugs
On Fri, 18 Sept 2026 at 13:52, Tender Wang <tndrwang@gmail.com> wrote:
>
> I also noticed the following part of the comment at the beginning of
> `ExecMerge()`:
>
> If the target tuple gets deleted or a concurrent update causes the join
> quals to fail, it returns a matched status of false and we call
> ExecMergeNotMatched().
>
> With this change, perhaps this part of the comment should also be
> adjusted to distinguish an original `WHEN MATCHED` case from an
> original `WHEN NOT MATCHED BY SOURCE` case.
>
> Do you think it's worth updating this comment as part of the patch?
OK, here is an update doing that.
Since a new v19 beta is about to be released, I'll hold off on pushing
this until later next week.
Regards,
Dean
Attachments:
v3-0001-Fix-concurrent-delete-handling-in-MERGE-.-WHEN-NO.patch (text/x-patch, 13.3K) download
From cb848422ab67077567fa7ee3a3b2f2d9367a8a65 Mon Sep 17 00:00:00 2001
From: Dean Rasheed <dean.a.rasheed@gmail.com>
Date: Sat, 19 Sep 2026 09:32:21 +0100
Subject: [PATCH v3] 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 any 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 there is nothing to do.
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"
--
2.51.0
* Re: wrong results: merge when not matched by source
@ 2026-09-20 01:41 Tender Wang <tndrwang@gmail.com>
parent: Dean Rasheed <dean.a.rasheed@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tender Wang @ 2026-09-20 01:41 UTC (permalink / raw)
To: Dean Rasheed <dean.a.rasheed@gmail.com>; +Cc: Jeff Davis <pgsql@j-davis.com>; pgsql-bugs
Dean Rasheed <dean.a.rasheed@gmail.com> 于2026年9月19日周六 16:43写道：
> OK, here is an update doing that.
>
> Since a new v19 beta is about to be released, I'll hold off on pushing
> this until later next week.
Thanks for working on this.
By the way, since you are looking at MERGE, I wonder if you might also
have a chance to look at the other issue reported earlier involving a
prepared MERGE in [1].
I am not even sure whether the reported behavior should be considered a bug.
I proposed a tentative fix in that thread, but I would appreciate your
thoughts on both the expected behavior and the proposed approach.
[1] https://www.postgresql.org/message-id/19689-0a1b106035cd8a5b%40postgresql.org
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: wrong results: merge when not matched by source
@ 2026-09-23 09:01 Dean Rasheed <dean.a.rasheed@gmail.com>
parent: Tender Wang <tndrwang@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Dean Rasheed @ 2026-09-23 09:01 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: Jeff Davis <pgsql@j-davis.com>; pgsql-bugs
On Sun, 20 Sept 2026 at 02:41, Tender Wang <tndrwang@gmail.com> wrote:
>
> Dean Rasheed <dean.a.rasheed@gmail.com> 于2026年9月19日周六 16:43写道：
> > OK, here is an update doing that.
> >
> > Since a new v19 beta is about to be released, I'll hold off on pushing
> > this until later next week.
Pushed and back-patched to v17.
> By the way, since you are looking at MERGE, I wonder if you might also
> have a chance to look at the other issue reported earlier involving a
> prepared MERGE in [1].
> I am not even sure whether the reported behavior should be considered a bug.
> I proposed a tentative fix in that thread, but I would appreciate your
> thoughts on both the expected behavior and the proposed approach.
>
> [1] https://www.postgresql.org/message-id/19689-0a1b106035cd8a5b%40postgresql.org
I'll try and have a look.
Regards,
Dean
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-09-23 09:01 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-17 19:17 wrong results: merge when not matched by source Jeff Davis <pgsql@j-davis.com>
2026-09-18 02:02 ` Tender Wang <tndrwang@gmail.com>
2026-09-18 11:03 ` Dean Rasheed <dean.a.rasheed@gmail.com>
2026-09-18 12:21 ` Dean Rasheed <dean.a.rasheed@gmail.com>
2026-09-18 12:51 ` Tender Wang <tndrwang@gmail.com>
2026-09-18 13:08 ` Dean Rasheed <dean.a.rasheed@gmail.com>
2026-09-19 08:43 ` Dean Rasheed <dean.a.rasheed@gmail.com>
2026-09-20 01:41 ` Tender Wang <tndrwang@gmail.com>
2026-09-23 09:01 ` Dean Rasheed <dean.a.rasheed@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox