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.