Subject: COLLATION update in 13.1

From: Matthias Apitz <[email-masked]>
Date: Mon, 24 Feb 2025 11:08:43 +0100

Hello,

When the Linux OS is updated, for example from SLES 15 SP5 to SP6, the
version of the glibc is sometimes updated, for example from 2.31 to 2.38.
For existing databases this gives on SQL a warning as:

user@rechner: $SC_SQL -Usisis sisis
WARNING:  database "sisis" has a collation version mismatch
DETAIL:  The database was created using collation version 2.31, but
the operating system provides version 2.38.
HINT:  Rebuild all objects in this database that use the default
collation and run
ALTER DATABASE sisis REFRESH COLLATION VERSION, or build PostgreSQL
with the right library version.
...

This HINT works fine on 15.x and 16.x server versions.

On 13.1 it gives an SQL ERROR:

sisis=# ALTER DATABASE sisis REFRESH COLLATION VERSION;
ERROR:  syntax error at or near "REFRESH"
LINE 1: ALTER DATABASE sisis REFRESH COLLATION VERSION;

What is the procedure on 13.1 to bring the external (glibc) version in sync
with. the used version in the PostgreSQL database?

Thanks

matthias

---

From: Jeremy Schneider <[email-masked]>
Date: Mon, 24 Feb 2025 02:32:25 -0800

On Mon, 24 Feb 2025 11:08:43 +0100
Matthias Apitz <[email-masked]> wrote:

> 
> What is the procedure on 13.1 to bring the external (glibc) version
> in sync with. the used version in the PostgreSQL database?

If I recall correctly, between versions 10 and 14 you need to use ALTER
COLLATION name REFRESH VERSION on every collation where there's a
version mismatch. (This can happen with ICU collations since version 10,
and with glibc collations since version 13.)

I only know of a small number of changes between glibc versions 2.31 and
2.38 ~ I suspect you already know to look out for glibc 2.28 which was
the crazy one. (SLE15 Service Pack 3.) Most databases would be
corrupted by that update, and running "refresh version" would simply
stop the warnings without fixing the corruption.

-Jeremy

---

From: Matthias Apitz <[email-masked]>
Date: Mon, 24 Feb 2025 12:32:57 +0100

Thanks for your hint, Jeremy. But this does not work either:

postgres=# SELECT collname, collversion FROM pg_collation where collname =
'de_DE.utf8';
  collname  | collversion
------------+-------------
 de_DE.utf8 | 2.38
(1 row)

postgres=# ALTER COLLATION de_DE.utf8 REFRESH VERSION;
ERROR:  schema "de_de" does not exist

What do I wrong?

Matthia

---

From: Dominique Devienne <[email-masked]>
Date: Mon, 24 Feb 2025 12:35:40 +0100

> postgres=# SELECT collname, collversion FROM pg_collation where collname =

Missing quotes.  ALTER COLLATION "de_DE.utf8" REFRESH VERSION;

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 24 Feb 2025 12:41:05 +0100

> and with glibc collations since version 13.)

Perhaps I need not say that, but ALTER COLLATION ... REFRESH VERSION only
makes the warning disappear.

If you want to avoid data corruption, rebuild all indexes on strings,
then make the warning disappear.

Yours,
Laurenz Albe

-- 

*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den 
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat 
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte, 
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder 
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich 
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.

*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are 
confidential and may be privileged or otherwise protected from disclosure 
and solely for the use of the person(s) or entity to whom it is intended. 
If you have received this message in error and are not the intended 
recipient, please notify the sender immediately and delete this message and 
any attachment from your system. If you are not the intended recipient, be 
advised that any use of this message is prohibited and may be unlawful, and 
you must not copy this message or attachment or disclose the contents to 
any other person.

---

From: Matthias Apitz <[email-masked]>
Date: Mon, 24 Feb 2025 12:53:32 +0100

Thanks. I tried a lot of combinations. Based on the output of \l

                                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale
| Locale Provider |   Access privileges
------------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 bar        | foo      | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 |
 | libc            |
 customers  | sisis    | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 |
 | libc            |
...

postgres=# ALTER COLLATION "de_DE.UTF8" REFRESH VERSION;
ERROR:  collation "de_DE.UTF8" for encoding "UTF8" does not exist

yours (Dominique) seems to work:

postgres=# ALTER COLLATION "de_DE.utf8" REFRESH VERSION;
NOTICE:  version has not changed
ALTER COLLATION

If I understand the other reply from Laurenz Albe right, the correct
procedure would be:

pgsql -Usisis sisis

