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(at)fetter(dot)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.