Unique constraint violation in Postgres due to OS upgrade
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Upgrading glibc to >= 2.28 allows unique constraint violation in Postgres , as the sorting order changes for most of the collations. This may lead to database inconsistency.
Environment
- The Glibc library version 2.28 has been released on a majority of Linux systems:
- Redhat 8 (released May 7th, 2019)
- Debian 10 (July 6th, 2019)
- Fedora 29 (October 30, 2018)
- and others
- Diagnosis
- Postgres
Diagnosis
After upgrade from Debian Stretch (PostgreSQL 9.6) to Debian Buster (PostgreSQL 11) there were duplicated data in the database table with unique constraint applied. The data looked like this:
bitbucket=# select * from repository where project_id=146;Output
| id | slug | name | state | project_id | scm_id | hierarchy_id | is_forkable | is_public | store_id | description |
793 | text-rnn | text-rnn | 1 | 146 | git | 695abfc80b2e075ae7ad | t | t | ||
812 | ink_data | ink_data | 1 | 146 | git | 2f40bb01986ce5bc3b63 | t | f | ||
829 | ink_data | ink_data | 1 | 146 | git | cb51bbad5d4d26bb9ce9 | t | f |
ids 812 and 829 have exactly the same slug and project_id . But it shouldn’t be possible if the unique constraint (CONSTRAINT uk_slug_project_id UNIQUE (slug, project_id)) is applied to that table.
Cause
Glibc 2.28 breaks collation for PostgreSQL (and others?)
Solution
If there is an upgrade of the OS, and glibc upgrade is also involved make sure the indexes are rebuilt before the application is started. The rebuilding of all the indexes will prevent the issue from happening.
If the upgrade was performed by restoring the application database into upgraded operating system, the indexes should be recalculated automatically if pg_restore was used.
If the collation is set to ISO C and POSIX the upgrade doesn’t break the ordering.
- In case the duplication is observed, follow the steps in How to fix duplicate entires in Bitbucket PostgreSQL database to resolve the duplicates.