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.
