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 1ooZZn-0007Ne-HW for pgsql-admin@arkaria.postgresql.org; Sat, 29 Oct 2022 00:19:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ooZYm-0004lp-CC for pgsql-admin@arkaria.postgresql.org; Sat, 29 Oct 2022 00:18:48 +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 1ooZYm-0004lf-0g for pgsql-admin@lists.postgresql.org; Sat, 29 Oct 2022 00:18:48 +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 1ooZYj-0002ok-Ry for pgsql-admin@lists.postgresql.org; Sat, 29 Oct 2022 00:18:47 +0000 Received: from smtp1.mailbox.org (smtp1.mailbox.org [IPv6:2001:67c:2050:b231:465::1]) (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 4Mzg4b4SVGz9sNY; Sat, 29 Oct 2022 02:18:39 +0200 (CEST) Date: Sat, 29 Oct 2022 02:18:38 +0200 (CEST) From: Erik Wienhold To: Scott Ribe , Teju Jakkidi vlogs Cc: Holger Jakobs , pgsql-admin@lists.postgresql.org Message-ID: <2102110489.726355.1667002718732@office.mailbox.org> In-Reply-To: References: <59B1180A-4A4B-4A1A-B50A-C9D9B41AA67D@gmail.com> <74e777cb-c332-3db7-7c4a-784d408227d6@jakobs.com> <032E52B8-00C2-431D-BBAF-DCFCCEA817D2@elevated-dev.com> Subject: Re: Duplicate data even with primary keys enabled MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4Mzg4b4SVGz9sNY List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 28/10/2022 22:50 CEST Scott Ribe wrote: > > A corrupted index could allow that to happen by failing to catch the duplicate > insert. I suppose it might also be able to cause the same row to be returned > multiple times--although I have personally only ever seen corrupted indexes > cause rows to not be found. I think it should be possible to tell those two cases apart by looking at system column ctid. The ctid should be the same for duplicate rows if the same row is found multiple times in the index. Different ctid if the corrupted index allowed INSERT with duplicates. -- Erik