On Fri, Mar 17, 2023 at 7:17 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > 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.
I agree, of course, but that isn't the thing that jumps out at me
about INSERT ... ON CONFLICT statements that result in cardinality
violation errors.
Even if "the tuple inserted first" was a meaningful concept, we'd
still have to credit the user with understanding that concept for it
to make any sense to soldier on instead of throwing an error. The
implementation would effectively be giving the INSERT statement the
benefit of the doubt by soldiering on, even though it would have
plenty of circumstantial evidence pointing to the statement having
been written carelessly. So it's just as well that it actually will
throw a cardinality violation error.
In short, a user that can understand a concept like "tuple insertion
order" (in a hypothetical world where that concept is actually valid)
can also just not write their insert statement that way in the first
place. Even if it was correct (it isn't), it would still *look* wrong.
--
Peter Geoghegan