From: Erik Wienhold <ewie@ewie.name>
To: Massimo Ortensi <mortensi@unimaticaspa.it>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Problems with unique constraints
Date: Thu, 26 Jan 2023 11:56:19 +0100 (CET)
Message-ID: <1968679708.705665.1674730579540@office.mailbox.org> (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 26/01/2023 11:11 CET Massimo Ortensi <mortensi@unimaticaspa.it> 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@sss.pgh.pa.us
--
Erik
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: ewie@ewie.name, mortensi@unimaticaspa.it, pgsql-admin@lists.postgresql.org
Subject: Re: Problems with unique constraints
In-Reply-To: <1968679708.705665.1674730579540@office.mailbox.org>
* 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