Two Test Suites, One Database

duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL: Key (typname, typnamespace)=(alembic_version, 2200) already exists.

That error came out of KittyLog's backend suite on July 21, 2026, during an adversarial review of a wave of pull requests. The suite had passed shortly before. Running it again, it passed. In between, something had tried to create Alembic's version table while another something was already creating it, and Postgres's system catalogs objected.

The setup is ordinary. KittyLog's integration tests run against one shared local test database. At session start, _init_test_database in tests/conftest.py drops every table and rebuilds the schema by running alembic upgrade head. One process, one database, clean slate every run. The shared database had been in place since December 2025 without incident.

The drop and rebuild is worth keeping

The drop-and-rebuild step is not incidental, so "just stop doing that" was off the table. The day before the collision, on July 20, we had replaced a hand-written test schema with the real migration chain, a change that caught real bugs on its own; that story belongs to another post.

So the rebuild stays. The problem was the assumption underneath it: that only one test run exists at a time.

Development became concurrent before the tests did

The tests themselves never run in parallel; there is an explicit no-xdist rule for the integration selection. What changed was the development process around them. This repo's workflow leans on parallel worktrees, with multiple agents each implementing an issue and each verifying its own branch by running the full suite. Two agents finishing near each other means two pytest processes, two _init_test_database calls, one database.

The race is a plain check-then-create. Alembic creates its alembic_version table without IF NOT EXISTS, so two concurrent alembic upgrade head runs collide in the catalogs, and one process dies with the error above. A clean re-run goes green, which makes it a flake rather than a correctness bug, but a flake that costs a full suite run to discover is still a tax on every wave of parallel work.

Why the boring option won

Issue #1321 listed three candidate fixes, and the cleverest ones lost.

Per-run database names (test_<pid>) would give every process true isolation, which sounds right until you price it: the test role needs createdb privileges that CI should never have, the per-run URL has to be threaded through every fixture, and a crashed run leaks a test_12345 database that somebody has to janitor. Highest complexity, for a low-priority flake.

Idempotent DDL doesn't actually fix it. DROP ... IF EXISTS was already in place; the window is inside Alembic's own table creation, which we don't control. Softer failures would just move the race, not close it.

What shipped instead is about thirty lines: an exclusive POSIX flock on a temp file keyed by a hash of the database URL, wrapped around the drop and rebuild. A second process blocks until the first finishes, then does its own clean rebuild. The kernel releases the lock if the holder dies, so there are no stale-lock hangs and no cleanup code. Different DATABASE_URLs hash to different lock files, so they don't block each other. On platforms without fcntl it degrades to no locking at all, which is exactly the old behavior.

The PR includes the receipts I like to see on a concurrency fix: the pre-fix logic, run twice concurrently, reproduced the exact catalog error; the patched logic under the same barrier had both processes exit clean; and a direct mutual-exclusion test showed a waiter blocking for 2.036 seconds and acquiring the lock at the holder's release timestamp. Concurrency fixes argued from vibes tend to come back.

When a file lock is enough

A file lock is the right tool when the contention is on one machine, the contenders are cooperating processes, and waiting is an acceptable outcome. All three held here: the shared database is local, every pytest process runs the same conftest, and an agent's suite taking a minute longer is invisible.

It stops being enough when any of those breaks. Across machines you need the database itself to arbitrate, with advisory locks or real per-run databases. If waiting is not acceptable, you need isolation, not serialization. And it does nothing for parallelism inside one run; the no-xdist rule still stands, because eight workers sharing one schema is a different problem than two suites sharing one database.

For a flake that shows up only when two robots run tests at the same time, thirty lines of flock and a temp file was the correct amount of engineering. I checked.

Source records

• Issue #1321, the _init_test_database collision report.
• PR #1327 / commit 04ead6c9, the cross-process file lock fix.
• Commit 43b91781, building the test schema from Alembic migrations.
• tests/conftest.py, _test_db_init_lock and _init_test_database.