Open wpcarro opened 1 month ago
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?
Thanks for opening the issue 🙏
I can reproduce your first problem fairly easily; 'change type' operations are implemented by:
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?
I'm trying to change a table's
id
column frominteger
(int4
) tobigint
(int8
), but I'm struggling to complete the migration because other tables referenceid
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:
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?