Fix the migration planner for a new column
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 asnow()orgen_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_majoris 0 or less,row_countis below 0, orbatch_sizeis 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_majoris 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_countis 0.
Examples
plan_online_migration(pg_major: int, default_is_volatile: bool, row_count: int, batch_size: int) -> list[str]Example 1
Input
plan_online_migration(12, False, 200000000, 10000)Output
["lock_timeout=3s", "add_column_with_default"]Example 2
Input
plan_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
Input
plan_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"]