Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.700.6.1.1\))
Subject: Re: [PATCH] Improve amcheck to also check UNIQUE constraint in btree
 index.
From: Mark Dilger <mark.dilger@enterprisedb.com>
In-Reply-To: <CALT9ZEHYM_zye46Y1ZmC=Sq5Ky-=uUgOjtQ_31Mzu1DbGn3=6g@mail.gmail.com>
Date: Fri, 17 May 2024 12:10:10 -0700
Cc: Alexander Korotkov <aekorotkov@gmail.com>,
 Tom Lane <tgl@sss.pgh.pa.us>,
 Noah Misch <noah@leadboat.com>,
 Peter Eisentraut <peter@eisentraut.org>,
 Aleksander Alekseev <aleksander@timescale.com>,
 Postgres hackers <pgsql-hackers@lists.postgresql.org>,
 Maxim Orlov <orlovmg@gmail.com>,
 Andres Freund <andres@anarazel.de>,
 Greg Stark <stark@mit.edu>,
 Julien Rouhaud <rjuju123@gmail.com>,
 David Steele <david@pgmasters.net>,
 Peter Geoghegan <pg@bowt.ie>,
 Maxim Orlov <m.orlov@postgrespro.ru>,
 lubennikovaav@gmail.com
Content-Transfer-Encoding: quoted-printable
Message-Id: <28FCD9E5-795E-44F4-ADFA-DBCAC1945DA1@enterprisedb.com>
References: <CALT9ZEG9Zk5TxuDTnZPGe9H+b7DL40gXfeouWCaZ3A70yvyWLw@mail.gmail.com>
 <CALT9ZEG1YAfPBH-ZMnK-cxu0SDdrV_w0QEBs9BLHfWvddpOHFA@mail.gmail.com>
 <20220922151328.lakwdytvwnyyif3b@awork3.anarazel.de>
 <CACG=ezbRVZrdTguWphiFP-L2cCi_jy+spZA0wPJWA806B0=2dQ@mail.gmail.com>
 <CACG=ezYk7up-BUL30SrGozURYWt97BK2f=XhUL6S+qU3F9qVzQ@mail.gmail.com>
 <CAJ7c6TOe-ifS9JAqhk-5rPrAbnfGkxNYQiWnR6CUaAqLP31T6A@mail.gmail.com>
 <CAPpHfduqJ9QCOrXDjkPxPRxJRws1FgFELwB2Yz3L6-n7FQMsrA@mail.gmail.com>
 <8c74cc5e-4b97-4cd7-9aaf-67886822a78c@eisentraut.org>
 <CAPpHfdvY7UitNMgUKVx73+W_6STCRoiRBmoE2t7WP1-KBodT+g@mail.gmail.com>
 <CALT9ZEFY-vsL4-cxta49wP=H7RjDaZk+Wg-ft4m-kVS_=WMsAQ@mail.gmail.com>
 <20240501022412.85@rfd.leadboat.com>
 <CAPpHfdsLcQ2w24r9CCNVwaDwKMXabQzG15KwSaaqMAczF1DdVg@mail.gmail.com>
 <CAPpHfdvxK+KSDqopaVdmCHgbO92AN4ibrbiCJ6i6-+g4oma-6Q@mail.gmail.com>
 <1375614.1715301775@sss.pgh.pa.us>
 <CAPpHfdstoBC7TLsLZ5eKUW7LNHcVo2d5X4msmgsigb7nxiD-5A@mail.gmail.com>
 <CALT9ZEFS8z2KoZ9omyfVyz-h54DSPP_jLp5Zc3Fzt6g1Fnw9MA@mail.gmail.com>
 <8635D8B1-45B7-4D67-86C7-D6ECB4F10B11@enterprisedb.com>
 <CAPpHfduspEtrBN_BrAg90iW1VdoJcdTH7Hnx68GeiEqpAfocpg@mail.gmail.com>
 <BC582142-AD7E-4591-A541-7578B62DE3D8@enterprisedb.com>
 <CAPpHfdu4jF3TrML0rcNczYcf__xXRycKzn2Vi+kKgiYNNC0w-A@mail.gmail.com>
 <CAPpHfdu6bGLxe-+9jfDwoji_T2iD6mjdiAGBr0JT-iX+TWNWOw@mail.gmail.com>
 <CAPpHfdvfA6nCqGOcNrv33YMR3_VkVZqdxWmk1RiFuA2M1DDGQg@mail.gmail.com>
 <2EDAC2EB-E8C2-4B56-9094-288ABD45DA4E@enterprisedb.com>
 <CALT9ZEHYM_zye46Y1ZmC=Sq5Ky-=uUgOjtQ_31Mzu1DbGn3=6g@mail.gmail.com>
To: Pavel Borisov <pashkin.elfe@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/28FCD9E5-795E-44F4-ADFA-DBCAC1945DA1%40enterprisedb.com>
Precedence: bulk


> On May 17, 2024, at 11:51 AM, Pavel Borisov <pashkin.elfe@gmail.com> =
wrote:
>=20
> Amcheck with checkunique option does check uniqueness violation =
between pages. But it doesn't warranty detection of cross page =
uniqueness violations in extremely rare cases when the first equal index =
entry on the next page corresponds to tuple that is not visible (e.g. =
dead). In this, I followed the Peter's notion [1] that checking across a =
number of dead equal entries that could theoretically span even across =
many pages is an unneeded code complication and amcheck is not a tool =
that provides any warranty when checking an index.

This confuses me a bit.  The regression test creates a table and index =
but never performs any DELETE nor any UPDATE operations, so none of the =
index entries should be dead.  If I am understanding you correct, I'd be =
forced to conclude that the uniqueness checking code is broken.  Can you =
take a look?

=E2=80=94
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company