Re: BUG #5754: CTE optimization fails to account for side effects

On Mon, Nov 15, 2010 at 06:55:47PM -0500, Tom Lane wrote:
> "David Fetter" <david@fetter.org> writes:
> > CREATE SEQUENCE my_seq;
> > WITH t AS (SELECT nextval('my_seq')) VALUES(1);
> > SELECT currval('my_seq');
>
> > ERROR:  currval of sequence "my_seq" is not yet defined in this
> > session
>
> > What's happened is that the optimization didn't account for the
> > idea that a SELECT might have a side effect, and if we're going
> > with the "CTEs execute exactly once and (equivalent to) fully,"
> > this is a bug.
>
> The reason it's not a bug is that we have not adopted that position.
> There is a proposal to make it so for wCTEs, but that doesn't mean
> we should change the existing, documented and useful behavior of
> regular CTEs.

The documented and useful behavior is of read-only CTEs, and since
we've decided that CTEs that cause writes are to behave this way,
simply rewording them as function calls from SELECT shouldn't change
this.

> (If you're wondering where it's documented, I cite section 7.8's
> statement that only as much of a CTE query is evaluated as is read
> by the parent query.  The limiting case of that is no reference ->
> no rows read.)

We can fix this inconsistency in the case of data-changing SELECTs and
not damage any code.  I seriously doubt that people are using the
current behavior as a write fence.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate