On Thu, Nov 23, 2023 at 7:34 AM Torsten Förtsch <tfoertsch123@xxxxxxxxx> wrote:
On Thu, Nov 23, 2023 at 2:29 PM Daniel Westermann (DWE) <daniel.westermann@xxxxxxxxxxxxxxxx> wrote:smrdbprod=# select crart_id, chemin, count(*) from smrr_mgr.formula group by crart_id, chemin having count(*) > 1;crart_id | chemin | count-------------+--------+-------35054630000 | @ | 24737310000 | @ | 210632380000 | @ | 214680880000 | @ | 24627230000 | @ | 210993780000 | @ | 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.
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
