Re: Writeable CTEs and side effects

Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
> Robert Haas wrote:
>> This has one MAJOR disadvantage: all the tuples from the CTE queries
>> have to be buffered someplace, rather than streamed from the subquery
>> up to the main query. For what may turn out to be pretty common uses
>> cases like WITH tuples AS (DELETE FROM big_table_1 RETURNING ...)
>> INSERT INTO big_table_2 ... this is going to suck pretty bad. I
>> wonder if it isn't better to just use the same command-ID throughout
>> and live with the weirdness of #2.

> I haven't looked at the CTE code in much detail but I was under the
> impression that it had to store the results somewhere in any case.
> You're right, though, it sucks for this use case. Weirdness of #2 is
> probably a lot easier to live with.

>> I think you should definitely get some input from Tom Lane on this
>> before you go too much further, but if he doesn't respond to this
>> thread, I suggest trying again after CommitFest.

> Agreed.

Well, I don't know that I have the controlling vote here, but to me
predictable results come first and implementation efficiency is second.
I think the semantics should definitely be that each WITH RETURNING
query is executed, to completion, exactly once, and then you do the
main query. If you end up dumping lots of useless rows into a
tuplestore, so be it. I doubt that this is a major optimization issue
anyway, because I don't believe that it would be common for the main
query to not look at all/most of the result rows.

I also agree with bumping the CID in between. Consider for example that
the main query contains a function call and the function looks at the
table modified by the WITH RETURNING. What state should it see? If you
don't run the WITH query to completion and then bump CID, the answer
will be full of odd implementation details (including whether some
unrelated function caused a CID bump first).

regards, tom lane