Repository navigation
Concurrent UPDATE of the same row fails with 23505, not 40001, when the table has a unique index #1421
Description
What happens
Two sessions update the same row of a columnar table that has a PRIMARY KEY or UNIQUE index. The second waits for the first. When the first commits, the second fails with 23505 (duplicate key value violates unique constraint), not 40001. This happens even when neither UPDATE changes the key.
docs/limitations.md:597-600 says the opposite:
A concurrent
UPDATEorDELETEof the same row serializes on the row identity. The second writer waits for the first to commit. It then gets a retryableserialization_failure… This holds at every isolation level and needs no unique index.
An application that retries on 40001, as the docs tell it to, does not retry on 23505. It also gets an error naming a duplicate it never inserted.
Measured (PG17, main 217f23b, READ COMMITTED, the second session seen waiting on a Lock before the first commits)
| table | first session | second session | columnar | heap |
|---|---|---|---|---|
pk (id PRIMARY KEY, v) |
SET v = 'a' WHERE id = 61 |
SET v = 'b' WHERE id = 61 |
23505 | ok |
| same | SET id = id + 1000 WHERE id = 60 |
the same | 23505 | ok |
uq (id, u UNIQUE, v) |
SET u = 5000 WHERE id = 63 |
the same | 23505 | ok |
| partitioned, PK on parent, columnar partitions | row-moving SET id = id + 1000 WHERE id = 60 |
the same | 23505 | 40001 |
| the same, no key | the same | the same | 40001 | 40001 |
In every case the data is right: the row exists once. Only the error class is wrong. Without a unique index, columnar gives 40001 as documented.
Why, as far as I can tell (unverified)
An UPDATE is delete-plus-insert. The loser's new version reaches the unique-insert lock (class 103) and the uniqueness check before its row-identity conflict is reported. So it waits on the key, sees the winner's committed key, and reports 23505.
Already known, but not as a contradiction
update_conc.sh:469-488 characterizes this ("a covering UNIQUE(id) index … the loser gets a unique_violation (23505) instead of duplicating"). Its arm asserts only that the loser errored, so it holds whichever SQLSTATE arrives, and nothing compares it with the documented 40001.
Plan
I'll take it (core-am): a failing test first, then report the row-identity conflict (40001) before the new version reaches the unique check. If that can't be done cleanly, the alternative is to change the documentation instead, and I'll say so here before choosing.
Found while writing the partition-row tests for #1341.
Activity
jdatcmd commentedon Oct 15, 2026
Plan (proposed, before any code lands)
Cause, now read in the code.
pgcolumnar_tuple_update(src/columnar_tableam.c:2250) serializes the old row only at flush.PgColumnarSerializeFlushRows(src/columnar_row_lock.c:231) takes the class-104 locks in sorted order and raises 40001 for a row another transaction committed as deleted. Before that, during the UPDATE itself,PgColumnarLockUniqueKeystakes the class-103 lock for the new version's key. The loser waits there on the winner's identical key. When it gets the lock, the executor's unique check sees the winner's committed key and raises 23505, and the flush that would have raised 40001 is never reached. With no unique index, nothing stops the loser before the flush, so it gets 40001 as documented.Proposed fix. In
tuple_update, only when the relation has a unique index, serialize the old row beforePgColumnarLockUniqueKeys:The flush-time batch stays as it is, and re-taking a lock already held is a no-op.
What it costs, and the behaviour change.
Test first.
partition_rows(#1341) and a plain-table arm assert 40001 against heap's outcome; red today with 23505. update_conc.sh:476's characterization becomes an exact SQLSTATE.Alternative. Leave the code and correct limitations.md: with a unique index the loser gets 23505 and should retry on it. I'd rather fix the code, because 23505 names a duplicate the user never wrote. @OffgridwithJD, if you see a reason to prefer the docs route, say so and I'll switch.
jdatcmd commentedon Oct 15, 2026
Fix in #1422, as planned above: tests first (red on main:
23505|1in update_conc, and23505in the new partition_rows move race), then the early serialization. Measured cost on PG17 for a 200k-row UPDATE: a keyed table 1218/2384/3659 ms on main against 1521/2891/4187 ms; a table without a key unchanged (450/518/558 against 442/503/562 ms). The deadlock note is in limitations.md.test: a concurrent same-row UPDATE must get 40001 with a unique index…
fix: a concurrent same-row UPDATE gets 40001, not 23505, on a table w…
test: a keyed UPDATE over groups with deletes keeps its memory bounde…
fix: the per-row serialization check frees its catalog reads (#1421)
test: the memory arms enter the ledger, two dated, and the counts fol…
docs: the CHANGELOG states the keyed UPDATE's cost as both of us meas…
fix: a concurrent same-row UPDATE gets 40001, not 23505, on a table w…
fix: a concurrent same-row UPDATE gets 40001, not 23505, on a table w…
Merge pull request #1422 from commandprompt/fix/1421-unique-update-40001