Re: Corruption or wrong results with 14.10?

On Thu, Nov 23, 2023 at 7:34 AM Torsten Förtsch <tfoertsch123(at)gmail(dot)com>
wrote:

> On Thu, Nov 23, 2023 at 2:29 PM Daniel Westermann (DWE) <
> daniel(dot)westermann(at)dbi-services(dot)com> wrote:
>
>> smrdbprod=# select crart_id, chemin, count(*) from smrr_mgr.formula group
>> by crart_id, chemin having count(*) > 1;
>> crart_id | chemin | count
>> -------------+--------+-------
>> 35054630000 | @ | 2
>> 4737310000 | @ | 2
>> 10632380000 | @ | 2
>> 14680880000 | @ | 2
>> 4627230000 | @ | 2
>> 10993780000 | @ | 2
>> ....
>>
>> I think I know what I have to do.
>>
>
> If you have moved the database from a system with glibc <2.28 to >=2.28,
> that could be the reason.
>
> https://wiki.postgresql.org/wiki/Collations
>

Just one note here... the reason can be _any_ operating systems move or
update. It can happen with ICU and it can happen with any version of glibc
(this is easily reproducible and we've seen it happen on production PG
deployments that didn't involve glibc 2.28)

glibc 2.28 has certainly been the most obvious and impactful case, so the
focus is understandable, but there's a bit of a myth that the problem is
only with glibc 2.28 (and not ICU or other glibc versions or data
structures other than indexes)

The only truly safe way to update an operating system under PosgreSQL is
with logical dump/load or logical replication, or continuing to compile and
use the identical older version of ICU from the old OS (if you use ICU). I
think the ICU folks are generally careful enough that it'll be unlikely for
compiler changes and new compiler optimizations to inadvertently change
collation on newer operating systems and build toolchains.

Ironically I just did a detailed talk on this topic at PASS Data Summit
last week, but unfortunately the recording probably won't be released for
awhile. 🙂

-Jeremy

--
http://about.me/jeremy_schneider