From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: 德哥 <digoal@126.com>
Cc: jian he <jian.universality@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17845: insert into on conflict bug .
Date: Fri, 17 Mar 2023 10:17:01 -0400
Message-ID: <4167810.1679062621@sss.pgh.pa.us> (raw)
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>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, digoal@126.com, jian.universality@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17845: insert into on conflict bug .
In-Reply-To: <4167810.1679062621@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox