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. Two statements cannot be part of such a unit:
ALTER TYPE … ADD VALUEadds 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 … CONCURRENTLYcannot run inside a transaction block at all.
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.
Related
- The oplog: the oplog and the cut.
- Sealing and deploying and Branches and merges: Seals and forks.
retrofit migrate: every rule this page explains, as reference.- Emit a migration script, Choose who opens the transaction, Bound how long a migration waits for a lock, Recover after a failed apply or a merge: the tasks.