Thread: BUG #17845: insert into on conflict bug .

The following bug has been logged on the website:

Bug reference:      17845
Logged by:          Zhou Digoal
Email address:      digoal@126.com
PostgreSQL version: 15.2
Operating system:   macos
Description:

insert into on conflict bug .
In the following insert statement, the row has not been updated multiple
times, why is it still showing an error?

```
create table a (id int primary key, info text, ts date);


insert into a 
select * from (values (1,'a',date '2022-01-01'),(1,'b',date
'2022-01-02'),(1,'c',date '2022-01-03')) as t (id,info,ts) order by ts
desc
on conflict (id) 
do update set info=excluded.info, ts=excluded.ts where a.ts < excluded.ts ;



                                QUERY PLAN                                
--------------------------------------------------------------------------
 Insert on a  (cost=0.06..0.10 rows=0 width=0)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: a_pkey
   Conflict Filter: (a.ts < excluded.ts)
   ->  Sort  (cost=0.06..0.07 rows=3 width=40)
         Sort Key: "*VALUES*".column3 DESC
         ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=40)
(7 rows)


ERROR:  21000: ON CONFLICT DO UPDATE command cannot affect row a second
time
HINT:  Ensure that no rows proposed for insertion within the same command
have duplicate constrained values.
LOCATION:  ExecOnConflictUpdate, nodeModifyTable.c:2054
```

On Thursday, March 16, 2023, PG Bug reporting form <noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference:      17845
Logged by:          Zhou Digoal
Email address:      digoal@126.com
PostgreSQL version: 15.2
Operating system:   macos
Description:       

insert into on conflict bug .
In the following insert statement, the row has not been updated multiple
times, why is it still showing an error?

```
create table a (id int primary key, info text, ts date);


insert into a
select * from (values (1,'a',date '2022-01-01'),(1,'b',date
'2022-01-02'),(1,'c',date '2022-01-03')) as t (id,info,ts) order by ts
desc
on conflict (id)
do update set info=excluded.info, ts=excluded.ts where a.ts < excluded.ts ;

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, March 16, 2023, PG Bug reporting form <noreply@postgresql.org>
> wrote:
>> In the following insert statement, the row has not been updated multiple
>> times, why is it still showing an error?

>> insert into a
>> select * from (values (1,'a',date '2022-01-01'),(1,'b',date
>> '2022-01-02'),(1,'c',date '2022-01-03')) as t (id,info,ts) order by ts
>> desc
>> on conflict (id)
>> do update set info=excluded.info, ts=excluded.ts where a.ts < excluded.ts
>> ;

> You have id=1 in there three times which is precisely the definition of
> “multiple times” (I.e., more than 1)

Yeah.  I believe the reason for the restriction is that it would
otherwise be very uncertain what order the input rows get processed in.
(No, the "order by" won't save you, because there's still a join
to be done after that.)

            regards, tom lane

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.

在 2023-03-16 22:28:27，"jian he" <jian.universality@gmail.com> 写道：

On Thu, Mar 16, 2023 at 5:42 PM PG Bug reporting form <noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference:      17845
Logged by:          Zhou Digoal
Email address:      digoal@126.com
PostgreSQL version: 15.2
Operating system:   macos
Description:       

insert into on conflict bug .
In the following insert statement, the row has not been updated multiple
times, why is it still showing an error?

```
create table a (id int primary key, info text, ts date);


insert into a
select * from (values (1,'a',date '2022-01-01'),(1,'b',date
'2022-01-02'),(1,'c',date '2022-01-03')) as t (id,info,ts) order by ts
desc
on conflict (id)
do update set info=excluded.info, ts=excluded.ts where a.ts < excluded.ts ;



                                QUERY PLAN                               
--------------------------------------------------------------------------
 Insert on a  (cost=0.06..0.10 rows=0 width=0)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: a_pkey
   Conflict Filter: (a.ts < excluded.ts)
   ->  Sort  (cost=0.06..0.07 rows=3 width=40)
         Sort Key: "*VALUES*".column3 DESC
         ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=40)
(7 rows)


ERROR:  21000: ON CONFLICT DO UPDATE command cannot affect row a second
time
HINT:  Ensure that no rows proposed for insertion within the same command
have duplicate constrained values.
LOCATION:  ExecOnConflictUpdate, nodeModifyTable.c:2054
```

it's expected behavior.

See the test expected result in src/test/regress/expected/insert_conflict.out, begin with line 694.

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.

"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

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