You are not logged in.

#1 2023-02-12 15:05:17

wolegis
Member
Registered: 2021-05-07
Posts: 6

[SOLVED] PostgreSQL: WARNING: collation "xxxx" has version mismatch

Since latest update of glibc (2.36-7 -> 2.37-2) PostgreSQL annoys me with

WARNING:  collation "xxxx" has version mismatch
DETAIL:  The collation in the database was created using version 2.36, but the operating system provides version 2.37.
HINT:  Rebuild all objects affected by this collation and run ALTER COLLATION pg_catalog."xxxx" REFRESH VERSION, or build PostgreSQL with the right library version.

I have found and carefully read

https://www.postgresql.org/docs/current … TION-NOTES

but I'm still lost. Reading the last part of HINT it might be I have to wait for a fixed PostgreSQL package.

The first part of HINT suggests I have to perform some action on my side. But running the query given in the above mentioned PostgreSQL documentation doesn't yield any result - whatever database user I run this query with.

I'm reluctant to simply run this ALTER COLLATION ... command as the documentation states "Note that this does not actually check whether all affected objects have been rebuilt correctly." So apparently this just makes the warning go away without actually fixing the root issue.

Any help and hints appreciated. Many thanks in advance.

Last edited by wolegis (2023-02-13 18:47:15)

Offline

#2 2023-02-12 15:13:34

loqs
Member
Registered: 2014-03-06
Posts: 17,196

Re: [SOLVED] PostgreSQL: WARNING: collation "xxxx" has version mismatch

Offline

#3 2023-02-12 15:20:51

wolegis
Member
Registered: 2021-05-07
Posts: 6

Re: [SOLVED] PostgreSQL: WARNING: collation "xxxx" has version mismatch

I have read this bug report. It essentially states that the issue is fixed with PostgreSQL version 15.1-3. This is (at least my case) wrong, as I have PostgreSQL 15.1-3 installed.

Offline

#4 2023-02-12 15:25:42

loqs
Member
Registered: 2014-03-06
Posts: 17,196

Re: [SOLVED] PostgreSQL: WARNING: collation "xxxx" has version mismatch

From the .BUILDINFO of postgresql 15.1-3

installed = glibc-2.37-2-x86_64

Meaning postgresql 15.1-3 was definitely built using glibc 2.37.  Did you restart the server after the package update?

Offline

#5 2023-02-12 15:27:14

wolegis
Member
Registered: 2021-05-07
Posts: 6

Re: [SOLVED] PostgreSQL: WARNING: collation "xxxx" has version mismatch

> Meaning postgresql 15.1-3 was definitely built using glibc 2.37.  Did you restart the server after the package update?

Sure.

Offline

#6 2023-02-12 15:42:05

loqs
Member
Registered: 2014-03-06
Posts: 17,196

Re: [SOLVED] PostgreSQL: WARNING: collation "xxxx" has version mismatch

Did the mentioned query locate any objects that need updating?

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;

After running REINDEX on any databse that has objects using the old collation,  does the query still return matches?
After that you can run the  ALTER COLLATION / ALTER DATABASE to change the collation recorded in the database which as you noted will stop the message being produced.
Edit:
I am wondering if adding postgresql to the glibc rebuild list was warranted.  The check for glibc version is at runtime.  The collation version is recorded at database / table / object creation time.  What is decided at compile time?
https://www.postgresql.org/docs/15/coll … N-MANAGING

Last edited by loqs (2023-02-12 16:46:21)

Offline

#7 2023-02-13 14:05:04

wolegis
Member
Registered: 2021-05-07
Posts: 6

Re: [SOLVED] PostgreSQL: WARNING: collation "xxxx" has version mismatch

loqs wrote:

Did the mentioned query locate any objects that need updating?

As I have already said: The query returned no results - regardless if run as db-user postgres or against one of my two application databases (with the corresponding db-user).

loqs wrote:

After running REINDEX on any databse that has objects using the old collation,  does the query still return matches?

Probably not. smile

loqs wrote:

After that you can run the  ALTER COLLATION / ALTER DATABASE to change the collation recorded in the database which as you noted will stop the message being produced.

I ran

alter collation pg_catalog."de_DE.utf8" refresh version;
alter collation pg_catalog."de_DE" refresh version;
alter collation pg_catalog."en_US.utf8" refresh version;
alter collation pg_catalog."en_US" refresh version;

as I had observed that for these collations the version was still 2.36. This had no recognisable effect. My journal was still flooded with warnings by PostgreSQL.

Additionally running

alter database xxxx refresh collation version;

for all databases previously named in the warnings (postgres, template1 and two application specific databases) finally made the warnings go away.

I don't feel particularly comfortable with this situation. I don't know what I was actually doing with the above mentioned statements. The concept of collation versioning is still obscure to me. What caused the warnings in the first place after having upgraded glibc (2.36 => 2.37) and postgresql (15.1-1 => 15.1-3) during one 'pacman -Syu'?

Offline

#8 2023-02-13 16:36:01

loqs
Member
Registered: 2014-03-06
Posts: 17,196

Re: [SOLVED] PostgreSQL: WARNING: collation "xxxx" has version mismatch

wolegis wrote:

What caused the warnings in the first place after having upgraded glibc (2.36 => 2.37) and postgresql (15.1-1 => 15.1-3) during one 'pacman -Syu'?

https://www.postgresql.org/docs/15/coll … N-MANAGING specifically 24.2.2.2. Predefined Collations.
initdb added some predefined collations based on the locales enabled on the system at that time.  In your case it appears those collations were never used.  The glibc version at that time is set as the collation's version in both the collation and database.  glibc is updated and postgresql detects the version mismatch between glibc version and collation version recorded in the database.
Edit:
I suspect you could simply the process for future glibc updates by removing the collations you updated that proved to be unused.  You would then only need to run ALTER DATABASE.

Last edited by loqs (2023-02-13 17:08:50)

Offline

Board footer

Powered by FluxBB