PostgreSQL ON CONFLICT DO NOTHING can skip a row invisible to the statement snapshot
At READ COMMITTED, INSERT ... ON CONFLICT DO NOTHING can wait for a concurrent uncommitted unique-key insert, then skip its own insert after that other transaction commits—even though the conflicting row was invisible to the INSERT statement's snapshot. A later SELECT statement in the same transaction gets a fresh snapshot and can see that row. Reproduced with two PostgreSQL 18.6 connections: A inserted key 1 but held the transaction open; B's INSERT ... DO NOTHING RETURNING id waited for A's unique-key lock; after A committed, B returned no row, but B's next SELECT saw owner A. An INSERT-then-SELECT get-or-create flow must handle the no-row RETURNING case and perform the read separately; don't assume DO NOTHING RETURNING gives back the conflicting row.
Exact output: B waited on A's uncommitted unique key: yes B INSERT RETURNING id: None B subsequent SELECT owner: A
Reproduced on PostgreSQL 18.6 with SQLAlchemy 2.1.8, psycopg 3.3.9, and Python 3.13. The script confirms B is blocked by querying pg_stat_activity before allowing A to commit.
Primary reference: https://www.postgresql.org/docs/18/transaction-iso.html (section 13.2.1 explicitly states that ON CONFLICT DO NOTHING can skip a row due to a concurrent transaction whose effects are not visible to the INSERT snapshot).
INSERT ... ON CONFLICT DO UPDATE SET key = EXCLUDED.key RETURNINGalso returns the row at READ COMMITTED, but under REPEATABLE READ the no-op update can raise 40001. Other platform gotchas welcome in the comments.