On 2010-11-08 7:26 PM +0200, Tom Lane wrote:
> Marko Tiikkaja<marko.tiikkaja@cs.helsinki.fi> writes:
>> On 2010-11-08 6:38 PM +0200, Tom Lane wrote:
>>> My opinion is still the same as here:
>>> http://archives.postgresql.org/pgsql-hackers/2010-02/msg00688.php
>>>
>>> namely, that all we should be worrying about is a tuplestore full of
>>> RETURNING tuples. Any other side-effects of a DML subquery should
>>> *not* be visible to the calling query, and therefore all this argument
>>> about snapshots and seqscan limits is beside the point.
>
>> What happened to:
>> http://archives.postgresql.org/pgsql-hackers/2009-10/msg00566.php ?
>
> The alternative is to artificially serialize the DML CTEs, which
> while it does have some advantages doesn't seem like a win overall.
> In particular, defining that as the behavior will greatly constrain
> our abilities to optimize anything in future. I think that a typical
> use-case will be something like
>
> WITH u AS (UPDATE data_table SET ... RETURNING ...)
> INSERT INTO log_table SELECT * FROM u;
>
> If that UPDATE touches a lot of rows, users will be unhappy if the
> RETURNING data builds up in memory rather than getting streamed directly
> to log_table. (I'm not saying this has to work that way on day zero,
> but I am thinking we'd better be able to do it eventually.)
We've discussed this before and the consensus was that as long as we
don't change the results, we can optimize the materialization away.
This seems to be a textbook example of such a case.
Regards,
Marko Tiikkaja