wCTE behaviour

On Thu, Nov 11, 2010 at 04:15:34AM +0200, Marko Tiikkaja wrote:

Hi all,

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.
While I think making the modifications visible would be a lot more
intuitive, it's not clear how we'd optimize the execution in the
future without changing the behaviour (triggers are a big concern).

+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.

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

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.
While I think making the modifications visible would be a lot more
intuitive, it's not clear how we'd optimize the execution in the
future without changing the behaviour (triggers are a big concern).

+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?

Regards,
Marko Tiikkaja

On 11 November 2010 16:50, Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi>wrote:

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.
While I think making the modifications visible would be a lot more
intuitive, it's not clear how we'd optimize the execution in the
future without changing the behaviour (triggers are a big concern).

+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?

I would expect that select to return nothing. And if the user wished to
reference what was deleted, they could use RETURNING anyway. </probable
ignorance>

WITH t AS (UPDATE foo SET col = true)
SELECT * FROM foo WHERE col = false;

... Wouldn't this be more practical to have foo's UPDATEs applied prior to
SELECT? Otherwise what would the usecase be?

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Marko Tiikkaja <marko.tiikkaja@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 someone came to us and complained because his ON UPDATE trigger
couldn't reliably see changes made to other rows by the same UPDATE
command, and could we please make UPDATE more deterministic, we'd
tell him to rethink what he was doing. This is the same thing.

It is already the case that a user who pushes on things hard enough can
see that a WITH isn't really run "before" the main command. For
example,

regression=# create sequence s1;
CREATE SEQUENCE
regression=# with tt(x,y) as (select x, nextval('s1') from generate_series(1,10) x)
regression-# select x,y, nextval('s1') as z from tt;
x | y | z
----+----+----
1 | 1 | 2
2 | 3 | 4
3 | 5 | 6
4 | 7 | 8
5 | 9 | 10
6 | 11 | 12
7 | 13 | 14
8 | 15 | 16
9 | 17 | 18
10 | 19 | 20
(10 rows)

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.

regards, tom lane

On Nov 11, 2010, at 9:13 AM, Tom Lane wrote:

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 can see that, but if one can't see the result of the write, or can't determine whether or not it will be visible in advance, what's the point of writeable CTEs?

Best,

David

"David E. Wheeler" <david@kineticode.com> writes:

I can see that, but if one can't see the result of the write, or can't determine whether or not it will be visible in advance, what's the point of writeable CTEs?

The writeable CTE returns a RETURNING set, which you can and should use
in the outer query. The thing that is being argued about here is what
you see if you look "directly" at the target table rather than making
use of RETURNING. Essentially, I'm arguing that we shouldn't promise
any particular behavior at that level, just as we don't promise that
UPDATE updates different rows in any determinate order.

regards, tom lane

Thom Brown <thom@linux.com> writes:

WITH t AS (UPDATE foo SET col = true)
SELECT * FROM foo WHERE col = false;

... Wouldn't this be more practical to have foo's UPDATEs applied prior to
SELECT? Otherwise what would the usecase be?

If that's what you want, you might as well just issue two separate
statements. There is no use-case for this at all unless the WITH
produces some RETURNING data that the SELECT makes use of.

regards, tom lane

On 11 Nov 2010, at 19:13, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Marko Tiikkaja <marko.tiikkaja@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

On Thu, Nov 11, 2010 at 12:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

+1

merlin

On Nov 11, 2010, at 9:29 AM, Tom Lane wrote:

I can see that, but if one can't see the result of the write, or can't determine whether or not it will be visible in advance, what's the point of writeable CTEs?

The writeable CTE returns a RETURNING set, which you can and should use
in the outer query. The thing that is being argued about here is what
you see if you look "directly" at the target table rather than making
use of RETURNING. Essentially, I'm arguing that we shouldn't promise
any particular behavior at that level, just as we don't promise that
UPDATE updates different rows in any determinate order.

Yes, if RETURNING guarantees the execution order, then great. That was the first thing I tried to do before I realized that the current CTE implementation doesn't support w.

David

"David E. Wheeler" <david@kineticode.com> writes:

On Nov 11, 2010, at 9:29 AM, Tom Lane wrote:

The writeable CTE returns a RETURNING set, which you can and should use
in the outer query. The thing that is being argued about here is what
you see if you look "directly" at the target table rather than making
use of RETURNING. Essentially, I'm arguing that we shouldn't promise
any particular behavior at that level, just as we don't promise that
UPDATE updates different rows in any determinate order.

Yes, if RETURNING guarantees the execution order, then great. That was the first thing I tried to do before I realized that the current CTE implementation doesn't support w.

