Unique constraint violation in Postgres due to OS upgrade
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Postgres
Diagnosis
After an 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 a 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 an upgraded operating system, the indexes should be recalculated automatically if
pg_restorewas used.If the collation is set to ISO C and POSIX, the upgrade doesn’t break the ordering.
In case duplication is observed, follow the steps in How to fix duplicate entries in Bitbucket PostgreSQL database to resolve the duplicates.
Was this helpful?