Message-ID: <8ed41c50ba6c5322296b869e497c1b5573853b54.camel@posteo.de>
Subject: ICU Collations and Collation Updates
From: Thomas Michael Engelke <thomas.engelke@posteo.de>
To: pgsql-general@lists.postgresql.org
Date: Mon, 14 Apr 2025 08:28:12 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/8ed41c50ba6c5322296b869e497c1b5573853b54.camel%40posteo.de>
Precedence: bulk

Good morning,

long time reader, first time writer.

Where I currently work my colleagues used libc collations before I
arrived. While using libc collations, they stumbled upon the collation
update problem after SLES updates (15.4 to 15.5) (collation version
difference for database and operating system) (paraphrased, don't have
the english message at the hand).

For an easy solution I suggested to switch to ICU collations. While
documenting the problem for older systems I realized that I did not
know enough about the problem to document why ICU collations would
solve this problem.

After reading https://www.postgresql.org/docs/17/collation.html this is
how I understand it:

When initdb creates a cluster the OS available collations are copied to
the database as database objects, listable using

select * from pg_collation;

Now, an OS collation update as part of the OS update will change the
collations available on the OS level, but not the collations that the
database uses.

Is my understanding correct then in that this way the database
collations never change, unless a manual intervention reinitialises the
collations and reindexes the database (or appropriate indexes)? How
does that process compare to other RDBMS?

Are regular collation updates deemed unnecessary for long running
database installations? Or do you people have maintenance workflows
that incorporate regular collation updates to the databases?

Thanks,

Thomas