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

"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