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?

[maingoh]

I also have a table that is reaching the integer limit on the primary key. Doing the migration to biging manually without downtime is quite painful if the table is referenced. Having pgroll automating this process would be awesome !

[gulcin]

Thanks for providing feedback @maingoh, it gives us more clarity when we prioritize issues for upcoming releases. We tagged this as part of the v1 milestone and will keep you informed as we make progress.