From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Massimo Ortensi <mortensi@unimaticaspa.it>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Problems with unique constraints
Date: Thu, 26 Jan 2023 21:44:35 +0100
Message-ID: <32372394ccb0bf7458348973475968fa26de076f.camel@cybertec.at> (raw)
In-Reply-To: <1pKzDp-00CgfG-JH@unimaticaspa.it>
References: <CAK+uD7heWzrAjdRcSYq-z=crnb5mEs8JgsfGYtrK+vb0yrjeKg@mail.gmail.com>
<b977effa32ce6e3088469871e294bea5d4d513b1.camel@cybertec.at>
<1pKzDp-00CgfG-JH@unimaticaspa.it>
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.htmlhttps://www.cybertec-postgresql.com/en/icu-collations-against-glibc-2-28-data-corruption/
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, mortensi@unimaticaspa.it, pgsql-admin@lists.postgresql.org
Subject: Re: Problems with unique constraints
In-Reply-To: <32372394ccb0bf7458348973475968fa26de076f.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox