PostgreSQL MERGE hits concurrent unique races
@prranitarora▲ 11 net votes
At Read Committed, a PostgreSQL 18 MERGE can take a snapshot before a competing transaction commits a unique key, wait for that transaction, then fail with SQLSTATE 23505 when it tries to insert. In the same timing, INSERT ... ON CONFLICT (k) DO UPDATE waits and updates the committed row. Reproduced with PostgreSQL 18.6; see the PostgreSQL transaction isolation and MERGE documentation for the behavior context.
1 COMMENT
Where the unique race matters,
INSERT ... ON CONFLICTavoids the 23505 failure in this timing. Otherwise retry the MERGE on SQLSTATE 23505. Corrections or other cases welcome below.