From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: =?UTF-8?B?5b635ZOl?= <digoal@126.com>,
        jian he <jian.universality@gmail.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17845: insert into on conflict bug .
In-reply-to: 
 <CAKFQuwZJSjVQDX5W00PeinCNvtm7Nt32z1Gzyavo+ebbEph1wg@mail.gmail.com>
References: <17845-fee889ae7e226a76@postgresql.org>
 <CACJufxGDczsEW0-3D8ba1NeACOD7rP9eVXBBkjHiupFq4u0StQ@mail.gmail.com>
 <5a1545f3.1e8a.186ed6b8959.Coremail.digoal@126.com>
 <CAKFQuwZJSjVQDX5W00PeinCNvtm7Nt32z1Gzyavo+ebbEph1wg@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Fri, 17 Mar 2023 05:19:40 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <4167809.1679062621.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Fri, 17 Mar 2023 10:17:01 -0400
Message-ID: <4167810.1679062621@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/4167810.1679062621%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, March 16, 2023, 德哥 <digoal@126.com> wrote:
>> In this case, `where a.ts < exclude. ts` and `order by ts desc` are used.
>> The ts of the tuple inserted first is the largest. So why throw an error?
>> Throwing an error here is obviously not logically correct.

> You’d get a duplicate value violation instead.  As it stands, which error
> you get is somewhat non-deterministic, but you will get one.

I'm going to push back against the idea that "the tuple inserted first"
is a well-defined concept.  SQL is a set-oriented language and in
principle all the row changes caused by a single statement happen
concurrently/independently.  Of course we all know that real
implementations typically don't do it like that, but they do take
advantage of the freedom to do the updates in any order.  So the
reason we throw an error here is to prevent cases where the happenstance
of the individual actions' order would affect the end result.

The bottom line is that it *is* happenstance, ORDER BY or no: the
implementation is not bound to preserve the order of rows coming out
of a sub-select.

			regards, tom lane