Confirmed, the local is the same but sorting changed.
Thanks
Il 26/01/2023 13:39, Erik Wienhold ha scritto:
On 26/01/2023 12:52 CET Massimo Ortensi <mortensi@xxxxxxxxxxxxxxx> wrote:
we had OS upgrades, but with the same locale.
Which OS? Locale may still be the same but collation information may have
changed, resulting in a different sort order.
https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html
Has libc version changed with the OS upgrades?
Is there any chance to avoid rebuilding of all indexes/constraints by checking
the correctness of indexes ?
Extension amcheck (functions bt_index_check and bt_index_parent_check) can
verify that btree entries for text are in lexical order.
https://www.postgresql.org/docs/current/amcheck.html
Il 26/01/2023 11:56, Erik Wienhold ha scritto:
On 26/01/2023 11:11 CET Massimo Ortensi <mortensi@xxxxxxxxxxxxxxx> wrote:
I had troubles with some unique constraints recently.
They simply didn't work, not seeing some records (select found only 47
records with specific values instead of 65, insert could create
duplicate records).
The constraint were on multiple columns. Drop constraint and create
constraint fixed the problem.
This happened on two servers :
- a version 10 server, where an abnormal poweroff happened 6 months
ago, found 3 tables with the problem throughout last 6 months.
- a test server, found the problem on 1 table after upgrading version 10
to 14
Anythng similar happened to anybody ?
How can I find out if similar problems are present ? constraint and
index looked ok and were used
Are the indexes on string types? Changes to the locale data is one cause:
https://www.postgresql.org/message-id/328626.1647362495@xxxxxxxxxxxxx
--
Erik
