xataio / pgroll

PostgreSQL zero-downtime migrations made easy
https://www.xata.io
Apache License 2.0
2.97k stars 54 forks source link

Rename-only 'alter column' operations shouldn't be restricted to tables with PKs #340

Closed andrew-farries closed 4 months ago

andrew-farries commented 4 months ago

Most 'alter column' sub-operations require that the table has a single column primary key or a unique, non-nullable column. This is required for backfilling.

'alter column' operations that have no sub-operations other than a rename should work on tables that don't meet this restriction because they don't require backfills.

This series of migrations should work:

{
  "name": "01_create_table",
  "operations": [
    {
      "create_table": {
        "name": "products",
        "columns": [
          {
            "name": "name",
            "type": "varchar(255)"
          }
        ]
      }
    }
  ]
}
{
  "name": "02_rename_column",
  "operations": [
    {
      "alter_column": {
        "table": "products",
        "column": "name",
        "name": "bananas"
      }
    }
  ]
}

but the second migration currently fails with:

Failed to start migration: migration is invalid: a backfill is required but table "products" doesn't have a single column primary key or a UNIQUE, NOT NULL column