On Sat, Nov 13, 2010 at 01:50:46AM +0900, Hitoshi Harada wrote: > 2010/11/13 Robert Haas <robertmh...@gmail.com>: > > On Fri, Nov 12, 2010 at 10:25 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> Yeb Havinga <yebhavi...@gmail.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? 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. 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. > > > > Yeah, I think we have to force any DML statements in CTEs to run to > > completion, whether we need the results or not, and even if they are > > unreferenced. Otherwise it's going to be really confusing, I fear. > > One thing that has annoyed me while designing this feature is if as > Tom suggests the all queries are executed in the same snapshot and > optimized as the current read-only CTE does we are tempted to > support recursive and forward-reference in even DML CTE. It > explodes out my head and I'd like not to think about it if we can.
Does this have about the same head-explodiness as the mutually recursive CTEs described in the SQL standard? More? Less? > On the other hand, different-snapshot, serialized execution model > occurs the problem I originally rose in the previous thread, in which > the space to store the data shared among different plans is missing. > It's of course doable, but the easier implementation the better. > > I'm inclined to agree with the same snapshot model, that is not only > easier to implement but also fits the current SQL processing design > and the existing CTE specification. Not only from the developer's view > but consistency from user's view. Whatever the standard says on the > DML *subquery*, we're going to create our new *CTE* feature. Yes, this > is CTE. For recursive and forward-reference issue, we can just forbid > them in DML CTE at first. Sounds good :) Cheers, David. -- David Fetter <da...@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
