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, 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.
Related
- Choose who opens the transaction: the envelope decides where the bound goes.
- Recover after a failed apply or a merge: a give-up in a file that commits in parts.
- Emit a migration script.
retrofit migrate: every rule, as reference.