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

"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.

(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.)

regards, tom lane

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