Narrow window where concurrent inserts can miss a unique conflict

[jdatcmd]

Summary

A narrow window exists where two concurrent transactions inserting the same key can both miss the unique conflict.

Detail

Uniqueness is enforced through the supporting index over rows as they are inserted. While one transaction's inserting statement is still in flight, its rows are not yet flushed and are not visible to another backend, so a second transaction inserting the same key during that window may not see the conflict. The window closes when the inserting statement flushes at statement end, after which the conflict is detected.

Single-session inserts and inserts separated in time are not affected.

Source

Relates to the eager row-number reservation and the index fetch path introduced in the index and constraint work (src/columnar_write_state.c, src/columnar_tableam.c).

Status

Documented in the README limitations section. Tracked here for a future dedicated pass. A complete fix likely requires speculative insertion style coordination across backends before the rows are flushed.

[jdatcmd]

Fixed in 85b7854 on main.

Root cause: pgColumnar already inserts index entries eagerly per row, but a row's columnar data is invisible to other backends until the statement-end flush. PostgreSQL's btree uniqueness check (_bt_check_unique) uses a dirty-snapshot heap fetch, and columnar_index_fetch_tuple returns false for a buffered-but-unflushed row in another backend, so two transactions inserting the same key in overlapping windows could both succeed.

Fix (per-key lock): new src/columnar_unique.c, called from the AM insert and update paths before the executor's index maintenance. For each applicable unique index it takes a transaction-scoped advisory lock keyed by an opclass-safe hash of the key value(s), so inserters of an equal key serialize. The second inserter waits until the first commits and flushes, then the ordinary btree check raises unique_violation. Held to commit; on-disk format 2.0 unchanged.

Opclass-safe hashing (the correctness-critical part): the key is hashed with the type's default hash proc under the index collation, and only when the index equality provably matches the type default opclass family, so keys that are equal under the index (numeric 1.0 vs 1.00, citext case, collation-aware text) always map to the same lock. Non-default opclasses (e.g. text_pattern_ops) and hashless types fall back to a coarse per-index lock (correct, more serialization). Hashes are bucketed (columnar.unique_lock_buckets, default 128) to bound the lock count; columnar.enable_unique_insert_lock (default on) can disable it.

Edge cases handled: multi-column, partial (predicate evaluated per row), expression, multiple unique indexes, NULLS DISTINCT (no lock on NULL key), NULLS NOT DISTINCT on PG15+ (canonical NULL hash).

Tests: test/unique_conc.sh drives two sessions with a pg_stat_activity lock-wait barrier and asserts fails-before (lock disabled: duplicate commits, count 2) and passes-after (blocks then unique_violation, count 1) for same-key and opclass-equal numeric 1.0/1.00, plus multi-column, partial, different-keys-do-not-block, NULLS DISTINCT/NOT DISTINCT, same-statement duplicate, and aborted-first-inserter. Full PostgreSQL 13 to 19 matrix (all suites incl. unique_conc) passes via test/run_all_versions.sh.

Residual: different keys sharing a bucket serialize (harmless over-serialization); a genuine same-key conflict can occasionally surface as a deadlock abort instead of unique_violation (both reject the duplicate); non-default opclasses use the coarse per-index fallback. Documented in README.