The following bug has been logged on the website:
Bug reference: 17923
Logged by: Hans Buschmann
Email address: buschmann@nidsa.net
PostgreSQL version: 15.2
Operating system: Fedora 38 x86-64
Description:
We recently upgraded our production system from FEDORA 37 to FEDORA 38
(x86-64).
This also upgraded the glibc from 2.36 to 2.37.
Quite a period later we observed excessive logging (every 30 seconds) of the
following messages:
LSAM 2023-04-18 17:10:08 CEST 01000 1:> WARNING: database "template1"
has a collation version mismatch
LSAM 2023-04-18 17:10:08 CEST 01000 2:> DETAIL: The database was created
using collation version 2.36, but the operating system provides version
2.37.
LSAM 2023-04-18 17:10:08 CEST 01000 3:> HINT: Rebuild all objects in
this database that use the default collation and run ALTER DATABASE
template1 REFRESH COLLATION VERSION, or build PostgreSQL with the right
library version.
LSAM 2023-04-18 17:10:38 CEST 01000 1:> WARNING: database "postgres" has
a collation version mismatch
LSAM 2023-04-18 17:10:38 CEST 01000 2:> DETAIL: The database was created
using collation version 2.36, but the operating system provides version
2.37.
LSAM 2023-04-18 17:10:38 CEST 01000 3:> 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.
... etc.
Our production database has collation c, so it is not mentioned in the
logs.
Unfortunately the frequent repetition of these messages caused more the
48000 identical entries in about 14 days.
The following questions arise:
1. Why isn't it sufficient to report this message only at server startup ?
The messages are sent to psql too, but only when logging into one of these
mostly unused databases, so it is not observed in a regular routine.
2. Why is this message reported for databases with no objects or similar
cases?
These empty databases have no objects, other databases may have only objects
where a collation version change doesn't matter, such as indexes on
non-string data types like integers. In these cases no warning is
necessary.
3. Is there a real collation change in glibc 2.37 or is it only a version
number mismatch with changes only in other parts of the library?
4. Could the version information be shown in \l output of psql?
5. Wouldn't it make sense to automatically update the collation version
number of such databases (without any collation-related objects) at server
startup?
This resembles the praxis of initdb, which quietly sets the collation
version number at creation time mostly without any user intervention.
Thanks for looking
Hans Buschmann
