Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pKzvl-0005yx-TT for pgsql-admin@arkaria.postgresql.org; Thu, 26 Jan 2023 10:56:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pKzvk-00005L-A4 for pgsql-admin@arkaria.postgresql.org; Thu, 26 Jan 2023 10:56:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pKzvj-000050-VR for pgsql-admin@lists.postgresql.org; Thu, 26 Jan 2023 10:56:31 +0000 Received: from mout-u-107.mailbox.org ([2001:67c:2050:101:465::107]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pKzvd-0000U6-C5 for pgsql-admin@lists.postgresql.org; Thu, 26 Jan 2023 10:56:31 +0000 Received: from smtp102.mailbox.org (smtp102.mailbox.org [IPv6:2001:67c:2050:b231:465::102]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-107.mailbox.org (Postfix) with ESMTPS id 4P2d1J0dJyz9sSB; Thu, 26 Jan 2023 11:56:20 +0100 (CET) Date: Thu, 26 Jan 2023 11:56:19 +0100 (CET) From: Erik Wienhold To: Massimo Ortensi , pgsql-admin@lists.postgresql.org Message-ID: <1968679708.705665.1674730579540@office.mailbox.org> In-Reply-To: <1pKzDp-00CgfG-JH@unimaticaspa.it> References: <1pKzDp-00CgfG-JH@unimaticaspa.it> Subject: Re: Problems with unique constraints MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4P2d1J0dJyz9sSB List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 26/01/2023 11:11 CET 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,=C2=A0 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