pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedICU, locale and collation question
7+ messages / 4 participants
[nested] [flat]
* ICU, locale and collation question
@ 2023-05-08 14:35 Oscar Carlberg <oscar.carlberg@fortnox.se>
0 siblings, 1 reply; 7+ messages in thread
From: Oscar Carlberg @ 2023-05-08 14:35 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hello,
We have a bunch of existing Postgres 10 clusters running on CentOS 7,
which have been initialized (initdb) with these collation options;
-E 'UTF-8'
--lc-collate=sv_SE.UTF-8
--lc-ctype=sv_SE.UTF-8
--lc_monetary=sv_SE.UTF-8
--lc-numeric=sv_SE.UTF-8
--lc-time=sv_SE.UTF-8
--lc-messages=en_US.UTF-8
And createdb were provided with these locale options when setting up
databases:
--lc-collate=sv_SE.UTF-8
--lc-ctype=sv_SE.UTF-8
\l in psql gives:
Name | Owner | Encoding | Collate
| Ctype |
----------------------+--------------------------+----------+-------------+-------------+
test-db | test-user | UTF8 |
sv_SE.UTF-8 | sv_SE.UTF-8 |
We're upgrading the servers using logical replication, and would like to
take the opportunity to switch to ICU rather than relying on glibc, to
avoid future problems with index corruption if using physical
replication between servers with different versions of glibc.
We're trying to figure out the most correct way to configure postgres to
achieve this. Currently we have:
-E 'UTF-8'
--locale-provider=icu
--icu-locale=sv-SE-x-icu
And createdb are provided with locale options:
--lc-collate=C
--lc-ctype=C
\l in psql now gives:
Name | Owner | Encoding | Collate |
Ctype | ICU Locale | Locale Provider |
---------------------+---------------------+----------+-------------+-------------+-------------+-----------------+
test-db | test-user | UTF8 | C |
C | sv-SE-x-icu | icu |
Is this a safe configuration to avoid index corruption, and other
problems, while still being compatible with the previous locale
settings? We have done some testing and it appears ORDER BY does sort
rows according to Swedish localization in the ICU configured test-db.
We're uncertain since this blogpost ->
https://peter.eisentraut.org/blog/2022/09/26/icu-features-in-postgresql-15
mentions that there are still some postgres code relying on libc locale
facilities. Should we set lc-collate and lc-ctype to sv_SE.UTF-8 when
creating databases in addition to the ICU options provided to initdb due
to this? Will we still be safe from glibc related corruption as long as
--locale-provider=icu --icu-locale=sv-SE-x-icu is set?
Best Regards,
Oscar
--
Innehållet i detta e-postmeddelande är konfidentiellt och avsett endast för
adressaten.Varje spridning, kopiering eller utnyttjande av innehållet är
förbjuden utan tillåtelse av avsändaren. Om detta meddelande av misstag
gått till fel adressat vänligen radera det ursprungliga meddelandet och
underrätta avsändaren via e-post
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ICU, locale and collation question
@ 2023-05-08 20:07 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Oscar Carlberg <oscar.carlberg@fortnox.se>
0 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2023-05-08 20:07 UTC (permalink / raw)
To: Oscar Carlberg <oscar.carlberg@fortnox.se>; pgsql-general@lists.postgresql.org
On Mon, 2023-05-08 at 16:35 +0200, Oscar Carlberg wrote:
> We have a bunch of existing Postgres 10 clusters running on CentOS 7,
> which have been initialized (initdb) with these collation options;
>
> -E 'UTF-8'
> --lc-collate=sv_SE.UTF-8
> --lc-ctype=sv_SE.UTF-8
> --lc_monetary=sv_SE.UTF-8
> --lc-numeric=sv_SE.UTF-8
> --lc-time=sv_SE.UTF-8
> --lc-messages=en_US.UTF-8
>
> And createdb were provided with these locale options when setting up
> databases:
> --lc-collate=sv_SE.UTF-8
> --lc-ctype=sv_SE.UTF-8
>
> We're upgrading the servers using logical replication, and would like to
> take the opportunity to switch to ICU rather than relying on glibc, to
> avoid future problems with index corruption if using physical
> replication between servers with different versions of glibc.
>
> We're trying to figure out the most correct way to configure postgres to
> achieve this. Currently we have:
>
> -E 'UTF-8'
> --locale-provider=icu
> --icu-locale=sv-SE-x-icu
>
> And createdb are provided with locale options:
> --lc-collate=C
> --lc-ctype=C
>
> Is this a safe configuration to avoid index corruption, and other
> problems, while still being compatible with the previous locale
> settings?
Yes, that is safe.
But it is not compatible with the old setup when it comes to lc_time,
lc_messages and the others. You should use sv_SE.UTF-8 for these
locale categories.
Note that that won't cause problems with upgrading the C library.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ICU, locale and collation question
@ 2023-05-09 06:54 Oscar Carlberg <oscar.carlberg@fortnox.se>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 7+ messages in thread
From: Oscar Carlberg @ 2023-05-09 06:54 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org
On 5/8/23 22:07, Laurenz Albe wrote:
> Yes, that is safe.
>
> But it is not compatible with the old setup when it comes to lc_time,
> lc_messages and the others. You should use sv_SE.UTF-8 for these
> locale categories.
>
> Note that that won't cause problems with upgrading the C library.
>
> Yours,
> Laurenz Albe
>
Thank you very much for clarifying!
Our initdb setup would then look like this for compatibility;
-E 'UTF-8'
--locale-provider=icu
--icu-locale=sv-SE-x-icu
--lc_monetary=sv_SE.UTF-8
--lc-numeric=sv_SE.UTF-8
--lc-time=sv_SE.UTF-8
--lc-messages=en_US.UTF-8
Should we still provide createdb with --lc-collate=C and --lc-ctype=C,
or should we set those to sv_SE.UTF-8 as well?
Best regards,
Oscar
--
Innehållet i detta e-postmeddelande är konfidentiellt och avsett endast för
adressaten.Varje spridning, kopiering eller utnyttjande av innehållet är
förbjuden utan tillåtelse av avsändaren. Om detta meddelande av misstag
gått till fel adressat vänligen radera det ursprungliga meddelandet och
underrätta avsändaren via e-post
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ICU, locale and collation question
@ 2023-05-09 13:31 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Oscar Carlberg <oscar.carlberg@fortnox.se>
1 sibling, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2023-05-09 13:31 UTC (permalink / raw)
To: Oscar Carlberg <oscar.carlberg@fortnox.se>; pgsql-general@lists.postgresql.org
On Tue, 2023-05-09 at 08:54 +0200, Oscar Carlberg wrote:
> Should we still provide createdb with --lc-collate=C and --lc-ctype=C,
> or should we set those to sv_SE.UTF-8 as well?
That depends on whether you want Swedish or binary sort order
(is "a" < "Z" or not?). The C collation is not good for natural
language sorting, but it is fast and not subject to collation changes.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ICU, locale and collation question
@ 2023-05-09 15:24 Peter Eisentraut <peter.eisentraut@enterprisedb.com>
parent: Oscar Carlberg <oscar.carlberg@fortnox.se>
1 sibling, 1 reply; 7+ messages in thread
From: Peter Eisentraut @ 2023-05-09 15:24 UTC (permalink / raw)
To: Oscar Carlberg <oscar.carlberg@fortnox.se>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org
On 09.05.23 08:54, Oscar Carlberg wrote:
> Our initdb setup would then look like this for compatibility;
> -E 'UTF-8'
> --locale-provider=icu
> --icu-locale=sv-SE-x-icu
> --lc_monetary=sv_SE.UTF-8
> --lc-numeric=sv_SE.UTF-8
> --lc-time=sv_SE.UTF-8
> --lc-messages=en_US.UTF-8
>
> Should we still provide createdb with --lc-collate=C and --lc-ctype=C,
> or should we set those to sv_SE.UTF-8 as well?
You should set those to something other than C. It doesn't matter much
what exactly, so what you have there is fine.
Setting it to C would for example affect the ability of the text search
functionality to detect words containing non-ASCII characters.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ICU, locale and collation question
@ 2023-05-10 05:02 Kirk Wolak <wolakk@gmail.com>
parent: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kirk Wolak @ 2023-05-10 05:02 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; +Cc: Oscar Carlberg <oscar.carlberg@fortnox.se>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org
On Tue, May 9, 2023 at 11:24 AM Peter Eisentraut <
peter.eisentraut@enterprisedb.com> wrote:
> On 09.05.23 08:54, Oscar Carlberg wrote:
> > Our initdb setup would then look like this for compatibility;
> > -E 'UTF-8'
> > --locale-provider=icu
> > --icu-locale=sv-SE-x-icu
> > --lc_monetary=sv_SE.UTF-8
> > --lc-numeric=sv_SE.UTF-8
> > --lc-time=sv_SE.UTF-8
> > --lc-messages=en_US.UTF-8
> >
> > Should we still provide createdb with --lc-collate=C and --lc-ctype=C,
> > or should we set those to sv_SE.UTF-8 as well?
>
> You should set those to something other than C. It doesn't matter much
> what exactly, so what you have there is fine.
>
> Setting it to C would for example affect the ability of the text search
> functionality to detect words containing non-ASCII characters.
>
> Doesn't searching LIKE 'abc%' behave much better for C than others. This
was the driving force for choosing C for us.
[EXPLAIN made it clear that it was range bound until 'abd']
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ICU, locale and collation question
@ 2023-05-10 05:23 Peter Eisentraut <peter.eisentraut@enterprisedb.com>
parent: Kirk Wolak <wolakk@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Peter Eisentraut @ 2023-05-10 05:23 UTC (permalink / raw)
To: Kirk Wolak <wolakk@gmail.com>; +Cc: Oscar Carlberg <oscar.carlberg@fortnox.se>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org
On 10.05.23 07:02, Kirk Wolak wrote:
> On Tue, May 9, 2023 at 11:24 AM Peter Eisentraut
> <peter.eisentraut@enterprisedb.com
> <mailto:peter.eisentraut@enterprisedb.com>> wrote:
>
> On 09.05.23 08:54, Oscar Carlberg wrote:
> > Our initdb setup would then look like this for compatibility;
> > -E 'UTF-8'
> > --locale-provider=icu
> > --icu-locale=sv-SE-x-icu
> > --lc_monetary=sv_SE.UTF-8
> > --lc-numeric=sv_SE.UTF-8
> > --lc-time=sv_SE.UTF-8
> > --lc-messages=en_US.UTF-8
> >
> > Should we still provide createdb with --lc-collate=C and
> --lc-ctype=C,
> > or should we set those to sv_SE.UTF-8 as well?
>
> You should set those to something other than C. It doesn't matter much
> what exactly, so what you have there is fine.
>
> Setting it to C would for example affect the ability of the text search
> functionality to detect words containing non-ASCII characters.
>
> Doesn't searching LIKE 'abc%' behave much better for C than others.
> This was the driving force for choosing C for us.
> [EXPLAIN made it clear that it was range bound until 'abd']
For that use, I would recommend making an index specifically on the
tables you need, instead of switching the whole database.
Also, if you are using the ICU provider for the database, then setting
lc_collation=C wouldn't even affect LIKE optimization, because the ICU
locale would be used.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-05-10 05:23 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-08 14:35 ICU, locale and collation question Oscar Carlberg <oscar.carlberg@fortnox.se>
2023-05-08 20:07 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-05-09 06:54 ` Oscar Carlberg <oscar.carlberg@fortnox.se>
2023-05-09 13:31 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-05-09 15:24 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
2023-05-10 05:02 ` Kirk Wolak <wolakk@gmail.com>
2023-05-10 05:23 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox