Обсуждение: 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