Re: data modifying WITH seems to drop rows in cascading updates -- bug?

On Friday, August 23, 2019, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> > Trying to figure out if this is undefined behavior of a bug. It's
> > confusing, and I'm aware of certain oddities in the fringes of the
> > data modifying with queries where the query dependencies are not
> > really clear. Why does the query only return one row?
>
> > postgres=# create table foo(id int);
> > CREATE TABLE
> > postgres=# insert into foo values(1);
> > INSERT 0 1
> > postgres=# with a as (update foo set id = id + 1 returning *), b
> > as(update foo set id = id + 1 returning * ) select * from a union all
> > select id from b;
> > id
> > ────
> > 2
> > (1 row)
>
> FWIW, I think it's intentional. The two UPDATEs execute against the
> same snapshot, so only one of them can update the row --- the other
> one is going to see it as already-updated-by-self. It's undefined
> only to the extent that it's not completely clear which one gets
> there first. In this formulation of the outer query, I think it's
> pretty safe to assume that "a" will get there first, but if you'd
> joined "a" and "b" in some other fashion, conceivably "b" would.
>
> Note that the fine manual (sec. 7.8.2) says
>
> Trying to update the same row twice in a single statement is not
> supported. Only one of the modifications takes place, but it is not
> easy (and sometimes not possible) to reliably predict which one. This
> also applies to deleting a row that was already updated in the same
> statement: only the update is performed. Therefore you should
> generally avoid trying to modify a single row twice in a single
> statement. In particular avoid writing WITH sub-statements that could
> affect the same rows changed by the main statement or a sibling
> sub-statement. The effects of such a statement will not be
> predictable.
>

Right. Shame on me for not checking the docs before posting. Simply
stated, this is undefined behavior.

merlin