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

On Wed, Oct 28, 2009 at 8:45 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
>> Like we've discussed before, WITH (.. RETURNING ..) is probably most
>> useful for moving rows from one table to another.  When you're moving a
>> lot of rows around, there's some point where I believe this execution
>> strategy will be a lot slower than the traditional approach due to
>> storing the RETURNING results on disk.  I've been thinking that in some
>> cases we could inline the CTE for this to actually be a quite
>> significant performance benefit, so I'm not too fancy about the approach
>> you're suggesting.
>
> Well, this is what we need to nail down *now*.  Are we going to say that
> use of WITH(RETURNING) means you forfeit all guarantees about order of
> trigger firing?  Short of that, I don't believe that it is sane to think
> about pipelining such things.  And if we do do that, it sounds like a
> security hole to me, because the owner of the trigger isn't the one who
> agreed to forfeit predictability.

I don't see why either behavior would be a security hole; we get to
define how the system behaves, and users have to write their triggers
to cope with that behavior. We don't want to throw random roadbocks
in the way of sanity, but users are not entitled to assume that no
future major release of PG will have semantics that are in any way
different from whichever release they're now running, especially for
features that don't even exist in the current release. If you have a
specific concern here, maybe you could provide an example.

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.
So I don't really know what the right thing is. What I really want
is to provide both behaviors, but I'm not sure there's any sensible
way to do that, and even if there were it's not clear to me that users
will know which one they want.

...Robert