Обсуждение: BUG #5754: CTE optimization fails to account for side effects

The following bug has been logged online:

Bug reference:      5754
Logged by:          David Fetter
Email address:      david@fetter.org
PostgreSQL version: 8.4+
Operating system:   All
Description:        CTE optimization fails to account for side effects
Details:

Here's how to reproduce:

BEGIN;
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.

CTEs should not optimize away (parts of) SELECTs that have 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