Skip to main content

PG.L5.COLLATION_VERSION_MISMATCH — indexes sorted by rules the server no longer uses

  • Category: safety
  • Level: 5
  • Confidence: deterministic
  • Downtime class: blocking
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18

A B-tree index on a text column stores its entries in the collation's sort order.

Change the collation — a glibc upgrade, a new ICU, an OS image bump — and that order changes underneath the index. The index is now sorted by rules the server no longer applies, so:

  • an equality lookup can miss a row that is present,
  • a unique constraint can stop rejecting a duplicate.

Nothing raises. The data is not corrupt in any way a checksum would notice; it is merely indexed to a different set of rules.

PostgreSQL records the collation version at creation precisely so this is detectable, and warns once at startup. A warning in a log nobody reads is what this rule turns into a finding.

The order of the fix is the whole difficulty​

-- refreshing FIRST, while the indexes are still sorted the old way:
ALTER DATABASE shop REFRESH COLLATION VERSION;

That records the new version while the indexes are unchanged. It silences PostgreSQL's own warning and leaves the problem — which is worse than doing nothing, because the evidence is now gone.

-- rebuild first, then record the new version:
REINDEX DATABASE shop;
ALTER DATABASE shop REFRESH COLLATION VERSION;

Two kinds of collation, two commands​

The fix material names a different object for each kind of collation this rule reads, and neither command accepts the other's object:

The finding namesRecord the new version withThe indexes to rebuild are found through
the database's own collationALTER DATABASE {{database}} REFRESH COLLATION VERSION, with {{database}} already filled in as the database the finding names, quoted: "shop"pg_index.indcollation: every index with a key column in the database's default collation
any other collationALTER COLLATION {{collation}} REFRESH VERSION, with {{collation}} already filled in as the name the finding carries, quoted: "pg_catalog"."de-DE-x-icu"pg_depend, with that name resolved through regcollation

The first step differs because PostgreSQL records no dependency on the default collation: it is pinned, so a query through pg_depend lists nothing for the collation almost every index is built on. The step lists the indexes rather than reindexing the whole database, so each can be rebuilt CONCURRENTLY, one statement at a time.

Three ways to be unable to answer, and none of them is a pass​

Neither side reports a version. Some providers report none on some platforms — measured on PostgreSQL 18 on macOS, the libc provider reports none at all. Drift is undetectable there in principle, which is not the same as absent. Reported as undetermined, named as a limit of the provider so nobody goes hunting for a missing privilege.

Only one side reports. The two halves mean opposite things. A recorded version with no current one means the provider stopped reporting — an OS change already happened and the evidence of what it was is gone. A current version with nothing recorded means the object predates version tracking. Each gets its own sentence.

Both present and different. The finding.

Why this finding carries a downtime class when the settings rules do not​

The field prices an operation. A server-baseline rule describes a configuration and prices nothing, which is why those carry none.

Here there genuinely is one: the correction is REINDEX, which takes a lock and rebuilds the index. A deploy pipeline reading the field needs that answer before it schedules the work.

The value is about the fix, not the finding — producing the finding costs nothing at all.

Why the comparison is a plain string equality​

PostgreSQL's own drift check is string equality, and the providers report incomparable formats: ICU says 153.128.47, the builtin provider says 1, glibc says 2.36.

Parsing those into an ordering would invent one across three incompatible schemes — and would make 2.9 newer than 2.36, which is how a real drift gets reported as fine.

What is read​

The database's own collation, which is what almost every index in an ordinary schema is actually built on, every collation created outside the system schemas, and every collation in pg_catalog that a column, an index or a constraint uses. Both are the same kind of fact and are judged by one rule.

pg_catalog is where PostgreSQL puts the operating system's and ICU's collations, so a column declared COLLATE "de-DE-x-icu" depends on one of them. The rest of that schema is not read: it holds about a thousand collations, and a finding about one nobody uses would be noise. C, POSIX and the builtin collations are pinned, so PostgreSQL records no dependency on them, and they are not read either.

Sources​

The fix material this rule carries​

A finding from this rule carries machine-readable fix material, using this sequence:

  • reindex_before_refresh — Rebuild the indexes FIRST, and only then tell the server the recorded version is current.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.