Skip site navigation (1) Skip section navigation (2)

Re: Writeable CTEs and side effects

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Writeable CTEs and side effects
Date: 2009-10-08 16:57:34
Message-ID: 26185.1255021054@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Greg SmithDate: 2009-10-08 17:00:33
Subject: Re: Concurrency testing
Previous:From: David E. WheelerDate: 2009-10-08 16:53:24
Subject: Re: Issues for named/mixed function notation patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group