Choose who opens the transaction
A migration script applies best as one transaction, and either the
script opens it or your runner does. You tell retrofit migrate which
with --envelope, and it writes the file to match: its own BEGIN; and
COMMIT;, or none. The value is the file’s envelope. migrate cannot
see how your runner sends the file, so the value is yours to state.
The envelope is the reference, and
Who owns the transaction
gives the model.
Find the condition your runner meets
Three conditions decide the value. Your runner’s documentation says which one holds; migrate does not detect it.
- Nothing opens a transaction around the file:
own, the default. The file holds its ownBEGIN;andCOMMIT;. - Your runner, or you, opens one transaction around the whole file:
caller. The file holds no wrapper; the transaction is yours. - Your runner sends each statement on its own, with no transaction
around the file:
independent.
A runner that sends the whole file as one simple Query, with no transaction of its own, meets the second condition: PostgreSQL runs the statements of one such message as a single transaction (Multiple Statements in a Simple Query).
Emit for that condition
retrofit migrate > migration.sql # nothing opens a transaction
retrofit migrate --envelope=caller > migration.sql # the runner opens one around the file
retrofit migrate --envelope=independent > migration.sql # the runner sends each statement alone
The file’s -- envelope: comment line, near the top, states what the
applier must do. Read it when you review the file. An own file runs
only under psql: a driver that sends it as one query fails at its
first line, so emit caller for such a runner.
Apply with psql
psql -X -v ON_ERROR_STOP=1 -f migration.sql
This applies an own file as its own transaction, and stops at the
first error with nothing committed. A file that commits part way (see
below) keeps every part
before the error. Do not add -1 to an own file: it
stops before its first statement and applies nothing.
To apply a file as one transaction that psql opens, emit it with
--envelope=caller and add -1, one -f per file:
retrofit migrate --envelope=caller > migration.sql
psql -X -v ON_ERROR_STOP=1 -1 -f migration.sql
Keep -X and -v ON_ERROR_STOP=1 on every line. Applying with
psql says what each one
guards against.
Set it once for the project
[project]
envelope = "caller"
Every migrate run in the project then emits caller files. Pass
--envelope to override the key for one run. The key is the project’s,
not a schema’s.
Split a file your runner wraps only in part
A runner that wraps some statements in a transaction and not others has no value that is true of the whole file. Split the migration into one file per group the runner wraps as a unit, and give each file the value that is true of it:
retrofit migrate --from seal --to 7a6b1234 --envelope=caller > 001-schema.sql
retrofit migrate --from 7a6b1234 --to last --envelope=independent > 002-backfill.sql
retrofit op show lists the opids to split at.
When the script needs a commit part way
Some changes need the transaction committed before the next statement: a new enum label used later in the same migration, or a partition detached concurrently. What migrate does then depends on the value:
- Under
ownthe file commits part way itself and applies in parts. stderr says the file is not all-or-nothing, and names each point a failure would leave committed. - Under
callermigrate writes no file, since the commit would end your transaction. Emit underownwith a runner that opens no transaction of its own, narrow--from/--toso the change falls outside the window, or split the migration into files. - Under
independentthe file is written; each statement already commits on its own, so the file is not all-or-nothing.
Mid-file boundaries lists each case, and Why some scripts commit in parts gives the reason. Recover after a failed apply or a merge covers a file that stopped part way.
Related
- Emit a migration script: the window, the dump and the reverse script.
- Bound how long a migration waits for a lock: the lock wait, per envelope.
- Who owns the transaction: the term.
retrofit migrate: every rule, as reference.