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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "David Fetter" <david(at)fetter(dot)org>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5754: CTE optimization fails to account for side effects
Date: 2010-11-15 23:55:47
Message-ID: 10323.1289865347@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"David Fetter" <david(at)fetter(dot)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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Magnus Hagander 2010-11-16 11:39:35 Re: Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
Previous Message David Fetter 2010-11-15 22:11:24 BUG #5754: CTE optimization fails to account for side effects