On Fri, Mar 17, 2023 at 8:22 AM 德哥 <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.
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.
quote from manual:
INSERT with an ON CONFLICT DO UPDATE clause is a “deterministic” statement. This means that the command will not be allowed to affect any single existing row more than once; a cardinality violation error will be raised when this situation arises. Rows proposed for insertion should not duplicate each other in terms of attributes constrained by an arbiter index or constraint.
I think your insertion affects the existing row more than once.