From: Dean Rasheed <dean.a.rasheed@gmail.com>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix concurrent update trigger issues with MERGE in a CTE.
Date: Fri, 18 Jul 2025 09:03:24 +0000
Message-ID: <E1uch00-006tVG-0m@gemulon.postgresql.org> (raw)
Fix concurrent update trigger issues with MERGE in a CTE.
If a MERGE inside a CTE attempts an UPDATE or DELETE on a table with
BEFORE ROW triggers, and a concurrent UPDATE or DELETE happens, the
merge code would fail (crashing in the case of an UPDATE action, and
potentially executing the wrong action for a DELETE action).
This is the same issue that 9321c79c86 attempted to fix, except now
for a MERGE inside a CTE. As noted in 9321c79c86, what needs to happen
is for the trigger code to exit early, returning the TM_Result and
TM_FailureData information to the merge code, if a concurrent
modification is detected, rather than attempting to do an EPQ
recheck. The merge code will then do its own rechecking, and rescan
the action list, potentially executing a different action in light of
the concurrent update. In particular, the trigger code must never call
ExecGetUpdateNewTuple() for MERGE, since that is bound to fail because
MERGE has its own per-action projection information.
Commit 9321c79c86 did this using estate->es_plannedstmt->commandType
in the trigger code to detect that a MERGE was being executed, which
is fine for a plain MERGE command, but does not work for a MERGE
inside a CTE. Fix by passing that information to the trigger code as
an additional parameter passed to ExecBRUpdateTriggers() and
ExecBRDeleteTriggers().
Back-patch as far as v17 only, since MERGE cannot appear inside a CTE
prior to that. Additionally, take care to preserve the trigger ABI in
v17 (though not in v18, which is still in beta).
Bug: #18986
Reported-by: Yaroslav Syrytsia <me@ys.lc>
Author: Dean Rasheed <dean.a.rasheed@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://postgr.es/m/18986-e7a8aac3d339fa47@postgresql.org
Backpatch-through: 17
Branch
------
REL_18_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/27c7c11366f72b1933e298481954a24c742036de
Modified Files
--------------
src/backend/commands/trigger.c | 74 ++++++++++++++--------
src/backend/executor/execReplication.c | 4 +-
src/backend/executor/nodeModifyTable.c | 6 +-
src/include/commands/trigger.h | 6 +-
.../isolation/expected/merge-match-recheck.out | 27 +++++---
src/test/isolation/specs/merge-match-recheck.spec | 22 ++++---
6 files changed, 89 insertions(+), 50 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: dean.a.rasheed@gmail.com, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Fix concurrent update trigger issues with MERGE in a CTE.
In-Reply-To: <E1uch00-006tVG-0m@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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