retrofit
0.15.0-wip.89
retrofit.sh

These docs are for 0.15.0-wip.89, a WIP, and may describe what no release does yet. The newest release is 0.14.0: this page for 0.14.0.

How a migration applies

A migration script is the difference between two points in the oplog, written as SQL. Each point is a cut: the schema as it stands after one operation. The script holds the statements that turn a database at one cut into a database at another. Writing it is where retrofit migrate stops: your runner, or psql, sends it to PostgreSQL, and the shell is what connects. This page gives the model behind the choices that follow from that split: what a script assumes, who owns its transaction, why some scripts commit in parts, why a lock wait is worth bounding, and how a reverse script is built. retrofit migrate is the reference, and Emit a migration script is the task guide.

A script assumes a starting point

A migration is only correct against a database in exactly the state its first cut describes. Each statement is written against the schema the replay holds at that point: a RENAME COLUMN names the column as it is called there, a DROP names something that exists there. Against any other state, the same statements fail, or worse, succeed on the wrong thing. That is why every script opens with an assumes database is at line naming the cut, and why no option removes it.

The starting cut is therefore the one input that has to be right. A Seal is a fixed point: the operations before it do not move. An unsealed operation is not: it moves as the log grows, and a reconcile rebases it. So the default start is the latest Seal, and a start named by an unsealed operation draws a note. Where you do not know which cut a database is at, a schema-only dump of it answers the question: identify fingerprints the dump against the oplog, and migrate --from-dump starts the script where the dump places the database.

Who owns the transaction

PostgreSQL applies a script well when the whole of it is one transaction: a failure part way leaves nothing applied. Something has to open that transaction, and only one thing can. The script can hold its own BEGIN; and COMMIT;, or the runner that sends it can open one around it. Both at once goes wrong quietly: PostgreSQL answers a BEGIN; inside an open transaction with only a warning (SQLSTATE 25001), and the file’s COMMIT; then ends the runner’s transaction early, so whatever follows it runs outside any transaction.

How your runner sends the file is invisible to retrofit, so it does not guess. The envelope is your statement of which case holds, and the file is written to match it: own (the file opens its transaction), caller (you opened one), or independent (no transaction is open around the file, and each statement applies on its own). The combination that goes wrong, a wrapper inside an open transaction, is the one no value writes. Since it is still reachable by mistake (psql --single-transaction on an own file, for one), an own file checks for an open transaction before its first statement and stops with a named error rather than applying half of itself.

Every file also states its envelope in an -- envelope: comment line. No runner reads it. It is there for the person reviewing the file, or reading it after a failed deploy, because the value is a fact about how the file was meant to be applied that the SQL alone does not show. Choose who opens the transaction maps the conditions to the values.

Why some scripts commit in parts

Most DDL runs inside a transaction, and a script of nothing else is one all-or-nothing unit however long it is. These statements cannot be part of such a unit:

  • ALTER TYPE … ADD VALUE adds an enum label that the same transaction cannot use. PostgreSQL makes the new label visible only after the transaction that added it commits. A later statement that uses the label needs a commit between the two.
  • DETACH PARTITION … CONCURRENTLY, CREATE INDEX … CONCURRENTLY and DROP INDEX … CONCURRENTLY cannot run inside a transaction block at all.
  • A DO block whose PL/pgSQL commits or rolls back, as a raw script that backfills in batches does, ends transactions of its own. PostgreSQL runs a DO block called inside a transaction block as one unit, so its COMMIT fails there.

A window that holds one of them has to close the transaction and open a new one around it. Each part is then a segment: its own transaction, committed before the next begins. A failure in a later segment leaves the earlier ones committed. The all-or-nothing is lost, and it is the window’s loss, not a setting’s: no envelope makes an added label usable in the transaction that added it.

Not every boundary costs atomicity. A commit that only has to come before a later reader costs nothing when nothing follows it. Whether something follows depends on more than operators. A data record holds a position in the script too: a seed dataset is written after all DDL, so adding one puts work after the last operator, and a boundary that separated nothing now separates the DDL from the data. Narrowing the window cannot undo that, because the seed block follows every operator in any window.

Under own, the file holds the boundaries itself, and a segmented file is still a correct file: it applies in order, and the run reports what a failure in each segment leaves committed. Under caller, a boundary would land inside your transaction, so there is no correct file to write, and the run stops. Under independent, each statement is already its own transaction, so the boundary is met without being written, and the run only says the file is not all-or-nothing.

One shape has no legal placement under any envelope: adding an enum label and using it in a later default or expression in the same window. The use needs the label committed; the window is one migration. Two windows, the add in the first, are the only form PostgreSQL accepts.

A data body that holds its own COMMIT; or BEGIN; would open a segment nobody counted, and the count the run reports is something you plan recovery around. That is why such bodies are screened and refused by default, and why admitting them turns the count into a lower bound.

Why bound the lock wait

A DDL statement that waits for a table lock takes a place in that table’s lock queue, and every later query on the table queues behind it. A migration that waits behind one long report query can stall the table for everyone, for as long as the report runs.

PostgreSQL’s lock_timeout setting makes a statement give up instead of waiting past a bound. The failure is clean: the statement is cancelled, its transaction rolls back, and the queue moves again. The setting has to be scoped to the migration’s own transaction, which is why the file writes it as SET LOCAL: it ends with the transaction whether that commits or rolls back, and cannot outlive the file on a pooled connection the way a plain SET would. Where the file opens no transaction (independent), SET LOCAL would do nothing and a plain SET would leak, so the bound belongs on the runner’s connection instead. Bound how long a migration waits for a lock gives the steps.

A give-up is a failure like any other, and what it leaves depends on the segments: a script of one transaction leaves nothing, a segmented one leaves its earlier segments committed. Bounding the wait trades a stall for a retry, so the retry has to be one you can make (Recover after a failed apply or a merge).

How a reverse script is built

Undoing a window operation by operation in reverse order would undo history, including the steps that later steps already undid: a column added, renamed and dropped would be re-created to be renamed and dropped again. --down instead computes, for each element the window touched, the net change from its state now back to its state at the start, and renders that change with the same renderer the forward script uses. A down statement is the statement the forward would write for the same change, with the same locks and markers.

Some of that change cannot bring data back. A column the window dropped is re-created from its definition, but its rows are gone. A column the window added is dropped, and with it the rows written since. Such a window is not refused: a rollback that loses data is sometimes the right call, and refusing it would only send you to write the same DROP by hand. Instead, the script’s header reports every loss, every rule it puts back, and everything it does not restore, so the decision is made on the record. Where the down cannot be computed at all (a raw script with no declared reversal, for one), the run refuses and names a way back, usually a narrower window or a compensating forward.

A rule a forward relaxed, such as a dropped NOT NULL, meets rows that traffic wrote while it was relaxed. The down cannot know whether those rows matter, so the relaxation can declare it: return the rule as a guard that fails on a bad row, return it NOT VALID and leave the rows, or rewrite the rows to fit. The declaration sits on the relaxation, so the decision is made when the rule is relaxed rather than during the rollback.

Why some windows are refused

A script that silently left something out would be worse than none: the apply would succeed and the database would be wrong. So each limit is a refusal before any byte is written, not an omission. An operator with no renderer refuses rather than writing an empty script. A copy into another schema that cannot hold a function, a view or a moved row refuses rather than writing a copy without it. A ReplaceFunction that PostgreSQL would reject in place refuses at emit time, where the fix is an operation away, rather than at apply time.

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

Published .