Re: wCTE behaviour

On Fri, Nov 12, 2010 at 10:25:51AM -0500, Tom Lane wrote:
> Yeb Havinga <yebhavinga(at)gmail(dot)com> writes:
> > On 2010-11-11 17:50, Marko Tiikkaja wrote:
> >> Just to be clear, the main point is whether they see the data
> >> modifications or not. The simplest case to point out this behaviour is:
> >>
> >> WITH t AS (DELETE FROM foo)
> >> SELECT * FROM foo;
> >>
> >> And the big question is: what state of "foo" should the SELECT
> >> statement see?
>
> > Since t is not referenced in the query, foo should not be deleted at
> > all,
>
> Yeah, that's another interesting question: should we somehow force
> unreferenced CTEs to be evaluated anyhow?

Yes.

> Now that I think about it,
> there was also some concern about the possibility of the outer query
> not reading the CTE all the way to the end, ie
>
> WITH t AS (DELETE FROM foo RETURNING *)
> SELECT * FROM t LIMIT 1;
>
> How many rows does this delete? I think we concluded that we should
> force the DELETE to be run to conclusion even if the outer query didn't
> read it all.

Yes.

> From an implementation standpoint that makes it more
> attractive to do the DELETE first and stick its results in a tuplestore
> --- but I still think we should view that as an implementation detail,
> not as part of the specification.

Right :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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