Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE

On Tue, 20 Jan 2026 at 08:58, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> In a CTE that inserts rows with both MERGE and INSERT, the transition table
> will not contain the rows from the MERGE.
>

Thanks for the report.

I took a look at this and was able to reproduce it with a simpler
example, not using MERGE ... RETURNING:

WITH ins AS (
INSERT INTO merge_bug_test (id, val) values (1, 'a')
)
MERGE INTO merge_bug_test t
USING (VALUES (2, 'b')) s(id, val) ON t.id = s.id
WHEN NOT MATCHED THEN
INSERT (id, val) VALUES (s.id, s.val);

2 rows are inserted, but the transition table only includes the
(1,'a') row added by the INSERT command.

With this example, I can confirm that the bug goes all the way back to
PG15 (MERGE ... RETURNING was only added in PG17).

It looks like the problem stems from this code in
MakeTransitionCaptureState() in commands/trigger.c:

table = GetAfterTriggersTableData(relid, cmdType);

where cmdType might be CMD_MERGE.

The problem is that MERGE really needs to use the same
AfterTriggersTableData structs as INSERT, UPDATE, and DELETE, so that
any captured tuples get added to the same tuplestores.

This means that the TransitionCaptureState needs pointers to 3
separate AfterTriggersTableData structs, one for each of INSERT,
UPDATE, and DELETE. It then follows that an AfterTriggersTableData
struct only needs 2 tuplestores (old and new), rather than 4, because
it never has cmdType == CMD_MERGE.

Attached is a rough patch doing that.

I wonder if it would be possible to get rid of
ModifyTableState.mt_oc_transition_capture and just have INSERT ... ON
CONFLICT DO UPDATE use a single TransitionCaptureState in a similar
way to MERGE? Anyway, that's a separate idea, not relevant to this bug
fix.

Regards,
Dean