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

>>>>> "Thomas" == Thomas Munro <thomas.munro@enterprisedb.com> writes:
Thomas> So, afterTriggers.query_stack is used to handle the reentrancyThomas> that results from triggers running
furtherstatements thatThomas> might fire triggers.  It isn't used for dealing with extraThomas> ModifyTable nodes that
canappear in a plan because of wCTEs.Thomas> Could it also be used for that purpose?  I think that wouldThomas> only
workif it is the case that each ModifyTable node beginThomas> and then runs to completion (ie no interleaving of
wCTEThomas>execution) and then its AS trigger fires, which I'm not sureThomas> 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)