pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE
9+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2026-01-30 fff87cb50d 661d55e257 b06e7c10d1 7023650429 Update .abi-compliance-history for change to TransitionCaptureState.
2026-01-24 b4307ae2e5 c6ce4dcf9d c5fc17ddac e7391bbf14 c5824536e7 Fix trigger transition table capture for MERGE in CTE queries.
* BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE
@ 2026-01-19 19:26 PG Bug reporting form <noreply@postgresql.org>
2026-01-20 15:59 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Dean Rasheed <dean.a.rasheed@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: PG Bug reporting form @ 2026-01-19 19:26 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: dwwoelfel@gmail.com
The following bug has been logged on the website:
Bug reference: 19380
Logged by: Daniel Woelfel
Email address: dwwoelfel@gmail.com
PostgreSQL version: 17.7
Operating system: macOS (aarch64)
Description:
In a CTE that inserts rows with both MERGE and INSERT, the transition table
will not contain the rows from the MERGE.
I have included a small reproduction script, which inserts 2 rows with a
merge and one row with an insert. On my machine, the trigger outputs: `Row
count: 1, Rows: [{"id":3,"val":"c"}]`, but I would expect it to output: `Row
count: 3, Rows: [{"id": 1, "val": "a"}, {"id": 2, "val": "b"},
{"id":3,"val":"c"}]`
```
CREATE TEMP TABLE merge_bug_test (id INT PRIMARY KEY, val TEXT);
-- Create trigger functions that list the IDs they see
CREATE OR REPLACE FUNCTION report_insert_rows()
RETURNS TRIGGER AS $$
BEGIN
RAISE WARNING '[AFTER INSERT TRIGGER] Row count: %, Rows: %', (SELECT
COUNT(*) FROM newrows), (SELECT
COALESCE(json_agg(row_to_json(newrows))::text, 'EMPTY') FROM newrows);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Create statement-level triggers for INSERT
CREATE TRIGGER insert_trigger
AFTER INSERT ON merge_bug_test
REFERENCING NEW TABLE AS newrows
FOR EACH STATEMENT
EXECUTE FUNCTION report_insert_rows();
-- MERGE inserts rows, but INSERT CTE inserts nothing
WITH
input_triples (id, val) AS (
VALUES (1, 'a'), (2, 'b')
),
insert_cte AS (
INSERT INTO merge_bug_test (id, val) values (3, 'c')
RETURNING id
),
-- Insert two row with merge
merge_cte AS (
MERGE INTO merge_bug_test t
USING input_triples s
ON t.id = s.id
WHEN NOT MATCHED THEN
INSERT (id, val) VALUES (s.id, s.val)
RETURNING t.id
)
-- Insert one row with a regular insert
SELECT id FROM merge_cte
UNION ALL
SELECT id FROM insert_cte;
DROP TABLE merge_bug_test;
DROP FUNCTION report_insert_rows;
```
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE
2026-01-19 19:26 BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE PG Bug reporting form <noreply@postgresql.org>
@ 2026-01-20 15:59 ` Dean Rasheed <dean.a.rasheed@gmail.com>
2026-01-20 17:21 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Dean Rasheed @ 2026-01-20 15:59 UTC (permalink / raw)
To: dwwoelfel@gmail.com; pgsql-bugs@lists.postgresql.org
On Tue, 20 Jan 2026 at 08:58, PG Bug reporting form
<noreply@postgresql.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
Attachments:
fix-transition-table-capture-in-merge-in-cte.patch (text/x-patch, 12.1K) download
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
new file mode 100644
index d30fda6..149bd0d
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -3919,21 +3919,10 @@ struct AfterTriggersTableData
bool after_trig_done; /* did we already queue AS triggers? */
AfterTriggerEventList after_trig_events; /* if so, saved list pointer */
- /*
- * We maintain separate transition tables for UPDATE/INSERT/DELETE since
- * MERGE can run all three actions in a single statement. Note that UPDATE
- * needs both old and new transition tables whereas INSERT needs only new,
- * and DELETE needs only old.
- */
-
- /* "old" transition table for UPDATE, if any */
- Tuplestorestate *old_upd_tuplestore;
- /* "new" transition table for UPDATE, if any */
- Tuplestorestate *new_upd_tuplestore;
- /* "old" transition table for DELETE, if any */
- Tuplestorestate *old_del_tuplestore;
- /* "new" transition table for INSERT, if any */
- Tuplestorestate *new_ins_tuplestore;
+ /* "old" transition table for UPDATE/DELETE, if any */
+ Tuplestorestate *old_tuplestore;
+ /* "new" transition table for INSERT/UPDATE, if any */
+ Tuplestorestate *new_tuplestore;
TupleTableSlot *storeslot; /* for converting to tuplestore's format */
};
@@ -3960,6 +3949,7 @@ static Tuplestorestate *GetAfterTriggers
TupleTableSlot *newslot,
TransitionCaptureState *transition_capture);
static void TransitionTableAddTuple(EState *estate,
+ int event,
TransitionCaptureState *transition_capture,
ResultRelInfo *relinfo,
TupleTableSlot *slot,
@@ -4527,19 +4517,13 @@ AfterTriggerExecute(EState *estate,
{
if (LocTriggerData.tg_trigger->tgoldtable)
{
- if (TRIGGER_FIRED_BY_UPDATE(evtshared->ats_event))
- LocTriggerData.tg_oldtable = evtshared->ats_table->old_upd_tuplestore;
- else
- LocTriggerData.tg_oldtable = evtshared->ats_table->old_del_tuplestore;
+ LocTriggerData.tg_oldtable = evtshared->ats_table->old_tuplestore;
evtshared->ats_table->closed = true;
}
if (LocTriggerData.tg_trigger->tgnewtable)
{
- if (TRIGGER_FIRED_BY_INSERT(evtshared->ats_event))
- LocTriggerData.tg_newtable = evtshared->ats_table->new_ins_tuplestore;
- else
- LocTriggerData.tg_newtable = evtshared->ats_table->new_upd_tuplestore;
+ LocTriggerData.tg_newtable = evtshared->ats_table->new_tuplestore;
evtshared->ats_table->closed = true;
}
}
@@ -4972,7 +4956,9 @@ MakeTransitionCaptureState(TriggerDesc *
need_new_upd,
need_old_del,
need_new_ins;
- AfterTriggersTableData *table;
+ AfterTriggersTableData *ins_table;
+ AfterTriggersTableData *upd_table;
+ AfterTriggersTableData *del_table;
MemoryContext oldcxt;
ResourceOwner saveResourceOwner;
@@ -5019,10 +5005,15 @@ MakeTransitionCaptureState(TriggerDesc *
AfterTriggerEnlargeQueryState();
/*
- * Find or create an AfterTriggersTableData struct to hold the
+ * Find or create an AfterTriggersTableData structs to hold the
* tuplestore(s). If there's a matching struct but it's marked closed,
* ignore it; we need a newer one.
*
+ * Note: MERGE must use the same AfterTriggersTableData structs as INSERT,
+ * UPDATE, and DELETE, so that any MERGE'd tuples are added to the same
+ * tuplestores as tuples from any INSERT, UPDATE, or DELETE commands
+ * running in the same top-level command (e.g., in a writable CTE).
+ *
* Note: the AfterTriggersTableData list, as well as the tuplestores, are
* allocated in the current (sub)transaction's CurTransactionContext, and
* the tuplestores are managed by the (sub)transaction's resource owner.
@@ -5030,21 +5021,34 @@ MakeTransitionCaptureState(TriggerDesc *
* transition tables to be deferrable; they will be fired during
* AfterTriggerEndQuery, after which it's okay to delete the data.
*/
- table = GetAfterTriggersTableData(relid, cmdType);
+ if (need_new_ins)
+ ins_table = GetAfterTriggersTableData(relid, CMD_INSERT);
+ else
+ ins_table = NULL;
+
+ if (need_old_upd || need_new_upd)
+ upd_table = GetAfterTriggersTableData(relid, CMD_UPDATE);
+ else
+ upd_table = NULL;
+
+ if (need_old_del)
+ del_table = GetAfterTriggersTableData(relid, CMD_DELETE);
+ else
+ del_table = NULL;
/* Now create required tuplestore(s), if we don't have them already. */
oldcxt = MemoryContextSwitchTo(CurTransactionContext);
saveResourceOwner = CurrentResourceOwner;
CurrentResourceOwner = CurTransactionResourceOwner;
- if (need_old_upd && table->old_upd_tuplestore == NULL)
- table->old_upd_tuplestore = tuplestore_begin_heap(false, false, work_mem);
- if (need_new_upd && table->new_upd_tuplestore == NULL)
- table->new_upd_tuplestore = tuplestore_begin_heap(false, false, work_mem);
- if (need_old_del && table->old_del_tuplestore == NULL)
- table->old_del_tuplestore = tuplestore_begin_heap(false, false, work_mem);
- if (need_new_ins && table->new_ins_tuplestore == NULL)
- table->new_ins_tuplestore = tuplestore_begin_heap(false, false, work_mem);
+ if (need_old_upd && upd_table->old_tuplestore == NULL)
+ upd_table->old_tuplestore = tuplestore_begin_heap(false, false, work_mem);
+ if (need_new_upd && upd_table->new_tuplestore == NULL)
+ upd_table->new_tuplestore = tuplestore_begin_heap(false, false, work_mem);
+ if (need_old_del && del_table->old_tuplestore == NULL)
+ del_table->old_tuplestore = tuplestore_begin_heap(false, false, work_mem);
+ if (need_new_ins && ins_table->new_tuplestore == NULL)
+ ins_table->new_tuplestore = tuplestore_begin_heap(false, false, work_mem);
CurrentResourceOwner = saveResourceOwner;
MemoryContextSwitchTo(oldcxt);
@@ -5055,7 +5059,9 @@ MakeTransitionCaptureState(TriggerDesc *
state->tcs_update_old_table = need_old_upd;
state->tcs_update_new_table = need_new_upd;
state->tcs_insert_new_table = need_new_ins;
- state->tcs_private = table;
+ state->tcs_insert_private = ins_table;
+ state->tcs_update_private = upd_table;
+ state->tcs_delete_private = del_table;
return state;
}
@@ -5233,20 +5239,12 @@ AfterTriggerFreeQuery(AfterTriggersQuery
{
AfterTriggersTableData *table = (AfterTriggersTableData *) lfirst(lc);
- ts = table->old_upd_tuplestore;
- table->old_upd_tuplestore = NULL;
- if (ts)
- tuplestore_end(ts);
- ts = table->new_upd_tuplestore;
- table->new_upd_tuplestore = NULL;
- if (ts)
- tuplestore_end(ts);
- ts = table->old_del_tuplestore;
- table->old_del_tuplestore = NULL;
+ ts = table->old_tuplestore;
+ table->old_tuplestore = NULL;
if (ts)
tuplestore_end(ts);
- ts = table->new_ins_tuplestore;
- table->new_ins_tuplestore = NULL;
+ ts = table->new_tuplestore;
+ table->new_tuplestore = NULL;
if (ts)
tuplestore_end(ts);
if (table->storeslot)
@@ -5557,17 +5555,17 @@ GetAfterTriggersTransitionTable(int even
{
Assert(TupIsNull(newslot));
if (event == TRIGGER_EVENT_DELETE && delete_old_table)
- tuplestore = transition_capture->tcs_private->old_del_tuplestore;
+ tuplestore = transition_capture->tcs_delete_private->old_tuplestore;
else if (event == TRIGGER_EVENT_UPDATE && update_old_table)
- tuplestore = transition_capture->tcs_private->old_upd_tuplestore;
+ tuplestore = transition_capture->tcs_update_private->old_tuplestore;
}
else if (!TupIsNull(newslot))
{
Assert(TupIsNull(oldslot));
if (event == TRIGGER_EVENT_INSERT && insert_new_table)
- tuplestore = transition_capture->tcs_private->new_ins_tuplestore;
+ tuplestore = transition_capture->tcs_insert_private->new_tuplestore;
else if (event == TRIGGER_EVENT_UPDATE && update_new_table)
- tuplestore = transition_capture->tcs_private->new_upd_tuplestore;
+ tuplestore = transition_capture->tcs_update_private->new_tuplestore;
}
return tuplestore;
@@ -5581,6 +5579,7 @@ GetAfterTriggersTransitionTable(int even
*/
static void
TransitionTableAddTuple(EState *estate,
+ int event,
TransitionCaptureState *transition_capture,
ResultRelInfo *relinfo,
TupleTableSlot *slot,
@@ -5599,9 +5598,26 @@ TransitionTableAddTuple(EState *estate,
tuplestore_puttupleslot(tuplestore, original_insert_tuple);
else if ((map = ExecGetChildToRootMap(relinfo)) != NULL)
{
- AfterTriggersTableData *table = transition_capture->tcs_private;
+ AfterTriggersTableData *table;
TupleTableSlot *storeslot;
+ switch (event)
+ {
+ case TRIGGER_EVENT_INSERT:
+ table = transition_capture->tcs_insert_private;
+ break;
+ case TRIGGER_EVENT_UPDATE:
+ table = transition_capture->tcs_update_private;
+ break;
+ case TRIGGER_EVENT_DELETE:
+ table = transition_capture->tcs_delete_private;
+ break;
+ default:
+ elog(ERROR, "invalid after-trigger event code: %d", event);
+ table = NULL; /* keep compiler quiet */
+ break;
+ }
+
storeslot = GetAfterTriggersStoreSlot(table, map->outdesc);
execute_attr_map_slot(map->attrMap, slot, storeslot);
tuplestore_puttupleslot(tuplestore, storeslot);
@@ -6195,7 +6211,7 @@ AfterTriggerSaveEvent(EState *estate, Re
oldslot,
NULL,
transition_capture);
- TransitionTableAddTuple(estate, transition_capture, relinfo,
+ TransitionTableAddTuple(estate, event, transition_capture, relinfo,
oldslot, NULL, old_tuplestore);
}
@@ -6211,7 +6227,7 @@ AfterTriggerSaveEvent(EState *estate, Re
NULL,
newslot,
transition_capture);
- TransitionTableAddTuple(estate, transition_capture, relinfo,
+ TransitionTableAddTuple(estate, event, transition_capture, relinfo,
newslot, original_insert_tuple, new_tuplestore);
}
@@ -6514,7 +6530,23 @@ AfterTriggerSaveEvent(EState *estate, Re
new_shared.ats_firing_id = 0;
if ((trigger->tgoldtable || trigger->tgnewtable) &&
transition_capture != NULL)
- new_shared.ats_table = transition_capture->tcs_private;
+ {
+ switch (event)
+ {
+ case TRIGGER_EVENT_INSERT:
+ new_shared.ats_table = transition_capture->tcs_insert_private;
+ break;
+ case TRIGGER_EVENT_UPDATE:
+ new_shared.ats_table = transition_capture->tcs_update_private;
+ break;
+ case TRIGGER_EVENT_DELETE:
+ new_shared.ats_table = transition_capture->tcs_delete_private;
+ break;
+ default:
+ new_shared.ats_table = NULL;
+ break;
+ }
+ }
else
new_shared.ats_table = NULL;
new_shared.ats_modifiedcols = modifiedCols;
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
new file mode 100644
index b60317c..556c86b
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -78,7 +78,9 @@ typedef struct TransitionCaptureState
/*
* Private data including the tuplestore(s) into which to insert tuples.
*/
- struct AfterTriggersTableData *tcs_private;
+ struct AfterTriggersTableData *tcs_insert_private;
+ struct AfterTriggersTableData *tcs_update_private;
+ struct AfterTriggersTableData *tcs_delete_private;
} TransitionCaptureState;
/*
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
new file mode 100644
index 1eb8fba..1acdd12
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -3033,13 +3033,19 @@ NOTICE: trigger = table1_trig, new tabl
with wcte as (insert into table1 values (43))
insert into table1 values (44);
NOTICE: trigger = table1_trig, new table = (43), (44)
+with wcte as (insert into table1 values (45))
+ merge into table1 using (values (46)) as v(a) on table1.a = v.a
+ when not matched then insert values (v.a);
+NOTICE: trigger = table1_trig, new table = (45), (46)
select * from table1;
a
----
42
44
43
-(3 rows)
+ 46
+ 45
+(5 rows)
select * from table2;
a
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
new file mode 100644
index 5f7f75d..cc87845
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -2228,6 +2228,10 @@ with wcte as (insert into table1 values
with wcte as (insert into table1 values (43))
insert into table1 values (44);
+with wcte as (insert into table1 values (45))
+ merge into table1 using (values (46)) as v(a) on table1.a = v.a
+ when not matched then insert values (v.a);
+
select * from table1;
select * from table2;
* Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE
2026-01-19 19:26 BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE PG Bug reporting form <noreply@postgresql.org>
2026-01-20 15:59 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Dean Rasheed <dean.a.rasheed@gmail.com>
@ 2026-01-20 17:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-01-20 18:00 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
2026-01-21 11:20 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Dean Rasheed <dean.a.rasheed@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Tom Lane @ 2026-01-20 17:21 UTC (permalink / raw)
To: Dean Rasheed <dean.a.rasheed@gmail.com>; +Cc: dwwoelfel@gmail.com; pgsql-bugs@lists.postgresql.org
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> On Tue, 20 Jan 2026 at 08:58, PG Bug reporting form
> <noreply@postgresql.org> wrote:
>> In a CTE that inserts rows with both MERGE and INSERT, the transition table
>> will not contain the rows from the MERGE.
> 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.
Yeah, I had just come to the same conclusion when I saw your email.
Using CMD_MERGE as the AfterTriggersTableData lookup key could be
correct if MERGE were supposed to have separate transition tables,
but AFAICT from the spec it isn't.
> Attached is a rough patch doing that.
I haven't read this in detail, but it seems like one issue to think
about is whether it's okay to add fields to struct
TransitionCaptureState in released branches? Although it's nominally
an ABI break, I can't think of a reason why any extension would be
manufacturing its own TransitionCaptureState structs rather than
calling MakeTransitionCaptureState(), nor should an extension be
touching the stated-to-be-private tcs_private field. So it seems
like we should be able to get away with it.
> 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.
Yeah, that would have to be HEAD-only in any case; we're not going
to change ModifyTableState in released branches.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE
2026-01-19 19:26 BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE PG Bug reporting form <noreply@postgresql.org>
2026-01-20 15:59 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Dean Rasheed <dean.a.rasheed@gmail.com>
2026-01-20 17:21 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-01-20 18:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-01-24 11:48 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Dean Rasheed <dean.a.rasheed@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2026-01-20 18:00 UTC (permalink / raw)
To: Dean Rasheed <dean.a.rasheed@gmail.com>; +Cc: dwwoelfel@gmail.com; pgsql-bugs@lists.postgresql.org
I wrote:
> Dean Rasheed <dean.a.rasheed@gmail.com> writes:
>> Attached is a rough patch doing that.
> I haven't read this in detail,
Okay, I've now been through it more carefully, and it looks good
except for these nitpicks (addressed in v2 patch attached):
* I think GetAfterTriggersTableData() ought to have an Assert that
the cmdType used as lookup key is INSERT/UPDATE/DELETE and nothing
else.
* Corrected grammar in comment in MakeTransitionCaptureState.
* Added comment in new switch in AfterTriggerSaveEvent.
One could argue that that new switch should have an explicit
case for TRIGGER_EVENT_TRUNCATE and then the default: should
be elog(ERROR). That seems excessive to me, given that the
switch earlier in the same function vetted the event value.
But others might see it differently.
regards, tom lane
Attachments:
fix-transition-table-capture-2.patch (text/x-diff, 12.8K) download
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index d30fda660eb..8df915f63fb 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -3919,21 +3919,10 @@ struct AfterTriggersTableData
bool after_trig_done; /* did we already queue AS triggers? */
AfterTriggerEventList after_trig_events; /* if so, saved list pointer */
- /*
- * We maintain separate transition tables for UPDATE/INSERT/DELETE since
- * MERGE can run all three actions in a single statement. Note that UPDATE
- * needs both old and new transition tables whereas INSERT needs only new,
- * and DELETE needs only old.
- */
-
- /* "old" transition table for UPDATE, if any */
- Tuplestorestate *old_upd_tuplestore;
- /* "new" transition table for UPDATE, if any */
- Tuplestorestate *new_upd_tuplestore;
- /* "old" transition table for DELETE, if any */
- Tuplestorestate *old_del_tuplestore;
- /* "new" transition table for INSERT, if any */
- Tuplestorestate *new_ins_tuplestore;
+ /* "old" transition table for UPDATE/DELETE, if any */
+ Tuplestorestate *old_tuplestore;
+ /* "new" transition table for INSERT/UPDATE, if any */
+ Tuplestorestate *new_tuplestore;
TupleTableSlot *storeslot; /* for converting to tuplestore's format */
};
@@ -3960,6 +3949,7 @@ static Tuplestorestate *GetAfterTriggersTransitionTable(int event,
TupleTableSlot *newslot,
TransitionCaptureState *transition_capture);
static void TransitionTableAddTuple(EState *estate,
+ int event,
TransitionCaptureState *transition_capture,
ResultRelInfo *relinfo,
TupleTableSlot *slot,
@@ -4527,19 +4517,13 @@ AfterTriggerExecute(EState *estate,
{
if (LocTriggerData.tg_trigger->tgoldtable)
{
- if (TRIGGER_FIRED_BY_UPDATE(evtshared->ats_event))
- LocTriggerData.tg_oldtable = evtshared->ats_table->old_upd_tuplestore;
- else
- LocTriggerData.tg_oldtable = evtshared->ats_table->old_del_tuplestore;
+ LocTriggerData.tg_oldtable = evtshared->ats_table->old_tuplestore;
evtshared->ats_table->closed = true;
}
if (LocTriggerData.tg_trigger->tgnewtable)
{
- if (TRIGGER_FIRED_BY_INSERT(evtshared->ats_event))
- LocTriggerData.tg_newtable = evtshared->ats_table->new_ins_tuplestore;
- else
- LocTriggerData.tg_newtable = evtshared->ats_table->new_upd_tuplestore;
+ LocTriggerData.tg_newtable = evtshared->ats_table->new_tuplestore;
evtshared->ats_table->closed = true;
}
}
@@ -4886,6 +4870,11 @@ GetAfterTriggersTableData(Oid relid, CmdType cmdType)
MemoryContext oldcxt;
ListCell *lc;
+ /* At this level, cmdType should not be, eg, CMD_MERGE */
+ Assert(cmdType == CMD_INSERT ||
+ cmdType == CMD_UPDATE ||
+ cmdType == CMD_DELETE);
+
/* Caller should have ensured query_depth is OK. */
Assert(afterTriggers.query_depth >= 0 &&
afterTriggers.query_depth < afterTriggers.maxquerydepth);
@@ -4972,7 +4961,9 @@ MakeTransitionCaptureState(TriggerDesc *trigdesc, Oid relid, CmdType cmdType)
need_new_upd,
need_old_del,
need_new_ins;
- AfterTriggersTableData *table;
+ AfterTriggersTableData *ins_table;
+ AfterTriggersTableData *upd_table;
+ AfterTriggersTableData *del_table;
MemoryContext oldcxt;
ResourceOwner saveResourceOwner;
@@ -5019,10 +5010,15 @@ MakeTransitionCaptureState(TriggerDesc *trigdesc, Oid relid, CmdType cmdType)
AfterTriggerEnlargeQueryState();
/*
- * Find or create an AfterTriggersTableData struct to hold the
+ * Find or create AfterTriggersTableData struct(s) to hold the
* tuplestore(s). If there's a matching struct but it's marked closed,
* ignore it; we need a newer one.
*
+ * Note: MERGE must use the same AfterTriggersTableData structs as INSERT,
+ * UPDATE, and DELETE, so that any MERGE'd tuples are added to the same
+ * tuplestores as tuples from any INSERT, UPDATE, or DELETE commands
+ * running in the same top-level command (e.g., in a writable CTE).
+ *
* Note: the AfterTriggersTableData list, as well as the tuplestores, are
* allocated in the current (sub)transaction's CurTransactionContext, and
* the tuplestores are managed by the (sub)transaction's resource owner.
@@ -5030,21 +5026,34 @@ MakeTransitionCaptureState(TriggerDesc *trigdesc, Oid relid, CmdType cmdType)
* transition tables to be deferrable; they will be fired during
* AfterTriggerEndQuery, after which it's okay to delete the data.
*/
- table = GetAfterTriggersTableData(relid, cmdType);
+ if (need_new_ins)
+ ins_table = GetAfterTriggersTableData(relid, CMD_INSERT);
+ else
+ ins_table = NULL;
+
+ if (need_old_upd || need_new_upd)
+ upd_table = GetAfterTriggersTableData(relid, CMD_UPDATE);
+ else
+ upd_table = NULL;
+
+ if (need_old_del)
+ del_table = GetAfterTriggersTableData(relid, CMD_DELETE);
+ else
+ del_table = NULL;
/* Now create required tuplestore(s), if we don't have them already. */
oldcxt = MemoryContextSwitchTo(CurTransactionContext);
saveResourceOwner = CurrentResourceOwner;
CurrentResourceOwner = CurTransactionResourceOwner;
- if (need_old_upd && table->old_upd_tuplestore == NULL)
- table->old_upd_tuplestore = tuplestore_begin_heap(false, false, work_mem);
- if (need_new_upd && table->new_upd_tuplestore == NULL)
- table->new_upd_tuplestore = tuplestore_begin_heap(false, false, work_mem);
- if (need_old_del && table->old_del_tuplestore == NULL)
- table->old_del_tuplestore = tuplestore_begin_heap(false, false, work_mem);
- if (need_new_ins && table->new_ins_tuplestore == NULL)
- table->new_ins_tuplestore = tuplestore_begin_heap(false, false, work_mem);
+ if (need_old_upd && upd_table->old_tuplestore == NULL)
+ upd_table->old_tuplestore = tuplestore_begin_heap(false, false, work_mem);
+ if (need_new_upd && upd_table->new_tuplestore == NULL)
+ upd_table->new_tuplestore = tuplestore_begin_heap(false, false, work_mem);
+ if (need_old_del && del_table->old_tuplestore == NULL)
+ del_table->old_tuplestore = tuplestore_begin_heap(false, false, work_mem);
+ if (need_new_ins && ins_table->new_tuplestore == NULL)
+ ins_table->new_tuplestore = tuplestore_begin_heap(false, false, work_mem);
CurrentResourceOwner = saveResourceOwner;
MemoryContextSwitchTo(oldcxt);
@@ -5055,7 +5064,9 @@ MakeTransitionCaptureState(TriggerDesc *trigdesc, Oid relid, CmdType cmdType)
state->tcs_update_old_table = need_old_upd;
state->tcs_update_new_table = need_new_upd;
state->tcs_insert_new_table = need_new_ins;
- state->tcs_private = table;
+ state->tcs_insert_private = ins_table;
+ state->tcs_update_private = upd_table;
+ state->tcs_delete_private = del_table;
return state;
}
@@ -5233,20 +5244,12 @@ AfterTriggerFreeQuery(AfterTriggersQueryData *qs)
{
AfterTriggersTableData *table = (AfterTriggersTableData *) lfirst(lc);
- ts = table->old_upd_tuplestore;
- table->old_upd_tuplestore = NULL;
- if (ts)
- tuplestore_end(ts);
- ts = table->new_upd_tuplestore;
- table->new_upd_tuplestore = NULL;
- if (ts)
- tuplestore_end(ts);
- ts = table->old_del_tuplestore;
- table->old_del_tuplestore = NULL;
+ ts = table->old_tuplestore;
+ table->old_tuplestore = NULL;
if (ts)
tuplestore_end(ts);
- ts = table->new_ins_tuplestore;
- table->new_ins_tuplestore = NULL;
+ ts = table->new_tuplestore;
+ table->new_tuplestore = NULL;
if (ts)
tuplestore_end(ts);
if (table->storeslot)
@@ -5557,17 +5560,17 @@ GetAfterTriggersTransitionTable(int event,
{
Assert(TupIsNull(newslot));
if (event == TRIGGER_EVENT_DELETE && delete_old_table)
- tuplestore = transition_capture->tcs_private->old_del_tuplestore;
+ tuplestore = transition_capture->tcs_delete_private->old_tuplestore;
else if (event == TRIGGER_EVENT_UPDATE && update_old_table)
- tuplestore = transition_capture->tcs_private->old_upd_tuplestore;
+ tuplestore = transition_capture->tcs_update_private->old_tuplestore;
}
else if (!TupIsNull(newslot))
{
Assert(TupIsNull(oldslot));
if (event == TRIGGER_EVENT_INSERT && insert_new_table)
- tuplestore = transition_capture->tcs_private->new_ins_tuplestore;
+ tuplestore = transition_capture->tcs_insert_private->new_tuplestore;
else if (event == TRIGGER_EVENT_UPDATE && update_new_table)
- tuplestore = transition_capture->tcs_private->new_upd_tuplestore;
+ tuplestore = transition_capture->tcs_update_private->new_tuplestore;
}
return tuplestore;
@@ -5581,6 +5584,7 @@ GetAfterTriggersTransitionTable(int event,
*/
static void
TransitionTableAddTuple(EState *estate,
+ int event,
TransitionCaptureState *transition_capture,
ResultRelInfo *relinfo,
TupleTableSlot *slot,
@@ -5599,9 +5603,26 @@ TransitionTableAddTuple(EState *estate,
tuplestore_puttupleslot(tuplestore, original_insert_tuple);
else if ((map = ExecGetChildToRootMap(relinfo)) != NULL)
{
- AfterTriggersTableData *table = transition_capture->tcs_private;
+ AfterTriggersTableData *table;
TupleTableSlot *storeslot;
+ switch (event)
+ {
+ case TRIGGER_EVENT_INSERT:
+ table = transition_capture->tcs_insert_private;
+ break;
+ case TRIGGER_EVENT_UPDATE:
+ table = transition_capture->tcs_update_private;
+ break;
+ case TRIGGER_EVENT_DELETE:
+ table = transition_capture->tcs_delete_private;
+ break;
+ default:
+ elog(ERROR, "invalid after-trigger event code: %d", event);
+ table = NULL; /* keep compiler quiet */
+ break;
+ }
+
storeslot = GetAfterTriggersStoreSlot(table, map->outdesc);
execute_attr_map_slot(map->attrMap, slot, storeslot);
tuplestore_puttupleslot(tuplestore, storeslot);
@@ -6195,7 +6216,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
oldslot,
NULL,
transition_capture);
- TransitionTableAddTuple(estate, transition_capture, relinfo,
+ TransitionTableAddTuple(estate, event, transition_capture, relinfo,
oldslot, NULL, old_tuplestore);
}
@@ -6211,7 +6232,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
NULL,
newslot,
transition_capture);
- TransitionTableAddTuple(estate, transition_capture, relinfo,
+ TransitionTableAddTuple(estate, event, transition_capture, relinfo,
newslot, original_insert_tuple, new_tuplestore);
}
@@ -6514,7 +6535,24 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
new_shared.ats_firing_id = 0;
if ((trigger->tgoldtable || trigger->tgnewtable) &&
transition_capture != NULL)
- new_shared.ats_table = transition_capture->tcs_private;
+ {
+ switch (event)
+ {
+ case TRIGGER_EVENT_INSERT:
+ new_shared.ats_table = transition_capture->tcs_insert_private;
+ break;
+ case TRIGGER_EVENT_UPDATE:
+ new_shared.ats_table = transition_capture->tcs_update_private;
+ break;
+ case TRIGGER_EVENT_DELETE:
+ new_shared.ats_table = transition_capture->tcs_delete_private;
+ break;
+ default:
+ /* Must be TRUNCATE, see switch above */
+ new_shared.ats_table = NULL;
+ break;
+ }
+ }
else
new_shared.ats_table = NULL;
new_shared.ats_modifiedcols = modifiedCols;
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index b60317c7a75..556c86bf5e1 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -78,7 +78,9 @@ typedef struct TransitionCaptureState
/*
* Private data including the tuplestore(s) into which to insert tuples.
*/
- struct AfterTriggersTableData *tcs_private;
+ struct AfterTriggersTableData *tcs_insert_private;
+ struct AfterTriggersTableData *tcs_update_private;
+ struct AfterTriggersTableData *tcs_delete_private;
} TransitionCaptureState;
/*
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 1eb8fba0953..1acdd12d29e 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -3033,13 +3033,19 @@ NOTICE: trigger = table1_trig, new table = (42)
with wcte as (insert into table1 values (43))
insert into table1 values (44);
NOTICE: trigger = table1_trig, new table = (43), (44)
+with wcte as (insert into table1 values (45))
+ merge into table1 using (values (46)) as v(a) on table1.a = v.a
+ when not matched then insert values (v.a);
+NOTICE: trigger = table1_trig, new table = (45), (46)
select * from table1;
a
----
42
44
43
-(3 rows)
+ 46
+ 45
+(5 rows)
select * from table2;
a
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 5f7f75d7ba5..cc878455ace 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -2228,6 +2228,10 @@ with wcte as (insert into table1 values (42))
with wcte as (insert into table1 values (43))
insert into table1 values (44);
+with wcte as (insert into table1 values (45))
+ merge into table1 using (values (46)) as v(a) on table1.a = v.a
+ when not matched then insert values (v.a);
+
select * from table1;
select * from table2;
=
* Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE
2026-01-19 19:26 BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE PG Bug reporting form <noreply@postgresql.org>
2026-01-20 15:59 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Dean Rasheed <dean.a.rasheed@gmail.com>
2026-01-20 17:21 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
2026-01-20 18:00 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-01-24 11:48 ` Dean Rasheed <dean.a.rasheed@gmail.com>
2026-01-24 15:58 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Dean Rasheed @ 2026-01-24 11:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: dwwoelfel@gmail.com; pgsql-bugs@lists.postgresql.org
On Tue, 20 Jan 2026 at 18:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Okay, I've now been through it more carefully, and it looks good
> except for these nitpicks (addressed in v2 patch attached):
>
OK, those make sense. Thanks for reviewing.
I've pushed this now. Expecting to have to push updates to
.abi-compliance-history soon.
Regards,
Dean
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE
2026-01-19 19:26 BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE PG Bug reporting form <noreply@postgresql.org>
2026-01-20 15:59 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Dean Rasheed <dean.a.rasheed@gmail.com>
2026-01-20 17:21 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
2026-01-20 18:00 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
2026-01-24 11:48 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Dean Rasheed <dean.a.rasheed@gmail.com>
@ 2026-01-24 15:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-01-24 17:03 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2026-01-24 15:58 UTC (permalink / raw)
To: Dean Rasheed <dean.a.rasheed@gmail.com>; +Cc: dwwoelfel@gmail.com; pgsql-bugs@lists.postgresql.org
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> I've pushed this now. Expecting to have to push updates to
> .abi-compliance-history soon.
crake has reported in [1]:
Leaf changes summary: 2 artifacts changed
Changed leaf types summary: 2 leaf types changed
Removed/Changed/Added functions summary: 0 Removed, 0 Changed, 0 Added function (2 filtered out)
Removed/Changed/Added variables summary: 0 Removed, 0 Changed, 0 Added variable
'struct AfterTriggersTableData' changed:
type size changed from 80 to 64 (in bytes)
2 data member deletions:
'Tuplestorestate* old_del_tuplestore', at offset 56 (in bytes)
'Tuplestorestate* new_ins_tuplestore', at offset 64 (in bytes)
there are data member changes:
'TupleTableSlot* storeslot' offset changed from 72 to 56 (in bytes) (by -16 bytes)
'struct TransitionCaptureState' changed:
type size changed from 24 to 40 (in bytes)
2 data member insertions:
'AfterTriggersTableData* tcs_update_private', at offset 24 (in bytes)
'AfterTriggersTableData* tcs_delete_private', at offset 32 (in bytes)
there are data member changes:
name of 'TransitionCaptureState::tcs_private' changed to 'TransitionCaptureState::tcs_insert_private'
I find it unhelpful that it reported the AfterTriggersTableData
change as an ABI change, even though that struct is not globally
accessible. I wonder if there's some libabigail switches we
should be tweaking to refine that.
regards, tom lane
[1] https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=crake&dt=2026-01-24%2013%3A06%3A41...
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE
2026-01-19 19:26 BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE PG Bug reporting form <noreply@postgresql.org>
2026-01-20 15:59 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Dean Rasheed <dean.a.rasheed@gmail.com>
2026-01-20 17:21 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
2026-01-20 18:00 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
2026-01-24 11:48 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Dean Rasheed <dean.a.rasheed@gmail.com>
2026-01-24 15:58 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-01-24 17:03 ` Andres Freund <andres@anarazel.de>
2026-01-24 17:27 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Andres Freund @ 2026-01-24 17:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Dean Rasheed <dean.a.rasheed@gmail.com>; dwwoelfel@gmail.com; pgsql-bugs@lists.postgresql.org
Hi,
On 2026-01-24 10:58:40 -0500, Tom Lane wrote:
> crake has reported in [1]:
>
> Leaf changes summary: 2 artifacts changed
> Changed leaf types summary: 2 leaf types changed
> Removed/Changed/Added functions summary: 0 Removed, 0 Changed, 0 Added function (2 filtered out)
> Removed/Changed/Added variables summary: 0 Removed, 0 Changed, 0 Added variable
>
> 'struct AfterTriggersTableData' changed:
> type size changed from 80 to 64 (in bytes)
> 2 data member deletions:
> 'Tuplestorestate* old_del_tuplestore', at offset 56 (in bytes)
> 'Tuplestorestate* new_ins_tuplestore', at offset 64 (in bytes)
> there are data member changes:
> 'TupleTableSlot* storeslot' offset changed from 72 to 56 (in bytes) (by -16 bytes)
>
> 'struct TransitionCaptureState' changed:
> type size changed from 24 to 40 (in bytes)
> 2 data member insertions:
> 'AfterTriggersTableData* tcs_update_private', at offset 24 (in bytes)
> 'AfterTriggersTableData* tcs_delete_private', at offset 32 (in bytes)
> there are data member changes:
> name of 'TransitionCaptureState::tcs_private' changed to 'TransitionCaptureState::tcs_insert_private'
>
>
> I find it unhelpful that it reported the AfterTriggersTableData
> change as an ABI change, even though that struct is not globally
> accessible. I wonder if there's some libabigail switches we
> should be tweaking to refine that.
Specifying --headers-dir ... during abidw seems to do the trick.
Without --headers-dir:
'struct AfterTriggersTableData at trigger.c:3912:1' changed:
type size changed from 80 to 64 (in bytes)
2 data member deletions:
'Tuplestorestate* old_del_tuplestore', at offset 56 (in bytes) at trigger.c:3934:1
'Tuplestorestate* new_ins_tuplestore', at offset 64 (in bytes) at trigger.c:3936:1
there are data member changes:
'TupleTableSlot* storeslot' offset changed from 72 to 56 (in bytes) (by -16 bytes)
'struct TransitionCaptureState at trigger.h:56:1' changed:
type size changed from 24 to 40 (in bytes)
2 data member insertions:
'AfterTriggersTableData* tcs_update_private', at offset 24 (in bytes) at trigger.h:82:1
'AfterTriggersTableData* tcs_delete_private', at offset 32 (in bytes) at trigger.h:83:1
there are data member changes:
name of 'TransitionCaptureState::tcs_private' changed to 'TransitionCaptureState::tcs_insert_private' at trigger.h:81:1
With --headers-dir:
'struct TransitionCaptureState at trigger.h:56:1' changed:
type size changed from 24 to 40 (in bytes)
2 data member insertions:
'AfterTriggersTableData* tcs_update_private', at offset 24 (in bytes) at trigger.h:82:1
'AfterTriggersTableData* tcs_delete_private', at offset 32 (in bytes) at trigger.h:83:1
(the reason --headers-dir matters is that it implies dropping private types)
So it seems like that'd improve things... I did check the PR adding this to
the buildfarm [1] and it doesn't use --headers-dir.
It also has the nice side-benefit of making the diffing a bit faster (65s to
48s on my older workstation).
Greetings,
Andres Freund
[1] https://github.com/PGBuildFarm/client-code/pull/38/changes
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE
2026-01-19 19:26 BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE PG Bug reporting form <noreply@postgresql.org>
2026-01-20 15:59 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Dean Rasheed <dean.a.rasheed@gmail.com>
2026-01-20 17:21 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
2026-01-20 18:00 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
2026-01-24 11:48 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Dean Rasheed <dean.a.rasheed@gmail.com>
2026-01-24 15:58 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
2026-01-24 17:03 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Andres Freund <andres@anarazel.de>
@ 2026-01-24 17:27 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2026-01-24 17:27 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: Dean Rasheed <dean.a.rasheed@gmail.com>; dwwoelfel@gmail.com; pgsql-bugs@lists.postgresql.org
Andres Freund <andres@anarazel.de> writes:
> On 2026-01-24 10:58:40 -0500, Tom Lane wrote:
>> I find it unhelpful that it reported the AfterTriggersTableData
>> change as an ABI change, even though that struct is not globally
>> accessible. I wonder if there's some libabigail switches we
>> should be tweaking to refine that.
> Specifying --headers-dir ... during abidw seems to do the trick.
Yeah, I was just experimenting with that. It's pretty unclear
from the abigail docs whether you have to name all the header
directories explicitly or it's enough to point at the top one ...
but experimentation says the top one is enough.
I'll send Andrew a patch.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE
2026-01-19 19:26 BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE PG Bug reporting form <noreply@postgresql.org>
2026-01-20 15:59 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Dean Rasheed <dean.a.rasheed@gmail.com>
2026-01-20 17:21 ` Re: BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-01-21 11:20 ` Dean Rasheed <dean.a.rasheed@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Dean Rasheed @ 2026-01-21 11:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: dwwoelfel@gmail.com; pgsql-bugs@lists.postgresql.org
On Tue, 20 Jan 2026 at 17:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Dean Rasheed <dean.a.rasheed@gmail.com> writes:
>
> > 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.
>
> Yeah, I had just come to the same conclusion when I saw your email.
> Using CMD_MERGE as the AfterTriggersTableData lookup key could be
> correct if MERGE were supposed to have separate transition tables,
> but AFAICT from the spec it isn't.
Yeah, I think that's right. The spec is clear that the effects of a
MERGE action should be the same as the effects of an
INSERT/UPDATE/DELETE command, and that includes triggers. However, I
don't think the spec says anything about what should happen if there
are multiple INSERT/UPDATE/DELETE/MERGE sub-commands within a single
top-level command, because including INSERT/UPDATE/DELETE/MERGE
statements inside a WITH statement is our own extension of the
standard.
So I think that we were free to choose whether to execute
statement-level triggers once per sub-command, or once for the entire
WITH query. Since we have chosen the latter, it follows that each
MERGE sub-command must add cumulatively to the set of rows in the
INSERT/UPDATE/DELETE transition tables, just as each
INSERT/UPDATE/DELETE sub-command does.
> > Attached is a rough patch doing that.
>
> I haven't read this in detail, but it seems like one issue to think
> about is whether it's okay to add fields to struct
> TransitionCaptureState in released branches? Although it's nominally
> an ABI break, I can't think of a reason why any extension would be
> manufacturing its own TransitionCaptureState structs rather than
> calling MakeTransitionCaptureState(), nor should an extension be
> touching the stated-to-be-private tcs_private field. So it seems
> like we should be able to get away with it.
Yeah, I think that's probably right. FWIW, I grepped for
TransitionCaptureState in a clone of PGXN, and didn't find any uses.
Regards,
Dean
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-01-24 17:27 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-19 19:26 BUG #19380: Transition table in AFTER INSERT trigger misses rows from MERGE when used with INSERT in a CTE PG Bug reporting form <noreply@postgresql.org>
2026-01-20 15:59 ` Dean Rasheed <dean.a.rasheed@gmail.com>
2026-01-20 17:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-01-20 18:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-01-24 11:48 ` Dean Rasheed <dean.a.rasheed@gmail.com>
2026-01-24 15:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-01-24 17:03 ` Andres Freund <andres@anarazel.de>
2026-01-24 17:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-01-21 11:20 ` 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