retrofit
0.13.0
retrofit.sh

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 own BEGIN; and COMMIT;.
  • 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 own the 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 caller migrate writes no file, since the commit would end your transaction. Emit under own with a runner that opens no transaction of its own, narrow --from/--to so the change falls outside the window, or split the migration into files.
  • Under independent the 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.

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

Published .