Re: Problems with unique constraints

On Thu, 2023-01-26 at 11:11 +0100, Massimo Ortensi 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 ?

Yes, there were quite a number of such reports.

> How can I find out if similar problems are present ? constraint and
> index looked ok and were used

This is almost certainly caused by an update to the library that provides your
collations. The update must have changed the definition of those collations
and consequently the sort order, corrupting your collations.

These articles describe the problem and ways to a solution:

https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html
https://www.cybertec-postgresql.com/en/icu-collations-against-glibc-2-28-data-corruption/

Yours,
Laurenz Albe