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

On Friday, August 23, 2019, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Merlin Moncure <mmoncure@gmail.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