Re: WIP: push AFTER-trigger execution into ModifyTable node

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> To be honest, I'm not entirely comfortable with either behavior.
> Pipelining a delete out of one table into an insert into another table
> seems VERY useful to me, and I'd like us to have a way to do that. On
> the other hand, in more complex cases, the fact that the effects of a
> statement are normally not visible to that statement could lead to
> some fairly confusing behavior, especially when triggers are involved.

Yup, that's right. The trigger timing is really the least of the issues.
If you consider that the various WITH clauses run concurrently with each
other and the main statement, rather than sequentially, then

(1) you have implementation-dependent, and not very desirable, behaviors
if any of the statements modify the same table;

(2) none of the statements will see each others' effects, which is
certainly going to confuse some people;

(3) I'm afraid that there's no good way to ensure that the modifying
statements run to completion if the top-level SELECT stops short of
reading that CTE to its end.

Pipelined execution would be nice but I really doubt that it's worth
what we'd have to give up to have it. The one-at-a-time behavior will
be simple to understand and reliable to use. Concurrent execution won't
be either.

regards, tom lane