Identify the version of a collation from ICU in Postgres

Question

Postgres 10 and later incorporates the International Components for Unicode (ICU) library for text-handling and other internationalization issues.

Changes happen to human languages, such as sorting rules evolving. Therefore collation definitions are versioned in ICU.

➥ How can I identify the version number of a collation?

I know Postgres will report version number as part of a collation version mismatch error message. Is there a way to proactively query for the version rather than wait for an error message?

Answer 1

TL;DR

Check out the pg_collation_actual_version() function.

 

DETAILS

The PostgreSQL documentation for ALTER COLLATION seems to discuss this issue in the "Notes" section:

When using collations provided by the ICU library, the ICU-specific version of the collator is recorded in the system catalog when the collation object is created. When the collation is used, the current version is checked against the recorded version, and a warning is issued when there is a mismatch...

A change in collation definitions can lead to corrupt indexes and other problems because the database system relies on stored objects having a certain sort order. ...

The following query can be used to identify all collations in the current database that need to be refreshed and the objects that depend on them:

SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",  
       pg_describe_object(classid, objid, objsubid) AS "Object"  
  FROM pg_depend d JOIN pg_collation c  
       ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid  
  WHERE c.collversion <> pg_collation_actual_version(c.oid)  
  ORDER BY 1, 2;

You will notice that the given query makes use of the pg_collation_actual_version() function, which is defined as:

pg_collation_actual_version returns the actual version of the collation object as it is currently installed in the operating system. If this is different from the value in pg_collation.collversion, then objects depending on the collation might need to be rebuilt.

Answer 2

To compare collation-versions of a Postgresql-16 DB-cluster with respective OS collation versions

select oid, collname, collversion "orig_version" , pg_collation_actual_version(oid) "curr_version" from pg_collation

---

Example: Debian Upgrade from 11/bullseye to 12/bookworm operating a hot-standby Postgresql-DB

Streaming-replication source on Debian11 bullseye

SQL_prompt=# select oid, collname, collversion "orig_version" , pg_collation_actual_version(oid) "curr_version" from pg_collation ;

oid | collname | orig_version | curr_version -------+------------------------+--------------+-------------- 100 | default | | 950 | C | | 951 | POSIX | | 962 | ucs_basic | | 963 | unicode | 153.14 | 153.14 12344 | C.UTF-8 | | 12350 | en_US.utf8 | 2.31 | 2.31 12352 | en_US | 2.31 | 2.31

Streaming replication target on Debian12/bookworm

SQL_prompt=# select oid, collname, collversion "orig_version" , pg_collation_actual_version(oid) "curr_version" from pg_collation ;

oid | collname | orig_version | curr_version -------+------------------------+--------------+-------------- 100 | default | | 950 | C | | 951 | POSIX | | 962 | ucs_basic | | 963 | unicode | 153.14 | 153.120 12344 | C.UTF-8 | | 12350 | en_US.utf8 | 2.31 | 2.36 12352 | en_US | 2.31 | 2.36

Hence

After promoting the replication target I expect log-entries like:

WARNING: database "mydb" has a collation version mismatch DETAIL: The database was created using collation version 2.31, but the operating system provides version 2.36. HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE mydb REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.