Re: wCTE: why not finish sub-updates at the end, not the beginning?

Greg Stark <gsstark(at)mit(dot)edu> writes:
> On Fri, Feb 25, 2011 at 2:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> However, the real reason for doing it isn't any of those, but rather
>> to establish the principle that the executions of the modifying
>> sub-queries are interleaved not sequential.

> Does the interleaved execution have sane semantics?

Depends on what you call sane. With the decision to not increment
command counter, it's already the case that people shouldn't have
two subqueries try to modify the same row.

> With a query like:

> WITH
> a as update x set x.i=x.i+1 returning x.i,
> b as update x set x.i=x.i+1 returning x.i
> select * from a natural join b;

> Is there any way to tell what it will return or what state it will
> leave the table in?

My reaction to that is "you shouldn't do that, and you definitely
shouldn't complain if it's not predictable whether a or b will
modify a given row". This is exactly the sort of assumption I don't
want people building into their queries, because we will be locked
into purely sequential execution if we promise that the results will
be consistent.

There is already precedent for that position. You can easily construct
queries using UPDATE ... FROM wherein the same target row joins to more
than one row in the FROM table, and then it's unpredictable which
joining row will be used to update that target row. Our position has
always been "don't do that", not that we'd lobotomize the planner and
executor to ensure predictability.

regards, tom lane