The following bug has been logged on the website:
Bug reference: 18029
Logged by: Christian Roser
Email address: christian-roser@web.de
PostgreSQL version: 15.3
Operating system: Debian
Description:
Hi,
During the upgrade from Debian 11 to 12 I stumbled across following
Warning:
WARNING: database "postgres" 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 postgres REFRESH COLLATION VERSION, or build
PostgreSQL with the right library version.
I'm well aware about the issues that came with the collation changes in
glibc 2.28 so this got my immediate full attention. After checking the code
a found that for me, still using C locale provider, it's informing about a
change in glibc version and not necessarily a change in a collation. I
understand that it's nearly impossible to determine if there were some
collation changes in any of the glibc versions between the one stored in
datcollversion and pg_database_collation_actual_version. Anyway, I find the
hint a little bit misleading, as it generally advises to rebuild all objects
within the database. A version change in glibc might indicate that there are
changes to the collation but this is not necessarily the case.
It would be helpful if the warning would be a little more precise and give
different output depending on the locale provider or generally advise to
check for collation changes. Rebuilding database objects is not wrong and
you should be on the safe side doing so but it's only necessary if there are
actual collation changes.
regards
Christian