Computed backfills

A backfill can be an expression over each row's own stored fields, so a migration can reshape data rather than only fill it with one value. Here each todo's priority is worked out from its legacy flag, by a function in a library:

// domains/planning/libraries/priorities.jadpouse planning/types { LegacyFlag, Priority }library priorities {    function from_flag(legacy_flag: LegacyFlag) -> Priority {        if (legacy_flag) {            return Priority.high        }        return Priority.normal    }}

database/migrations/20261020T1401_priority_from_flag/changes.jadpo:

// changes.jadpo/// Replaces the legacy flag with a priority worked out from it.migration {    Todo.priority: {        backfill: priorities.from_flag(LegacyFlag(Todo.legacy_flag))    }    Todo.legacy_flag: FieldChange.retired}

What a backfill may read

Todo.legacy_flag means each row's stored value, as on the right of an update's set: (Reads and writes). The expression may use:

• the entity's fields as they stand before this migration, including one this migration retires;
• constants, a constant declaration read through its library (priorities.default_priority, Constants);
• pure functions, such as those of the priorities library (Project layout and modules).

What it may not read. Another entity's field; a field the same migration adds, which no stored record has a value for yet; and a setting, directly or through a function it calls, a crypto operation's key: included, since jadpo migrate apply is given none and each deployment sets its own. Each is migration.backfill_outside_row (examples under "Errors"). The clock, the random generator, queries and actions are refused, as in any function.

A retired field is read as the built-in type its column holds (boolean, text, uuid, a whole number, numeric, timestamptz, date), since its declared type goes with it. So LegacyFlag(Todo.legacy_flag) gives the function the named type it takes. A retired enum or structured field isn't offered.

Its type is the field's declared type, as for any value set on the field (Reads and writes). A field with rules of its own takes a construction, which is refused unless it is proven safe.

Nothing in a backfill may fail, since a row whose value failed would have none. A call or construction nothing handles, or an attempt passing a failure on, is migration.backfill_can_fail. A defect, such as an overflow, still stops the batch: it rolls back, the column stays consistent, the run stops at that row, and jadpo migrate apply names the record's key and the fault, never the row's values.

Only the latest migration's backfills are checked, since jadpo check reads only its decisions. What an earlier one calls ran with the code it was written against.

There is no freeform SQL in migrations. A backfill is checked Jadpo, so a migration can't write a value its field's rules forbid.

How a backfill runs

A literal or an enum's variant (Priority.normal) is filled by one statement in the migration's transaction. Any other value is computed: the migration adds the column as optional and fills existing rows in batches, each in its own transaction, so a large table is never locked for the whole backfill. A last short step fills rows that servers still on the old build wrote meanwhile, and makes the column required.

When every operation in the expression has a SQL form, each batch is one UPDATE. Otherwise jadpo migrate apply reads each batch, works the values out in Jadpo and writes them back. The generated SQL says which.

By UPDATE. For the example above, the migration's SQL adds priority as optional, then a line saying how the backfill runs (-- backfill Todo.priority: by UPDATE, 1000 rows a batch, each batch its own transaction), a statement counting the rows left, and one batch, whose UPDATE sets "priority" = (CASE WHEN "todo"."legacy_flag" THEN 'high' ELSE 'normal' END).

Functions are written in place, up to 16 calls deep: their parameters are replaced by their arguments' SQL and their vars by their values. An if chain whose branches return becomes CASE, the last arm its else. So does a match on a plain variant, true and false, a literal, none or some. A var mut, a loop, or a statement that only runs for its effect has no SQL form. Each standard operation's form is in SQL forms: a whole number is always bigint, a decimal keeps its places, and a defect fails the statement. These forms, and those of the operators and conditionals, are tested against the runtime on live PostgreSQL.

