Subject: Re: Move defaults toward ICU in 16?

From: Pavel Stehule <[email-masked]>
Date: Fri, 17 Feb 2023 18:27:13 +0100

pá 17. 2. 2023 v 18:02 odesílatel Jeff Davis <[email-masked]> napsal:

> On Fri, 2023-02-17 at 00:06 -0800, Jeff Davis wrote:
> > On Tue, 2023-02-14 at 09:59 -0800, Andres Freund wrote:
> > > I am saying that pg_upgrade should be able to deal with the
> > > difference. The
> > > details of how to implement that, don't matter that much.
> >
> > To clarify, you're saying that pg_upgrade should simply update
> > pg_database to set the new databases' collation fields equal to that
> > of
> > the old cluster?
>
> Thinking about this more, it's not clear to me if this would be in
> scope for pg_upgrade or not. If pg_upgrade is fixing up the new cluster
> rather than checking for compatibility, why doesn't it just take over
> and do the initdb for the new cluster itself? That would be less
> confusing for users, and avoid some weirdness (like, if you drop the
> database "postgres" on the original, why does it reappear after an
> upgrade?).
>
> Someone might want to do something interesting to the new cluster
> before the upgrade, but it's not clear from the docs what would be both
> useful and safe.
>

Today I tested icu for Czech sorting. It is a little bit slower, but not
too much, but it produces partially different results.

select row_number() over (order by nazev collate "cs-x-icu"), nazev from
obce
except select row_number() over (order by nazev collate "cs_CZ"), nazev
from obce;

returns a not empty set. So minimally for Czech collate, an index rebuild
is necessary

Regards

Pavel

> Regards,
>         Jeff Davis

---

From: Jeff Davis <[email-masked]>
Date: Fri, 17 Feb 2023 12:43:47 -0800

On Fri, 2023-02-17 at 18:27 +0100, Pavel Stehule wrote:
> Today I tested icu for Czech sorting. It is a little bit slower, but
> not too much, but it produces partially different results.

Thank you for trying it.

If it's a significant slowdown, can you please send more information?
ICU version, libc version, and testcase?

> select row_number() over (order by nazev collate "cs-x-icu"), nazev
> from obce 
> except select row_number() over (order by nazev collate "cs_CZ"),
> nazev from obce;
> 
> returns a not empty set. So minimally for Czech collate, an index
> rebuild is necessary

Yes, that's true of any locale change, provider change, or even
provider version change.

-- 
Jeff Davis
PostgreSQL Contributor Team - AWS

---

From: Pavel Stehule <[email-masked]>
Date: Sat, 18 Feb 2023 05:52:54 +0100

> ICU version, libc version, and testcase?

no - this slowdown is not significant - although 1% can looks too much -
but it is just two ms

It looks so libicu has little bit more expensive initialization, but the
execution is little bit faster

But when I try to repeat the measurements, the results are very unstable on
my desktop :-/

SELECT * FROM obce ORDER BY nazev LIMIT 10 // is faster with glibc little
bit
SELECT * FROM obce ORDER BY nazev // is faster with libicu

You can download dataset https://pgsql.cz/files/obce.sql

It is table of municipalities in czech republic (real names) - about 6000
rows

I use fedora 37 - so libicu 71.1, glibc 2.36

Regards

Pavel

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 20 Feb 2023 15:55:45 +0100

> Yes, that's true of any locale change, provider change, or even

I'm confused.  We are not going to try to change existing databases to a 
different collation provider during pg_upgrade, are we?

---

From: Jeff Davis <[email-masked]>
Date: Mon, 20 Feb 2023 11:41:52 -0800

> different collation provider during pg_upgrade, are we?

No, certainly not.

I interpreted Pavel's comments as a comparison of ICU and libc in
general and not specific to this patch. Changing providers obviously
requires an index rebuild to be safe.