Fix the migration planner for a new column

Review the AI's codeEasy10 min

Your task

An AI's migration, ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'active', ran instantly in staging and locked the 200 million row orders table in production for 9 minutes, taking checkout down. Whether that statement is instant or rewrites the whole table depends on facts the AI never saw. Now a planner picks the steps from those facts.

An assistant wrote plan_online_migration(pg_major, default_is_volatile, row_count, batch_size). It is in the editor. It has a bug. Find it and fix it.

Input and output:

  • pg_major: the Postgres major version, such as 12.
  • default_is_volatile: true when the default is computed per row, such as now() or gen_random_uuid(), and false for a constant like 'active'.
  • row_count: how many rows the table holds.
  • batch_size: how many rows each backfill batch updates.
  • Return the migration steps, in order.

Rules the tests check:

  • If pg_major is 0 or less, row_count is below 0, or batch_size is 0 or less, return [].
  • Every plan starts with "lock_timeout=3s".
  • Postgres 11 and later store a constant default in the catalog, so adding the column is instant. Only when pg_major is at least 11 and the default is not volatile, the plan is ["lock_timeout=3s", "add_column_with_default"].
  • In every other case, adding the column with its default rewrites the table under a lock. The plan is then "lock_timeout=3s", "add_column_nullable", then "backfill_batches=N" with N the row count divided by the batch size, rounded up, then "set_default", "add_not_null_not_valid" and "validate_not_null".
  • Leave out the backfill step when row_count is 0.

Examples

plan_online_migration(pg_major: int, default_is_volatile: bool, row_count: int, batch_size: int) -> list[str]
Example 1
Inputplan_online_migration(12, False, 200000000, 10000)
Output["lock_timeout=3s", "add_column_with_default"]
Example 2
Inputplan_online_migration(12, True, 200000000, 10000)
Output["lock_timeout=3s", "add_column_nullable", "backfill_batches=20000", "set_default", "add_not_null_not_valid", "validate_not_null"]
Example 3
Inputplan_online_migration(9, False, 200000000, 10000)
Output["lock_timeout=3s", "add_column_nullable", "backfill_batches=20000", "set_default", "add_not_null_not_valid", "validate_not_null"]