A construction keeps its value as the runtime keeps it. Building a type with a fixed scale keeps exactly that many places, rounded half to even (decimal.round's form, then a cast to numeric(38, scale), which stops the statement past 38 digits where the runtime faults). So Amount(Whole(cost)), with Whole at scale: 0, keeps 2.50 as 2 and 3.50 as 4, as jadpo test does, and a decimal is stored at its field's places, rounded half to even, as the runtime stores one. No other rule changes a value built on Bool, Int, Decimal, Text, Bytes, Uuid, a date, an instant or a duration: ranges, lengths and patterns only refuse, a backfill's constructions are proved not to, and text isn't normalised as it is built. A standard type that changes a value as it is built (Html made safe, a coordinate kept as its double, a secret) has no SQL form, so a backfill building one runs in Jadpo.

In Jadpo. A backfill with an operation that has no SQL form (text.upper, a var mut) is worked out by jadpo migrate apply in the interpreter, with values identical to the runtime's. Its line in the migration's SQL says so (-- backfill Todo.priority: in Jadpo, 1000 rows a batch, …) and names a digest of the code it runs. The digest covers the backfill's body, every function it reaches (where each is written left out), the constants they read, every type they name along their chains with its fields and variants (rules and scales included), and the toolchain's version, whose runtime works the values out.

A change to any of them makes jadpo check ask for the migration to be written again, as a change to a backfill by UPDATE does. A database that ran the old code refuses the new one as changed, so a resumed backfill can't fill some rows one way and the rest another. The code it runs is the project's as jadpo migrate apply finds it, which must check without errors once a row needs it.

Each batch reads up to 1000 rows still without a value, past where the last ended, in key order, FOR NO KEY UPDATE, so a request on the old build can't change a field the value is worked out from before the batch commits. It writes the values back in one UPDATE … FROM unnest($1, $2) filling only rows still without one. The step that locks the table reads and fills the rows written meanwhile the same way. It reads values held in one column (the built-in types, an enum without data, and a standard type held in one, such as a percentage or a quantity, read and bound as a query and a write do), and fills those, or a structured value held as JSON, through the codec.

Only the latest migration's backfills are worked out with the project's code. An earlier migration's backfill in Jadpo that has rows to fill stops jadpo migrate apply (Filling Todo.priority for <migration> needs the build it was written in), keeping every step that committed, so it is applied with the build that wrote it and then this one. On an empty table nothing needs working out, so a new database takes every migration at once. jadpo dev applies migrations with the same interpreter; jadpo test builds its database on empty tables, so nothing needs it there.

Batches, locks and resuming

Every step of such a migration takes jadpo_migrations' lock first, as every migration does. A batch is up to 1000 rows, its own transaction at read committed, in key order from where the last ended ("id" > $1 ORDER BY "id" LIMIT 1000). It fills only the rows still without a value, so a batch run twice fills nothing twice, and it takes only its rows' locks. A batch that meets a concurrent change (a deadlock with a request writing the same rows) is tried again, up to three times, then the run stops.

Every backfill fills before any column is required. A store's section that fills a backfill in batches runs every backfill's batches first, in the order their columns are added. One step then locks each table against writes, never reads (SHARE ROW EXCLUSIVE), fills the rows written meanwhile for each column, and adds a check per column that it has a value. A backfill written out as one value beside them fills the existing rows in the section's first step, and again in that step the rows written meanwhile, and is required with the rest. Alone, it is one statement in the migration's transaction.

The last steps lock out writes and make no read wait on a scan. The check is added NOT VALID, reading no row, so every row written from then on is held to it. The next step, a transaction of its own, proves it (VALIDATE CONSTRAINT), reading every row under SHARE UPDATE EXCLUSIVE, which lets reads and writes go on. The last makes each column required, which PostgreSQL 12 and later do without reading a row once a proved check says every row has a value, then drops the check and records the migration. Adding the check, requiring the column and dropping the check each take ACCESS EXCLUSIVE for a moment, which waits for reads already running and holds new ones back meanwhile; no form of SET NOT NULL avoids that. postgres.sql opens each such step with a -- step: line saying what it does, and names the check <table>_<column>_filled.

The resume marker is a row in jadpo_migration_steps: the migration's name and digest, its next step, and the key the last batch ended at. It is written in the transaction of the step it records, so a run stopped anywhere, killed included, leaves the column consistent and carries on from the step after the last one committed. The table is the runtime's own, made the first time such a migration runs. A migration's row goes in the transaction that records it as applied; one whose digest changed since is refused as changed.

Progress. On a terminal, the live line says how far each backfill has got (Filling Todo.priority 2000 of 5000 rows, or Filling Todo.priority in Jadpo 2000 of 5000 rows). In plain output and CI nothing is printed while it runs, rather than a line per batch. The result lists each backfill under its migration with the rows it filled, its batches and how (2500 rows filled in 3 batches by UPDATE, or 2500 rows filled in 3 batches in Jadpo (slower than SQL)), and whether it carried on from an earlier run. --json gives them as backfills, each with its how ("in Jadpo"). jadpo migrate new lists "fill existing rows in batches in Jadpo" for one worked out in Jadpo.

Not built yet: a computed backfill for a field that allows less (a variant removed, a shorter max_length), which is refitted only with a value written out; and a backfill in Jadpo that fills a value held in several columns, or reads a field held so or as JSON.