"command cannot affect row a second time" in INSERT ... ON CONFLICT
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]MERGE command example CREATE TABLE source (sid INT); CREATE TABLE target (tid INT, UNIQUE (tid)); INSERT INTO target VALUES (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);
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.
Hi Karthik,
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.
Not sure if it will answer your question *entirely* but you will find
a bit more detail about "cannot affect row a second time" in the
discussion [1]/messages/by-id/CAJ7c6TPQJNFETz9H_qPpA3x7ybz2D1QMDtBku_iK33gT3UR34Q@mail.gmail.com. This error has nothing to do with unique constraints,
so I think you trigger one of two errors depending on the order of
inserted rows and the content of your table. This being said, I didn't
investigate your scenario in much detail.
[1]: /messages/by-id/CAJ7c6TPQJNFETz9H_qPpA3x7ybz2D1QMDtBku_iK33gT3UR34Q@mail.gmail.com
--
Best regards,
Aleksander Alekseev
On Thu, Oct 31, 2024 at 9:52 AM Karthik Ramanathan <
karthikram.3006@gmail.com> wrote:
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.
Right, id 1 exists, you insert id 1 again, that row becomes id 20, then you
attempt to insert id 20 again, which conflicts, and the system attempts to
update the 1-become-20 row to 20 but fails to perform the update since that
now-existing row was already modified in this statement (it was inserted).
You don't get a duplicate key error because the second modification
condition is more general and thus triggers first. I.e., that error has to
happen regardless of whether a duplicate key error condition was going to
happen or not (e.g., you could have done something like "set i = i * 20" -
not tested)
*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.
Here the insertion of id 20 happens just fine, then inserting id 1
conflicts, the existing row with id 1 gets updated to id 20 which results
in a duplicate key violation.
1. Is there a different reason the two queries produce a different error?
First error condition wins. Multiple modification gets tested
first, before checking whether the outcome of a modification would result
in a duplicate.
2. Is there a better way to think about the "command cannot affect row a
second time"? Appreciate any guidance. Thanks.
A row inserted or updated in a statement cannot be subsequently modified in
that same statement. I don't actually understand how you are presently
thinking about this...
Apparently the algorithm for merge is able to avoid impacting the same row
twice and thus if the underlying DML is going to produce a duplicate key
violation that is what you will see. I hesitate to claim you'd never see
a multi-update scenario but do find it reasonable that it would be less
prone to it.
David J.