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.