Well, it doesn't "guarantee the execution order", it's just that that's
the defined conduit for getting information out of the WITH and into the
parent query. Looking directly at the table is not that conduit.

I misspoke by saying that the behavior would be nondeterministic.
What I think we should do is run all elements of the tree with the
same snapshot, which would provide perfectly deterministic behavior:
if you look at the target table, you see the prior state. You don't
see the updated state, which is what allows us to possibly optimize
things so that the updates aren't completely made before execution
of the main query starts.

regards, tom lane

On Thu, Nov 11, 2010 at 12:36:38PM -0500, Merlin Moncure wrote:

On Thu, Nov 11, 2010 at 12:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

+1

-1.

When people want to see what has gone before, they can use RETURNING
clauses. With the "indeterminate order" proposal, they cannot.

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

On Thu, Nov 11, 2010 at 12:34:55PM -0500, Tom Lane wrote:

Thom Brown <thom@linux.com> writes:

WITH t AS (UPDATE foo SET col = true)
SELECT * FROM foo WHERE col = false;

... Wouldn't this be more practical to have foo's UPDATEs applied
prior to SELECT? Otherwise what would the usecase be?

If that's what you want, you might as well just issue two separate
statements. There is no use-case for this at all unless the WITH
produces some RETURNING data that the SELECT makes use of.

There are lots of use cases where it does exactly this. One simple
example is maintaining a rollup table, so as less-rolled data get
deleted, they get aggregated into an INSERT into that table. Think of
RRDtool, only with a real data store.

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

David Fetter <david@fetter.org> writes:

On Thu, Nov 11, 2010 at 12:36:38PM -0500, Merlin Moncure wrote:

On Thu, Nov 11, 2010 at 12:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

+1

-1.

When people want to see what has gone before, they can use RETURNING
clauses. With the "indeterminate order" proposal, they cannot.

Say what? The RETURNING data is well defined in any case.

regards, tom lane

On Thu, Nov 11, 2010 at 1:53 PM, David Fetter <david@fetter.org> wrote:

On Thu, Nov 11, 2010 at 12:36:38PM -0500, Merlin Moncure wrote:

On Thu, Nov 11, 2010 at 12:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

+1

-1.

When people want to see what has gone before, they can use RETURNING
clauses.  With the "indeterminate order" proposal, they cannot.

If you want to see what happened 'before' you *must* use a returning
clause. It's the link that pipelines data from one query to another.
There is in fact no 'before', just a way to define hook output into
input. ISTM you have a lot more available routes of CTE optimization
if you go this way.

but, can you present an example of a case that depends on execution
order w/o returning? maybe I'm not seeing something...

merlin

David Fetter <david@fetter.org> writes:

On Thu, Nov 11, 2010 at 12:34:55PM -0500, Tom Lane wrote:

If that's what you want, you might as well just issue two separate
statements. There is no use-case for this at all unless the WITH
produces some RETURNING data that the SELECT makes use of.

There are lots of use cases where it does exactly this.

Name *one*. If there is no RETURNING data, there is absolutely no
reason to use WITH instead of issuing the query separately. In fact,
I would assume that a DML query without RETURNING would not even be
syntactically legal in WITH.

One simple
example is maintaining a rollup table, so as less-rolled data get
deleted, they get aggregated into an INSERT into that table.

Yes, exactly. The way you would do that is something like

WITH del AS (DELETE FROM foo WHERE whatever RETURNING *)
INSERT INTO rollup SELECT * FROM del;

I am very interested to see how you will do the same thing without
using RETURNING and with the behavior you claim to want that the
DELETE is visibly complete before the INSERT starts. Where's the
INSERT gonna get the already-deleted data from?

With my proposal (ie, both queries using same snapshot) you could
actually do it without RETURNING, like this:

WITH useless_cte AS (DELETE FROM foo WHERE whatever)
INSERT INTO rollup SELECT * FROM foo WHERE same-whatever;

But I don't see any reason to think that that's a superior way to write
the query, especially since it might be subject to weird race conditions
against other concurrent modifications of the table. RETURNING is just
a lot saner way to be sure that you're looking at exactly what the
DELETE deleted.

regards, tom lane

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, like
WITH t AS (SELECT nextval('seq'))
SELECT * FROM foo
does not update the sequence.

But if t is referenced..
WITH t AS (DELETE FROM foo RETURNING *)
SELECT * FROM foo NATURAL JOIN t;

Since the extension of t can only be known by deleting foo, it makes
sense that this query cannot return rows. "Select the rows from foo that
I just deleted."

regards,
Yeb Havinga

Yeb Havinga <yebhavinga@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.

regards, tom lane

On Fri, Nov 12, 2010 at 10:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Yeb Havinga <yebhavinga@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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company