Date: Thu, 26 Jan 2023 11:11:09 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101
 Thunderbird/102.6.1
To: pgsql-admin@lists.postgresql.org
References: 
 <CAK+uD7heWzrAjdRcSYq-z=crnb5mEs8JgsfGYtrK+vb0yrjeKg@mail.gmail.com>
 <b977effa32ce6e3088469871e294bea5d4d513b1.camel@cybertec.at>
From: Massimo Ortensi <mortensi@unimaticaspa.it>
Subject: Problems with unique constraints
In-Reply-To: <b977effa32ce6e3088469871e294bea5d4d513b1.camel@cybertec.at>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Message-Id: <1pKzDp-00CgfG-JH@unimaticaspa.it>
Archived-At: 
 <https://www.postgresql.org/message-id/1pKzDp-00CgfG-JH%40unimaticaspa.it>
Precedence: bulk

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