sisis=# REINDEX (VERBOSE) DATABASE sisis;
sisis=# ALTER COLLATION "de_DE.utf8" REFRESH VERSION;
ALTER COLLATION

Correct?

On Mon, Feb 24, 2025 at 12:35 PM Dominique Devienne <[email-masked]>
wrote:

>> postgres=# SELECT collname, collversion FROM pg_collation where collname
>> = 'de_DE.utf8';
>>   collname  | collversion

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 24 Feb 2025 12:57:39 +0100

On Mon, 2025-02-24 at 12:53 +0100, Matthias Apitz wrote:
> If I understand the other reply from Laurenz Albe right, the correct procedure would be:
> 
> pgsql -Usisis sisis

That REINDEX is certainly correct, even though it rebuilds way more
indexes than necessary.

If the ALTER COLLATION statement is correct or not depends on the
collation you are using.  You could look at "\l" and "pg_collation"
to get the name right.

Yours,
Laurenz Albe

-- 

*E-Mail Disclaimer*

---

From: Matthias Apitz <[email-masked]>
Date: Mon, 24 Feb 2025 13:07:06 +0100

Thanks. I did \l before which gives:

                                                  List of databases

But why the ALTER statement needs the spelling different as the output of
\l :

sisis=# ALTER COLLATION "de_DE.UTF-8" REFRESH VERSION;
ERROR:  collation "de_DE.UTF-8" for encoding "UTF8" does not exist

?

---

From: Ron Johnson <[email-masked]>
Date: Mon, 24 Feb 2025 09:59:00 -0500

On Mon, Feb 24, 2025 at 6:53 AM Matthias Apitz <[email-masked]>
wrote:
[snip]

> sisis=# ALTER COLLATION "de_DE.utf8" REFRESH VERSION;
Just reindex those with text columns.

create or replace view dba.all_indices_types as
    select tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname as
table_name
            , ndcl.relname as index_name
            , array_agg(ty.typname order by att.attnum) as index_types
    from pg_class ndcl
        inner join pg_index nd
            on (ndcl.oid = nd.indexrelid and ndcl.relkind = 'i')
        inner join pg_class tbcl
            on (nd.indrelid = tbcl.oid and tbcl.relkind = 'r')
        inner join pg_attribute att
            on att.attrelid = nd.indexrelid
        inner join pg_type ty
            on att.atttypid = ty.oid
    where tbcl.relnamespace::regnamespace::text != 'pg_catalog'
    group by tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname
            , ndcl.relname
    order by 1, 2;
select * from dba.all_indices_types where index_types &&
'{"text","varchar","char"}';

(This view might not handle indices on the parents of declared-partition
tables.)

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 24 Feb 2025 16:10:06 +0100

On Mon, 2025-02-24 at 13:07 +0100, Matthias Apitz wrote:
>     Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
>  bar        | foo      | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 |            | libc            |
>  customers  | sisis    | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 |            | libc            |
> ...
> 
> But why the ALTER statement needs the spelling different as the output of \l :
> 

Yes, that is annoying.  You can use other collation names in CREATE DATABASE
than the ones in pg_collation.  You have to choose the name that is used in
pg_collation, which is probably "de_DE.utf8".

Really, they refer to the same collation.

Yours,
Laurenz Albe

-- 

*E-Mail Disclaimer*

---

From: Matthias Apitz <[email-masked]>
Date: Mon, 24 Mar 2025 06:57:18 +0100

> then make the warning disappear.

One last question related to ALTER COLLATION ... In our clusters are also
the databases 'postgres', 'template0' and 'template1'. The latter we
never us and we CREATE new databases from 'template0'. Anyway, does it
make sense to ALTER COLLATION in these databases as well?

Thanks

	matthias

-- 
Matthias Apitz, ✉ [email-masked], http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Annalena Baerbock: "We are fighting a war against Russia ..." (25.1.2023)

I, Matthias, I am not at war with Russia.
Я не воюю с Россией.
Ich bin nicht im Krieg mit Russland.

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 24 Mar 2025 07:27:21 +0100

> make sense to ALTER COLLATION in these databases as well?

I would say so, yes.  At least on the template you are using for new databases.

By the way, your signature makes Google move your e-mail to "spam".  Just saying.

Yours,
Laurenz Albe

---

From: Matthias Apitz <[email-masked]>
Date: Fri, 28 Mar 2025 07:37:49 +0100

> I would say so, yes.  At least on the template you are using for new databases.

Thanks for your answer. Just for the record:

$ psql -Upostgres template0
Passwort für Benutzer postgres:
psql: FATAL:  database "template0" is not currently accepting connections
$

With 'template1' and 'postgres' it works fine.

	matthias