A concurrent build that is still running is INVALID with its migration not
yet finished, exactly like a failed one. With the web and worker roles in
two containers, the second boot dropped the index the first was building,
which cancelled the first one's migration and left it failed until someone
resolved it by hand.
The check and the drop now run in one transaction that first takes Prisma's
migration lock without waiting and does nothing when another instance holds
it; an index Postgres reports as being built is never dropped. One
transaction also keeps lock_timeout on the statement it bounds behind a
transaction pooler, and the applied-migrations lookup quotes the schema, so
a mixed-case schema no longer reads as having none applied.
A CREATE INDEX CONCURRENTLY that fails half-way leaves an INVALID index,
and the migration re-run after migrate resolve --rolled-back finds it
through IF NOT EXISTS and does nothing: recorded applied, with an index
the planner never uses. 0477 has this shape.
The boot now drops an INVALID index that a not-yet-applied migration
builds concurrently before it runs migrate deploy, with a bounded lock
wait, so the re-run builds it again. An INVALID index whose migration is
recorded applied is named on stderr with the rebuild that mends it. The
deploy runbook says both.
A marked migration's DO block holds the locks it takes until it commits,
so an app transaction taking two of its tables in the other order can
deadlock with it. The block then rolled back whole, exactly as on a lock
timeout, so the boot retries 40P01 for marked migrations as it does 55P03.
Corrects 0415's note that the drops hold their lock for an instant.
Prisma ignores PGTZ, so a database whose default zone is not UTC ran every
migration in that zone, and a migration mixing timestamp and timestamptz
wrote shifted values. The boot wrapper now appends -c TimeZone=UTC to the
options parameter of DATABASE_URL for its Prisma calls, honouring
DATABASE_SESSION_OPTIONS_DISABLED like the app does.
migrate deploy runs a migration file statement by statement, so the
top-level SET LOCAL lock_timeout in 0412 did nothing: the migration
waited as long as the lock was held, with every write to measurements
queued behind it, and a file that failed half-way stayed half-applied.
0412 now takes its locks inside one DO block with a statement-scoped
timeout, which rolls back whole, after an idempotent CREATE OR REPLACE.
The entrypoint runs migrate deploy through scripts/migrate-deploy.mjs.
A 55P03 failure of a migration marked -- @retry-on-lock-timeout is
marked rolled back and retried after 15/30/60/120/240 s, read from
Prisma's report or, after P3009, from the stored row. Every other
failure stays fatal. A guard holds migrations to the shape that makes
the marker true, and 0411/0413 add their columns with IF NOT EXISTS so
a manual re-run is safe too.
Marc-André Bombeck