Change Column Type when FK

[wpcarro]

I'm trying to change a table's id column from integer (int4) to bigint (int8), but I'm struggling to complete the migration because other tables reference id as a foreign key.

I tried following this example: https://github.com/xataio/pgroll/blob/main/docs/README.md#change-type

Here's what I have:

{
  "name": "00_testing",
  "operations": [
    {
      "alter_column": {
        "table": "inspection_item",
        "column": "id",
        "type": "bigint",
        "up": "CAST(id as bigint)",
        "down": "CAST(id as integer)"
      }
    }
  ]
}

When I try to complete the migration, I get this error:

I see examples for adding/deleting foreign key constraints, but I don't see any examples of changing the type of a column that is referenced as a foreign key.

Does anyone have examples of this?

[wpcarro]

Another surprising thing that I've found is values changing when I don't expect them to

Let's say my migration looks like this:

{
  "name": "03_downsize_keyspace",
  "operations": [
    {
      "alter_column": {
        "table": "inspection_item",
        "column": "id",
        "type": "serial4",
        "up": "cast(id as int4)",
        "down": "cast(id as int8)"
      }
    }
  ]
}

Before running this, I try sanity-checking that my "up" expression will do what I expect: not change values when downsizing the footprint. I can do this because my keyspace fits within int4:

select id, cast(id as int4)
from inspection_item
where id != cast(id as int4)
-- correctly returns no values

But when I run...

pgroll start sql/03_downsize_keyspace.json

...and then I run this query, I see that id and _pgroll_new_id differ.

select id, _pgroll_new_id
from inspection_item
where id != _pgroll_new_id
-- returns many values

What's happening here?

[andrew-farries]

Thanks for opening the issue 🙏

I can reproduce your first problem fairly easily; 'change type' operations are implemented by:

• creating a new column with the new type on migration start
• dropping the old column and renaming the new one on migration complete.

The drop of the old column on complete doesn't work because the column is referenced as part of a FK constraint. pgroll needs to be smarter here and update the FK to point at the new column before the dropping the old one.

I will add this issue to the v1 milestone.

I'm unable to reproduce the second problem. Could you please provide the series of migrations before 03_downsize_keyspace and open a separate issue for it?

[ryanslade]

Won't pgroll also need to update the type on all the columns references the original?

It will work if Postrges is able to cast the types automatically, for example int4 -> int8. But, if the type are not compatible for example int -> text then we'll need to run the same translation step from the original table on all the foreign keys.

One way we could do this is by looking for a USING expression on the alter type expression and repeat it for all the foreign keys by inspecting the schema. @andrew-farries Does this sound like something we want to do?