Content
View differences
Updated by Attila Dombi over 1 year ago
**The problem:**
[We discovered](https://github.com/opf/openproject/pull/17119/files#r1846927483) some type mismatches between the `*_id` foreign keys and their referenced primary keys in our database.
For example the `work_packages.id` column is a `bigint` but the `hierarchical_item_hierarchies.ancestor_id` and `hierarchical_item_hierarchies.descendant_id` are `integers`.
If we ever have a `work_packages.id` (`bigint`) bigger than what an `integer` can hold, then storing it in the `hierarchical_item_hierarchies.ancestor_id` will not be reliably possible, because it will cause an overflow.
For smaller databases this is unlikely to happen, because `integer` can hold ids up to 2.147.483.647, but it is definitely a possibility.
There are a few more examples of this in our `structure.sql`.
**The solution:**
`structure.sql`. Changing the foreign keys to `bigint` would be recommended in order to avoid integer overflows, which is very unlikely, but it's possible on large databases.
From Klaus:
> unfortunately bigint and int are not binary compatible in postgres, so you cannot simply `ALTER TABLE x ALTER COLUMN y TYPE BIGINT` without a complete rewrite of the table. So this has do be done with a downtime or we have to do a complicated migration adding a new field, filling it and removing and renaming the old... See [https://zemanta.github.io/2021/08/25/column-migration-from-int-to-bigint-in-postgresql/](https://zemanta.github.io/2021/08/25/column-migration-from-int-to-bigint-in-postgresql/)
**Possible explanation of why we have this problem:**
As it turns out, those columns were from pre rails 5.1 times, when the database ids had `integer` type. In rails 5.1 primary keys were changed to `bigint`.
[We discovered](https://github.com/opf/openproject/pull/17119/files#r1846927483) some type mismatches between the `*_id` foreign keys and their referenced primary keys in our database.
For example the `work_packages.id` column is a `bigint` but the `hierarchical_item_hierarchies.ancestor_id` and `hierarchical_item_hierarchies.descendant_id` are `integers`.
If we ever have a `work_packages.id` (`bigint`) bigger than what an `integer` can hold, then storing it in the `hierarchical_item_hierarchies.ancestor_id` will not be reliably possible, because it will cause an overflow.
For smaller databases this is unlikely to happen, because `integer` can hold ids up to 2.147.483.647, but it is definitely a possibility.
There are a few more examples of this in our `structure.sql`.
**The solution:**
From Klaus:
> unfortunately bigint and int are not binary compatible in postgres, so you cannot simply `ALTER TABLE x ALTER COLUMN y TYPE BIGINT` without a complete rewrite of the table. So this has do be done with a downtime or we have to do a complicated migration adding a new field, filling it and removing and renaming the old... See [https://zemanta.github.io/2021/08/25/column-migration-from-int-to-bigint-in-postgresql/](https://zemanta.github.io/2021/08/25/column-migration-from-int-to-bigint-in-postgresql/)
**Possible explanation of why we have this problem:**
As it turns out, those columns were from pre rails 5.1 times, when the database ids had `integer` type. In rails 5.1 primary keys were changed to `bigint`.