On 11 Nov 2010, at 19:13, Tom Lane <t...@sss.pgh.pa.us> wrote:
Marko Tiikkaja <marko.tiikk...@cs.helsinki.fi> writes:
On 2010-11-11 6:41 PM +0200, David Fetter wrote:
On Thu, Nov 11, 2010 at 04:15:34AM +0200, Marko Tiikkaja wrote:
The discussion around wCTE during the last week or so has brought
to
my attention that we don't actually have a consensus on how exactly
wCTEs should behave. The question seems to be whether or not a
statement should see the modifications of statements ran before it.
+1 for letting writeable CTEs see the results of previous CTEs, just
as current non-writeable ones do. A lot of the useful cases for
this
feature depend on this visibility.
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?
You've already predetermined the outcome of the argument by phrasing
it
that way: if you assume that the CTE runs "before" the main statement
then the conclusion is foregone. To my mind, they should be thought
of
as running in parallel, or at least in an indeterminate order, just
exactly the same way that different data modifications made in a
single
INSERT/UPDATE/DELETE command are considered to be made simultaneously.
..
If we establish a precedent that WITHs can be thought of as executing
before the main command, we will eventually have to de-optimize
existing
WITH behavior. Or else make up reasons why the inconsistency is
okay in
some cases and not others, but that will definitely be a case of
rationalizing after the fact.
I apologize, I had misunderstood what you are suggesting. But now
that I do, it seems to be an even worse idea to go your way. Based on
my research, I'm almost certain that the SQL standard says that the
execution order is deterministic if there is at least one DML
statement in the WITH list.
Can anyone confirm this?
Regards,
Marko Tiikkaja
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
