Between any two glibc versions, locales can change, and those changes alter the order of character string comparison (text/varchar/char/citext). It may also happen that locales are removed, such as en_US@ampm, present in RHEL8 and removed in RHEL9, and aa_ER@saaho, present in RHEL9 and then renamed to ssy_ER in RHEL10. Since PostgreSQL relies on locales to compare character strings, a glibc change can directly affect the processing of data already stored and data still to be written.
The latest important milestone happened between RHEL7 and RHEL8. Back then, the change was massive; it affected almost every locale, and thousands of PostgreSQL clusters ran into the problem.
Most of the available information focuses on the idea that only indexes are affected, without detailing what happens with other potentially compromised areas. Reindexing is the suggested solution, and although it helps, it can leave unresolved issues behind.
To help myself, I am working on a project (pg-glibc-collation-audit) that aims to deterministically identify locale changes between two glibc versions. To avoid making this blog a 50-page document, I have compiled all the evidence in that repository accessible via the links.
I have put together a list of 10 cases I know of where a glibc change affects the database and causes data corruption, and I have added two more that do not lead to data corruption but do change how the database behaves after an upgrade.
The full breakage map: Breakage map
The evidence comes from a simple method. Two PostgreSQL clusters are built. One on Rocky Linux 8.9 acting as the primary, and another on Rocky Linux 9.3 that starts as a replica until it is promoted to primary (read/write).
The locale used is sv_SE. The reason is simple; the previously mentioned tool already let me know that sv_SE was one of the locales affected between the two versions.
In glibc 2.28, the sv_SE locale treats W as a variant of V and sorts them together. In 2.34, W is a letter of its own and sorts after V. The example below is enough to explain it.
Full session, with the EXPLAIN plans and the raw output from both nodes: The change
wa stops sitting between va and Vasa and moves to the end. That single move is the origin of everything that follows. Byte order (COLLATE “C”) returned Vasa Wasa va vind vz wa wind on both nodes, so the locale is real on both sides, and the only variable is the glibc version.
Problem: The index stores the rows in the order glibc 2.28 put them. When PostgreSQL looks for waa000 with the 2.34 rules, it walks down the tree to where that word should be today, but the word is not where it is written. It finds nothing, and it has no way of knowing what went wrong.
Diagnosis: Ask the same question of the index and of the table. Use bt_index_check() and bt_index_parent_check() from the amcheck extension to check whether it passed.
Evidence: case 1.
Solution: REINDEX INDEX where needed.
Problem: PostgreSQL checks key uniqueness by looking it up in the index before inserting it. When that lookup does not find the row already stored, the insert goes through. The constraint is still in the catalog, but it is no longer effective.
Diagnosis: Same as regular B-Tree indexes
Evidence: case 2.
Solution, if the application has not written yet: REINDEX INDEX where needed. Nothing got in while the index was blind, so it rebuilds without complaining.
Solution, if it has already been writing: The duplicates that got in are real rows of the table, and REINDEX refuses to rebuild the index while they are there. You have to find them with a sequential scan, decide which copy survives, delete the rest, and only reindex after that.
Problem: A foreign key does not have its own index. PostgreSQL enforces it by looking up the value in the parent’s unique index, so when that index goes blind, the foreign key answers wrong in both directions.
Diagnosis: Test both directions, inserting a child whose parent already exists and deleting a parent that has children. Using the amcheck extension when indexes are available.
Evidence: case 3.
Solution, if the application has not written yet: Reindex the parent’s unique index. The foreign key answers correctly again in both directions.
Solution, if it has already been writing: You also have to review what the application did while the index was broken. A DELETE that reported zero rows left work undone, and nobody noticed; the INSERTs for children whose parent was present came back rejected.
Problem: LC_COLLATE decides the order, and LC_CTYPE decides what counts as a letter and what lower() returns. Both come from glibc, and only the first has a version number. I compared the two nodes character by character, across the whole Unicode catalog. Some code points answer differently, and almost all of them are characters that did not count as letters before and do now.
Diagnosis: Two characters are enough. In one of them, what lower() returns changed, and that is why a functional index on lower(login) stops finding a user who exists in the table. The other did not count as a letter before and does now, and that is why a code stored long ago now violates a CHECK that forbids letters, without anyone touching it.
Evidence: case 4.
Solution: Reindex the functional indexes and check the stored rows again against any constraint or expression that depends on LC_CTYPE. That includes the lower() and upper() functions, the character classes of a regular expression such as [[:alpha:]], and comparisons with ILIKE, which ignore case according to the current locale.
Problem: It is no different from a B-tree index, and none of the other access methods is unaffected. Each one fails in its own way, though, and the way to diagnose it differs.
Diagnosis: The partial index, defined as WHERE w < ‘vz’, still returns the 19,232 rows it indexed, and almost 10,000 of them no longer satisfy its own predicate.
GiST loses rows the same way a B-tree does, with one difference that matters. The B-tree returns 0, and the GiST returns a partial result. A 0 gets noticed, a non-zero that looks possible does not.
BRIN, with a wide range, returned the right number and looked unaffected. It stores the minimum and maximum values for each block group and rechecks every row against the live collation, so it only loses rows when an entire group is discarded. Testing the 2,704 three-letter prefixes, 206 lose rows, and vfx is one of those.
Evidence: case 5.
Solution: REINDEX repairs all three.
Problem: An EXCLUDE constraint is an index underneath, so it fails the same way a unique index does. The constraint is still in the catalog but is no longer enforced.
Diagnosis: The value is already in the table. The constraint should reject a second copy.
Evidence: case 6.
Solution, if the application has not written yet: REINDEX. There are no conflicting rows yet, so the index rebuilds with nothing further to do.
Solution, if it has already been writing: You have to look for the conflicting rows that were accepted while it was broken, the same as with the duplicates in case 2.
Problem: There is no broken index here. The row is physically in the wrong place. It was written to the low partition because under 2.28 waa000 came before the vz boundary. Today it comes after. The planner prunes the low partition because it knows nothing greater than vz can be in there, and the row becomes invisible.
Diagnosis: Ask where the rows are, look for one through the partitioned table, and see which partition a new copy would be routed to today.
Evidence: case 7.
Solution, if the application has not written yet: No REINDEX touches this. The affected rows have to be moved, deleted from their partition, and reinserted into the parent so that tuple routing places them correctly.
Solution, if it has already been writing: The same, and first you have to look for the values that ended up with a copy in each partition. A new row with the same value is routed today to the upper partition, while the old one stays in the lower one.
Problem: A CHECK is evaluated on insert and never evaluated again. Not on an upgrade, not on a restart, not on a reindex. The rows that no longer satisfy it remain where they are, and the constraint is still marked as valid in the catalog.
Diagnosis: Count the stored rows that violate the constraint, and then try to insert a row identical to the ones already inside.
Evidence: case 8.
Solution: Drop the constraint, create it again NOT VALID, and run VALIDATE CONSTRAINT, which is what makes the server tell you it is broken. Then you have to decide what happens to the offending rows, and that decision is a business one, not a technical one.
Problem:
The generated column: PostgreSQL requires that the expression of a generated column be immutable, and text comparison is immutable per the catalog. The glibc change is what turns that into a lie. The value was computed once, stored, and after the glibc upgrade, the same expression returns something else.
The materialized view: The same failure one level up. It holds its own copy of the rows, in the order they had when it was built. Nothing re-evaluates it, and nothing warns, so after the upgrade, the view is a snapshot of rules that no longer exist.
Diagnosis:
The generated column: Compare the stored value against the value the expression returns after the glibc upgrade. Nothing guards this one, because no index can answer it; the comparison is a filter over the table, and the two nodes differ on their own.
The materialized view: Compare its stored order against the table’s order under the new rules, and run the query twice: once as it plans, and once with enable_indexscan, enable_bitmapscan, and enable_indexonlyscan off. If the two answers differ, the first one came from the stale index, not from the new rules.
Evidence: case 9.
Solution:
The generated column: On PostgreSQL 17 or newer; recompute it with ALTER TABLE … ALTER COLUMN … SET EXPRESSION AS (…), which rewrites the table. On PostgreSQL 16 or older, drop the column and add it back.
The materialized view: Run REFRESH MATERIALIZED VIEW, but only after the indexes on the tables it reads have been rebuilt. A refresh re-runs the view’s own query, and on a migrated node, that query is still answered by the stale index, so refreshing first stores the old order again and raises nothing.
Problem: The same query, in the same session, returns two different answers depending on the plan. An index scan returns the order in which the index was written, and a sort uses the live rules. There is no corruption here in the storage sense, and that is why nobody opens a ticket until a user notices the list is wrong.
Diagnosis: Run the same ORDER BY twice, once letting the planner use the index and once with index scans turned off.
Evidence: case 10.
Solution: REINDEX fixes the mismatch between the index and the table.
Problem: There is no index involved here, and nothing is broken. The query reads the whole table and still returns a different number. The range w >= ‘wa’ AND w < ‘wb’ covered 775 rows under 2.28, because W was a variant of V, and the words starting with V fell inside it. Under 2.34, it covers 388. The 387 that are left are the ones starting with V, and they are still in the table. What changed is what the range means.
Diagnosis: Ask the same question in both environments.
Evidence: Case A.
Solution: There is no repair, because nothing is broken. Both answers are correct for their glibc version. What you do have to do is review the queries that filter by text ranges, above all the UPDATEs and the DELETEs, because they are going to affect a different number of rows.
Problem: ANALYZE stores a histogram in pg_statistic, a list of values sorted according to the collation rules in force at the time it ran. The planner walks that list with a binary search to estimate how many rows satisfy a predicate. After the glibc change, the list stopped being sorted for the server reading it, so the search returns anything, and the estimate comes out incorrect.
Diagnosis: Compare the planner’s estimates with the rows that are actually present, and verify that the stored statistics match on both nodes.
Evidence: case B.
Solution: ANALYZE, which rebuilds the histogram with the new rules.
After the promotion, every session that touches the collation gets a version mismatch WARNING the first time it uses it.
Evidence: the warning.
That warning has two problems. The first is that it emits the same message for en_US.utf8, whose order did not change at all, because it compares glibc version strings rather than behavior. The second is that there is no equivalent warning for LC_CTYPE, so the characters in case 4 leave no trace anywhere.
The order matters more than the individual commands. The following sequence is the one from the node where the application had already been writing. If nobody wrote, the only step that drops out is the hunt for duplicates, because there are none.
Evidence: the repair.
Do not start with REFRESH VERSION. It silences the warning in every session and repairs nothing.
Reindex, as much as possible. You have to reindex everything that depends on the collation. Ordinary indexes can be rebuilt without complaint because they are rebuilt by reading the table. However, unique indexes and primary keys can only be rebuilt if the application hasn’t written yet; otherwise, they will be refused, and the error identifies the repeated value. That failure is how the database tells you where manual work is left, so you have to find the duplicates with a sequential scan, decide which copy survives, delete the rest, and reindex.
Move the misplaced rows out of the partition. Deleting them from the child partition and reinserting them into the parent ensures that tuple routing places them where they belong.
Revalidate the CHECK constraints. Drop, add back NOT VALID, and then VALIDATE CONSTRAINT, which fails and names the problem. The offending rows have to go somewhere before the constraint can be trusted again.
Recompute the stored values. SET EXPRESSION on the generated column recomputes it row by row, and materialized views need a REFRESH.
Only then set the new version. ALTER COLLATION … REFRESH VERSION and ALTER DATABASE … REFRESH COLLATION VERSION as the last step, followed by ANALYZE.
What does not come back is the data. What happens to the rows that violated their CHECK, and to the duplicate that got in, is a decision a person has to make.
Everything I learned about locales started with Locale data changes, and it is a great document, but it leaves things out. The query it provides for identifying indexes is excellent, but it leaves out things like partial indexes, where the predicate depends on the collation, expression indexes, and so on, as well as everything related to identifying objects that are not indexes.
I’ve created what I believe are more accurate (and probably still not perfect) queries, tested on all today’s PostgreSQL-supported versions (14 to 18):
However, they currently assess locale changes only in columns, not in expressions such as doc->>’name’ on a jsonb column.
A glibc change can happen for many reasons. OS major or minor upgrades, migrating the cluster to a new server, upgrading your OS base images (Containers), or even if the sysadmin modifies the glibc version without changing the OS version.
Reindexing text indexes after a glibc update is solid advice, but it’s only half the job. Indexes are part of the database and can be rebuilt, but everything else is data, such as rows in the wrong partition, rows that violate a constraint are still marked valid, generated values computed under rules that no longer exist, duplicates that got in while a unique index was blind. That has to be manually solved by someone who knows what those values mean.
And case A is worth keeping in mind separately, because it is the only one with no possible repair. A logical migration with dump and restore rebuilds every index according to the new rules and avoids cases 1 to 10, but it does not avoid that one. A freshly restored database, with amcheck saying ok on every index, will still return a different number of rows for a query that filters by a text range.
Addressing all the above steps might require extensive additional downtime during your OS upgrades and cluster migrations. To minimize all of that, the best way is to properly assess and plan. You might consider:
Be careful not to overstress yourself; a proper assessment and planning will keep you safe. Excluding what happened between RHEL7 and RHEL8, collation changes tend to affect very few collations at a time, and your cluster doesn’t necessarily use that. Even if any of your collations are affected, it doesn’t mean character-based data under the affected collation is being used by an index, a materialized view, a constraint, etc. Even if you are using an affected collation and character-based data is referenced by any of the aforementioned objects, you might still not be using the affected characters.
Resources
RELATED POSTS
