Why

utopia-server runs build_vector_index jobs with no serialization and worker concurrency defaults to 64. When two builds for the same table are claimed together (two embedding dimensions arriving at once), both issue CREATE INDEX CONCURRENTLY on chunks. Each CIC has to wait for every other transaction on the table to finish and each is such a transaction for the other; with ingest writing chunks alongside, Postgres reports deadlock detected (40P01), one build fails and is retried. vector_index::build's own comment says builds are meant to run one at a time, but nothing enforced it.

Found while merging the store tests into one process (#886), where the same pair of statements deadlocked 3/3 times; reproduced in isolation at roughly a 50 % rate per attempt when a concurrent transaction touches chunks, and never when the two builds start more than ~10 ms apart.

What

build takes a session-level advisory lock (pg_try_advisory_lock polled every 250 ms) on its held connection before the CIC work and releases it afterwards; if the release fails the connection is closed instead of returned to the pool so a stale lock cannot block future builds. Session level because CIC cannot run inside a transaction, so a transaction-scoped lock has nowhere to live. Cross-worker and cross-instance.

Why try-lock and not the blocking pg_advisory_lock: the blocking call is itself a statement holding a snapshot, and CIC's last phase waits for every older snapshot, so the queued build waited on the running build's lock while the running build waited on the queued build's snapshot: the same deadlock in a new place. Verified locally: the blocking variant failed 10/10, the polled try-lock passes 10/10.

Test

two_dimensions_built_at_once_take_turns in tests/a_vector_index_is_built_by_a_job.rs: builds dims 11 and 13 concurrently under a concurrent transaction on chunks, eight rounds. Against the unfixed library it failed 5/5 runs with 40P01; with the fix 10/10 pass. The file stays a standalone test binary (see #886) because it does DDL on a shared table.

Also re-ran a_vector_build_uses_one_connection (pool of one connection: the lock is taken on the same held connection, so it still fits), the_nearest_chunk_is_found_however_it_is_reached and a_batch_gathers_its_neighbours_in_order: 3/3 clean, and pg_locks shows no advisory lock left behind.

cargo fmt --all --check and cargo clippy -p utopia-store --all-targets -D warnings clean.

🤖 Generated with Claude Code