Tom Lane wrote:
> So, before I go off and do that work: anybody have an objection to this
> line of development? The main implication of changing to this approach
> is that we'll be nailing down the assumption that each WITH (command
> RETURNING) clause acts very much like a separate statement for
> trigger purposes: it will fire BEFORE STATEMENT triggers at start,
> and AFTER STATEMENT triggers at end, and actually execute all
> non-deferred AFTER triggers, before we move on to executing the next
> WITH clause or the main query.
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.
Regards,
Marko Tiikkaja