Re: wCTE behaviour

"David E. Wheeler" <david(at)kineticode(dot)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