Message-ID: <32372394ccb0bf7458348973475968fa26de076f.camel@cybertec.at>
Subject: Re: Problems with unique constraints
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Massimo Ortensi <mortensi@unimaticaspa.it>,
	pgsql-admin@lists.postgresql.org
Date: Thu, 26 Jan 2023 21:44:35 +0100
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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.3 (3.46.3-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/32372394ccb0bf7458348973475968fa26de076f.camel%40cybertec.at>
Precedence: bulk

On Thu, 2023-01-26 at 11:11 +0100, Massimo Ortensi wrote:
> I had troubles with some unique constraints recently.
>=20
> They simply didn't work, not seeing some records (select found only 47=
=20
> records with specific values instead of 65, insert could create=20
> duplicate records).
>=20
> The constraint were on multiple columns. Drop constraint and create=20
> constraint fixed the problem.
>=20
> This happened on two servers :
>=20
> - a version 10 server,=C2=A0 where an abnormal poweroff happened 6 months=
=20
> ago, found 3 tables with the problem throughout last 6 months.
>=20
> - a test server, found the problem on 1 table after upgrading version 10=
=20
> to 14
>=20
>=20
> 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=20
> index looked ok and were used

This is almost certainly caused by an update to the library that provides y=
our
collations.  The update must have changed the definition of those collation=
s
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-da=
ta-corruption/

Yours,
Laurenz Albe