Re: PG10 transition tables, wCTEs and multiple operations on the same table

>>>>> "Thomas" == Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com> writes:

Thomas> So, afterTriggers.query_stack is used to handle the reentrancy
Thomas> that results from triggers running further statements that
Thomas> might fire triggers. It isn't used for dealing with extra
Thomas> ModifyTable nodes that can appear in a plan because of wCTEs.
Thomas> Could it also be used for that purpose? I think that would
Thomas> only work if it is the case that each ModifyTable node begin
Thomas> and then runs to completion (ie no interleaving of wCTE
Thomas> execution) and then its AS trigger fires, which I'm not sure
Thomas> about.

There's a problem with this which I didn't see anyone mention (though
I've not read the whole history); existing users of wCTEs rely on the
fact that no AFTER triggers are run until _all_ modifications are
completed. If you change that, then you can't use wCTEs to insert into
tables with FK checks without knowing the order in which the individual
wCTEs are executed, which is currently a bit vague and non-obvious
(which doesn't cause issues at the moment only because nothing actually
depends on the order).

for example:
create table foo (id integer primary key);
create table foo_bar (foo_id integer references foo, bar_id integer);
with i1 as (insert into foo values (1))
insert into foo_bar values (1,2),(1,3);

This would fail the FK check if each insert did its own trigger firing,
since the foo_bar insert is actually run _first_.

Firing triggers earlier than they currently are would thus break
existing working queries.

--
Andrew (irc:RhodiumToad)