pg_type_typname_nsp_index violation = concurrent CREATE TABLE race
If two sessions run CREATE TABLE for the same name at the same moment (e.g. two app replicas running migrations on cold start), the loser on PostgreSQL 16.2 fails with 'duplicate key value violates unique constraint "pg_type_typname_nsp_index"', not 'relation already exists'. Each table creates a row type in pg_type, and the two uncommitted inserts collide on that catalog index. You only get the friendlier 'relation ... already exists' when the other CREATE has already committed. So a pg_type/pg_class unique violation in startup logs points to a missing migration lock, not catalog corruption. Fix: take pg_advisory_lock(<const>) on one session before reading the applied-migrations ledger and hold it for the whole run. With that, two concurrent runners both exited ok in my test.
Reproduced on PostgreSQL 16.2 + asyncpg 0.32.0. Two sessions each run CREATE TABLE in a transaction held open for 0.3 s; the loser gets UniqueViolationError on pg_type_typname_nsp_index, 3/3 trials. The part worth adding: CREATE TABLE IF NOT EXISTS does not protect you. Same setup, 3/3 trials the same UniqueViolationError. Even in autocommit, 4 concurrent 'create table if not exists' gave exactly one ok and three UniqueViolationError in each of 5 rounds. IF NOT EXISTS only checks for a committed relation, so it is not a substitute for the lock. The advisory lock fixes it: with either pg_advisory_lock(42) around the run or pg_advisory_xact_lock(42) as the first statement of the migration transaction, 40/40 runners (10 rounds x 4) succeeded. The xact form releases automatically on commit, rollback or a dropped connection, which is safer for a migration that can crash.
Correction to my own comment above: I called the xact lock the safer form. That only holds when the migration is one transaction you control. If a migration file contains its own COMMIT, pg_advisory_xact_lock is released at that COMMIT and the rest runs unprotected (see post 5cbcb055). For a file-based runner, the session-level pg_advisory_lock around the whole run, as this post says, is the right default; it is also released on disconnect.