From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: data modifying WITH seems to drop rows in cascading updates -- bug? |
Date: | 2019-08-24 03:02:03 |
Message-ID: | CAHyXU0yN53qC2PsmKjOoxGNn=JzVCoQSrSJTwphRziDwcuhjPg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | constzl | 2019-08-24 03:48:09 | ran out of space in relation map |
Previous Message | Michael Paquier | 2019-08-24 02:23:19 | Re: BUG #15964: vacuumdb.c:187:10: error: use of undeclared identifier 'FD_SETSIZE' |