Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xTNvf-00000000IVq-2Be5 for pgsql-bugs@arkaria.postgresql.org; Tue, 17 Nov 2026 18:29:16 +0000 Received: from localhost ([127.0.0.1]:56772 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xTNve-00000006ZFm-3uWE for pgsql-bugs@arkaria.postgresql.org; Tue, 17 Nov 2026 18:29:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:34746) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xTKdU-00000005kwb-1qgG for pgsql-bugs@lists.postgresql.org; Tue, 17 Nov 2026 14:58:16 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xTKdN-00000000Bhz-14zF for pgsql-bugs@lists.postgresql.org; Tue, 17 Nov 2026 14:58:16 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=0R1FuGASinx0H/KMgl5EXuErAHzmx7JYB/dxFuy1Hr8=; b=SV79ANUJUl43Ujx+4FkcXnGaKL st+A+nGgBVjI7dpazXwsIwwg95wsOa4O9hX1GPfjD1YLOf87+IDwNQ4CL0lbYofMzUs7/R2aVb8zD RQ3Zj5yetzq6Cxt+L03n2KZrfg/WVpmjc3dGHcheDHURDQ5d+e9YASQTX5wAS+RCIb4oUkpTVcTQS cL668PB/VH+kFVzbDEzO5bD0vF6RK79i64lJRtqpEdMKyr+3yiXPJQpvZZUbw3JF03h5mJV3UgKwy 8K3JfBJ7HbFr26Am7k9szTbwp3xlSW9tHPUsytOPCBos482iVmDhSYo3IUk7G6tcuzglmXONZMqrE J3dHKguw==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xTKdI-00000000TEN-0Qh2 for pgsql-bugs@lists.postgresql.org; Tue, 17 Nov 2026 14:58:07 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xTKdG-00000001AMW-2MhA for pgsql-bugs@lists.postgresql.org; Tue, 17 Nov 2026 14:58:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ) To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 303677365@qq.com Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org Date: Tue, 17 Nov 2026 14:57:39 +0000 Message-ID: <19912-ceea4a1c0a7dd41c@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19912 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: Unsupported/Unknown Operating system: Linux x86_64; master build 81b2179b492 (20devel) Description: =20 **PostgreSQL version:** 20devel master build (commit 81b2179b492, built 2026-10-27). The MERGE WHEN NOT MATCHED BY SOURCE feature and the EvalPlanQual handling involved date back to PG17, so released versions may be affected as well; I have not verified on a release build. **Operating system:** Linux x86_64 **Description:** For a MERGE command with a WHEN NOT MATCHED BY SOURCE action, if a target row that matched the source when the MERGE started is concurrently updated so that it no longer satisfies the join condition, the executor re-evaluates the join via EvalPlanQual and (correctly) runs the NOT MATCHED BY SOURCE action for that row. However, that action and its RETURNING clause observe stale, non-NULL source column values =C3=A2=C2=80=C2=94 the values from the originally-matched source row =C3=A2=C2=80=C2=94 instead of NULL, which is = what a genuine not-matched-by-source row sees. This is internally inconsistent: a single MERGE statement returns different source column values for the two kinds of NMBS rows (genuine vs. concurrently-unmatched). The stale value is visible in RETURNING, is stored into the table when referenced in the SET clause, and can select a different NMBS action branch when referenced in a WHEN ... AND qual over a source column. The official regression test (src/test/regress/sql/merge.sql, the bug18634 test added with the NMBS feature) asserts that source columns are NULL for NOT MATCHED BY SOURCE rows, and prepjointree.c comments that the join subplan "nulls the output from the source relation when the join condition doesn't match" =C3=A2=C2=80=C2=94 the semantics an NMBS action is entitled = to rely on. **Reproduction (READ COMMITTED, two sessions):** Setup: ```sql CREATE TABLE b2_t (a int, b int, c text); CREATE TABLE b2_s (a int, c text); INSERT INTO b2_s VALUES (1, 's1_val'); INSERT INTO b2_t VALUES (1, 10, 'orig1'), (2, 20, 'orig2'); ``` Session B (holds an uncommitted row lock): ```sql BEGIN; UPDATE b2_t SET a =3D 999 WHERE a =3D 1; ``` Session A (blocks on the row locked by B; then B COMMITs): ```sql MERGE INTO b2_t t USING b2_s s ON t.a =3D s.a WHEN MATCHED THEN UPDATE SET b =3D 777 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET b =3D 888, c =3D 'NMBS' RETURNING merge_action(), t.a, t.b, t.c, s.c AS returning_src; ``` Observed in session A (after B commits): ```text merge_action | t.a | t.b | t.c | returning_src --------------+-----+-----+------+--------------- UPDATE | 999 | 888 | NMBS | s1_val <-- EPQ-transitioned row: stale non-NULL source value UPDATE | 2 | 888 | NMBS | <-- genuine NMBS row: NULL, as expected ``` Control (no concurrency): starting from the same final logical state reached directly, the same MERGE returns NULL for both rows: ```sql TRUNCATE b2_s, b2_t; INSERT INTO b2_s VALUES (1, 's1_val'); INSERT INTO b2_t VALUES (999, 10, 'orig1'), (2, 20, 'orig2'); -- run the same MERGE as above ``` ```text merge_action | t.a | t.b | t.c | returning_src --------------+-----+-----+------+--------------- UPDATE | 2 | 888 | NMBS | UPDATE | 999 | 888 | NMBS | ``` So the same final state, reached through a concurrent update, yields a different (stale) source value for the EPQ-transitioned row. The stale value is not only visible in RETURNING; it is stored into the table when the NMBS action references the source column. With a join source: ```sql CREATE TABLE b2_a (a int, c text); CREATE TABLE b2_b (a int, c text); INSERT INTO b2_a VALUES (1, 's1_val'); INSERT INTO b2_b VALUES (1, 's2_s1_val'); INSERT INTO b2_t VALUES (1, 10, 'orig'); -- Session B: BEGIN; UPDATE b2_t SET a =3D 999 WHERE a =3D 1; -- Session A (then B commits): MERGE INTO b2_t t USING (b2_a s JOIN b2_b s2 ON s.a =3D s2.a) ON t.a =3D s.a WHEN MATCHED THEN UPDATE SET b =3D 777 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET c =3D 'NMBS_' || coalesce(s.c, 'NULLSRC') RETURNING merge_action(), t.a, t.c, s.c AS returning_src; ``` Observed: ```text merge_action | t.a | t.c | returning_src --------------+-----+--------------+--------------- UPDATE | 999 | NMBS_s1_val | s1_val <-- stale source value stored in the table ``` A genuine NMBS row (same setup, no concurrency, target row a=3D2) stores 'NMBS_NULLSRC'. With a second NMBS action guarded by a qual on the source column: ```sql WHEN NOT MATCHED BY SOURCE AND s.c IS NOT NULL THEN UPDATE SET c =3D 'WRONG_BRANCH' WHEN NOT MATCHED BY SOURCE THEN UPDATE SET c =3D 'CORRECT_BRANCH' ``` the EPQ-transitioned row takes the first branch (observed c=3D'WRONG_BRANCH= '), while the genuine control takes CORRECT_BRANCH. **Root cause:** In ExecMergeMatched() (src/backend/executor/nodeModifyTable.c), when the target tuple was concurrently updated (TM_Updated), the join quals are re-evaluated with the updated target tuple via EvalPlanQual, but neither context->planSlot nor econtext->ecxt_innertuple is refreshed from the EPQ re-evaluation result. When the re-check concludes the row no longer matches and the executor transitions to the NOT MATCHED BY SOURCE action, the action's projection (ExecProject) and RETURNING (ExecProcessReturning(..., context->planSlot)) therefore read the stale source tuple from the original plan slot. Regular UPDATE/DELETE pass the EPQ tuple back to callers via *epqreturnslot and build the new tuple from it; MERGE's MATCHED=C3=A2=C2=86= =C2=92NMBS transition has no equivalent refresh.