retrofit
0.14.0
retrofit.sh

Bound how long a migration waits for a lock

A migration statement that waits for a table lock queues every later query on that table behind it. retrofit migrate --lock-timeout writes PostgreSQL’s lock_timeout setting into the script, so a statement that cannot get its lock within the bound gives up instead of waiting, and its transaction rolls back. The lock timeout is the reference, and Why bound the lock wait gives the reason.

Bound the wait for one migration

retrofit migrate --lock-timeout=2s > migration.sql
psql -X -v ON_ERROR_STOP=1 -f migration.sql

The file sets the bound at the head of each transaction it opens, as SET LOCAL lock_timeout = '2s';. A statement that waits longer fails with canceling statement due to lock timeout, its transaction rolls back, and psql stops. The bound takes a unit, as in 500ms, 2s or 1min; The lock timeout gives the accepted forms.

The bound applies to each lock a statement takes, row locks included, so a statement that takes several locks can wait up to the bound on each.

Set a bound for every migration

[project]
lock_timeout = "2s"

Every migrate run in the project then writes the bound. Pass --lock-timeout to override it for one run, and --lock-timeout=off to leave it out.

Retry after a give-up

How you retry depends on how the file applies:

  • A file that is one transaction (the default, or one emitted with --envelope=caller) rolls back whole. Run it again once whatever held the lock is done, and once your runner allows a re-run.
  • A file that commits in parts keeps the parts before the give-up. Running it again fails at the first statement already applied: follow Finish a migration that stopped part way.

migrate retries nothing itself.

Bound the wait when each statement applies alone

A file emitted with --envelope=independent opens no transaction, so there is nothing for SET LOCAL to scope into, and migrate writes no such file with a bound. Set the bound on the runner’s connection instead, for example:

PGOPTIONS='-c lock_timeout=2s' psql -X -v ON_ERROR_STOP=1 -f migration.sql

and emit with --lock-timeout=off when the project sets one.

Check what the bound does not reach

The bound covers the statements inside the file’s transactions. A few run outside them and wait without it: a partition detached concurrently, an index created or dropped concurrently, a raw script whose DO block commits or rolls back, and statements in a data record that commits on its own. While a bound is set, migrate names each such statement on stderr. A data record that sets lock_timeout itself replaces the bound for the rest of its transaction. The lock timeout lists each case.

Under --envelope=caller the bound lasts until your transaction ends, so it also bounds whatever else you send in that transaction, your runner’s own statements included.

Before 1.0, any release may change behavior; the release notes list what changed.

Published .