Hello hackers,
I am looking to better understand the applicability of the error message "command cannot affect row a second time".
Consider the following table and data:
CREATE TABLE ioc (i int, UNIQUE(i));
INSERT INTO ioc VALUES (1);
The following two queries produce different errors:
Query 1
postgres=# INSERT INTO ioc VALUES (1), (20) ON CONFLICT (i) DO UPDATE SET i = 20;
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.
Query 2
postgres=# INSERT INTO ioc VALUES (20), (1) ON CONFLICT (i) DO UPDATE SET i = 20;
ERROR: 23505: duplicate key value violates unique constraint "ioc_i_key"
DETAIL: Key (i)=(20) already exists.
INSERT ... ON CONFLICT does not support deferrable unique constraints, and so the two errors appear to be logically equivalent. However, the MERGE command which does support deferring unique constraints, consistently produces the duplicate key violation for similar queries [1] but also raises "command cannot affect row a second time" in other scenarios as demonstrated by regress tests in merge.sql.
Naively, it seems to me that attempting to take a tuple lock on both:
1. The conflicting tuple (i = 1 in the second tuple in Query 2) as well as
2. The tuple it updates into (i = 20 in the second tuple in Query 2) (which may or may not exist)
in ExecOnConflictUpdate could yield a consistent error message in both scenarios but it offers no real functional gains.
1. Is there a different reason the two queries produce a different error?
2. Is there a better way to think about the "command cannot affect row a second time"? Appreciate any guidance. Thanks.
Warm regards,
Karthik Ramanathan
[1] MERGE command example
CREATE TABLE source (sid INT);
CREATE TABLE target (tid INT, UNIQUE (tid));
INSERT INTO target VALUES (1);
Query 1a
postgres=# INSERT INTO source VALUES (20), (1);
postgres=# MERGE INTO target t
USING source AS s
ON t.tid = s.sid
WHEN MATCHED THEN UPDATE SET tid = 20
WHEN NOT MATCHED THEN INSERT VALUES (s.sid);
ERROR: 23505: duplicate key value violates unique constraint "target_tid_key"
DETAIL: Key (tid)=(20) already exists.
Query 1b
postgres=# INSERT INTO source VALUES (1), (20);
postgres=# MERGE INTO target t
USING source AS s
ON t.tid = s.sid
WHEN MATCHED THEN UPDATE SET tid = 20
WHEN NOT MATCHED THEN INSERT VALUES (s.sid);
ERROR: 23505: duplicate key value violates unique constraint "target_tid_key"
DETAIL: Key (tid)=(20) already exists.
