public inbox for [email protected]help / color / mirror / Atom feed
COLLATION update in 13.1 13+ messages / 6 participants [nested] [flat]
* COLLATION update in 13.1 @ 2025-02-24 10:08 Matthias Apitz <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Matthias Apitz @ 2025-02-24 10:08 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: COLLATION update in 13.1 @ 2025-02-24 10:32 Jeremy Schneider <[email protected]> parent: Matthias Apitz <[email protected]> 0 siblings, 2 replies; 13+ messages in thread From: Jeremy Schneider @ 2025-02-24 10:32 UTC (permalink / raw) To: Matthias Apitz <[email protected]>; +Cc: [email protected] On Mon, 24 Feb 2025 11:08:43 +0100 Matthias Apitz <[email protected]> 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 ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: COLLATION update in 13.1 @ 2025-02-24 11:32 Matthias Apitz <[email protected]> parent: Jeremy Schneider <[email protected]> 1 sibling, 1 reply; 13+ messages in thread From: Matthias Apitz @ 2025-02-24 11:32 UTC (permalink / raw) To: Jeremy Schneider <[email protected]>; +Cc: [email protected] 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 On Mon, Feb 24, 2025 at 11:32 AM Jeremy Schneider <[email protected]> wrote: > On Mon, 24 Feb 2025 11:08:43 +0100 > Matthias Apitz <[email protected]> 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 > ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: COLLATION update in 13.1 @ 2025-02-24 11:35 Dominique Devienne <[email protected]> parent: Matthias Apitz <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Dominique Devienne @ 2025-02-24 11:35 UTC (permalink / raw) To: Matthias Apitz <[email protected]>; +Cc: Jeremy Schneider <[email protected]>; [email protected] On Mon, Feb 24, 2025 at 12:33 PM Matthias Apitz <[email protected]> wrote: > 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? > Missing quotes. ALTER COLLATION "de_DE.utf8" REFRESH VERSION; ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: COLLATION update in 13.1 @ 2025-02-24 11:41 Laurenz Albe <[email protected]> parent: Jeremy Schneider <[email protected]> 1 sibling, 1 reply; 13+ messages in thread From: Laurenz Albe @ 2025-02-24 11:41 UTC (permalink / raw) To: Jeremy Schneider <[email protected]>; Matthias Apitz <[email protected]>; +Cc: [email protected] On Mon, 2025-02-24 at 02:32 -0800, Jeremy Schneider wrote: > On Mon, 24 Feb 2025 11:08:43 +0100 > Matthias Apitz <[email protected]> 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.) 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. ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: COLLATION update in 13.1 @ 2025-02-24 11:53 Matthias Apitz <[email protected]> parent: Dominique Devienne <[email protected]> 0 siblings, 2 replies; 13+ messages in thread From: Matthias Apitz @ 2025-02-24 11:53 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: Jeremy Schneider <[email protected]>; [email protected] 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 protected]> wrote: > On Mon, Feb 24, 2025 at 12:33 PM Matthias Apitz <[email protected]> > wrote: > >> 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? >> > > Missing quotes. ALTER COLLATION "de_DE.utf8" REFRESH VERSION; > ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: COLLATION update in 13.1 @ 2025-02-24 11:57 Laurenz Albe <[email protected]> parent: Matthias Apitz <[email protected]> 1 sibling, 1 reply; 13+ messages in thread From: Laurenz Albe @ 2025-02-24 11:57 UTC (permalink / raw) To: Matthias Apitz <[email protected]>; Dominique Devienne <[email protected]>; +Cc: Jeremy Schneider <[email protected]>; [email protected] 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 > sisis=# REINDEX (VERBOSE) DATABASE sisis; > sisis=# ALTER COLLATION "de_DE.utf8" REFRESH VERSION; > ALTER COLLATION > > Correct? 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* 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. ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: COLLATION update in 13.1 @ 2025-02-24 12:07 Matthias Apitz <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Matthias Apitz @ 2025-02-24 12:07 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Dominique Devienne <[email protected]>; Jeremy Schneider <[email protected]>; [email protected] Thanks. I did \l before which gives: 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 | ... 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 sisis=# ALTER COLLATION "de_DE.utf8" REFRESH VERSION; NOTICE: version has not changed ALTER COLLATION ? On Mon, Feb 24, 2025 at 12:57 PM Laurenz Albe <[email protected]> wrote: > 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 > > sisis=# REINDEX (VERBOSE) DATABASE sisis; > > sisis=# ALTER COLLATION "de_DE.utf8" REFRESH VERSION; > > ALTER COLLATION > > > > Correct? > > 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* > 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. > ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: COLLATION update in 13.1 @ 2025-02-24 14:59 Ron Johnson <[email protected]> parent: Matthias Apitz <[email protected]> 1 sibling, 0 replies; 13+ messages in thread From: Ron Johnson @ 2025-02-24 14:59 UTC (permalink / raw) To: pgsql-general On Mon, Feb 24, 2025 at 6:53 AM Matthias Apitz <[email protected]> wrote: [snip] > pgsql -Usisis sisis > > sisis=# REINDEX (VERBOSE) DATABASE sisis; > sisis=# ALTER COLLATION "de_DE.utf8" REFRESH VERSION; > ALTER COLLATION > > Correct? > > 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! ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: COLLATION update in 13.1 @ 2025-02-24 15:10 Laurenz Albe <[email protected]> parent: Matthias Apitz <[email protected]> 0 siblings, 0 replies; 13+ messages in thread From: Laurenz Albe @ 2025-02-24 15:10 UTC (permalink / raw) To: Matthias Apitz <[email protected]>; +Cc: Dominique Devienne <[email protected]>; Jeremy Schneider <[email protected]>; [email protected] On Mon, 2025-02-24 at 13:07 +0100, Matthias Apitz wrote: > Thanks. I did \l before which gives: > > 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 | > ... > > 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 > > sisis=# ALTER COLLATION "de_DE.utf8" REFRESH VERSION; > NOTICE: version has not changed > ALTER COLLATION 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* 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. ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: COLLATION update in 13.1 @ 2025-03-24 05:57 Matthias Apitz <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Matthias Apitz @ 2025-03-24 05:57 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: [email protected] El día lunes, febrero 24, 2025 a las 12:41:05p. m. +0100, Laurenz Albe escribió: > 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 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 protected], 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. ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: COLLATION update in 13.1 @ 2025-03-24 06:27 Laurenz Albe <[email protected]> parent: Matthias Apitz <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Laurenz Albe @ 2025-03-24 06:27 UTC (permalink / raw) To: Matthias Apitz <[email protected]>; +Cc: [email protected] On Mon, 2025-03-24 at 06:57 +0100, Matthias Apitz wrote: > El día lunes, febrero 24, 2025 a las 12:41:05p. m. +0100, Laurenz Albe escribió: > > 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. > > 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? 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 ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: COLLATION update in 13.1 @ 2025-03-28 06:37 Matthias Apitz <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 0 replies; 13+ messages in thread From: Matthias Apitz @ 2025-03-28 06:37 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: [email protected] El día lunes, marzo 24, 2025 a las 07:27:21a. m. +0100, Laurenz Albe escribió: > On Mon, 2025-03-24 at 06:57 +0100, Matthias Apitz wrote: > > El día lunes, febrero 24, 2025 a las 12:41:05p. m. +0100, Laurenz Albe escribió: > > > 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. > > > > 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? > > 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 -- Matthias Apitz, ✉ [email protected], 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. ^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2025-03-28 06:37 UTC | newest] Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-02-24 10:08 COLLATION update in 13.1 Matthias Apitz <[email protected]> 2025-02-24 10:32 ` Jeremy Schneider <[email protected]> 2025-02-24 11:32 ` Matthias Apitz <[email protected]> 2025-02-24 11:35 ` Dominique Devienne <[email protected]> 2025-02-24 11:53 ` Matthias Apitz <[email protected]> 2025-02-24 11:57 ` Laurenz Albe <[email protected]> 2025-02-24 12:07 ` Matthias Apitz <[email protected]> 2025-02-24 15:10 ` Laurenz Albe <[email protected]> 2025-02-24 14:59 ` Ron Johnson <[email protected]> 2025-02-24 11:41 ` Laurenz Albe <[email protected]> 2025-03-24 05:57 ` Matthias Apitz <[email protected]> 2025-03-24 06:27 ` Laurenz Albe <[email protected]> 2025-03-28 06:37 ` Matthias Apitz <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox