MIME-Version: 1.0
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>
 <28FCD9E5-795E-44F4-ADFA-DBCAC1945DA1@enterprisedb.com>
In-Reply-To: <28FCD9E5-795E-44F4-ADFA-DBCAC1945DA1@enterprisedb.com>
From: Pavel Borisov <pashkin.elfe@gmail.com>
Date: Fri, 17 May 2024 23:42:42 +0400
Message-ID: <CALT9ZEEJs33i2u_vGUFdubw7y_C92HPyNuwfS5uYS+71XmJEgw@mail.gmail.com>
Subject: Re: [PATCH] Improve amcheck to also check UNIQUE constraint in btree index.
To: Mark Dilger <mark.dilger@enterprisedb.com>
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-Type: multipart/alternative; boundary="000000000000ca8c9d0618ab8ebb"
Archived-At: <https://www.postgresql.org/message-id/CALT9ZEEJs33i2u_vGUFdubw7y_C92HPyNuwfS5uYS%2B71XmJEgw%40mail.gmail.com>
Precedence: bulk

--000000000000ca8c9d0618ab8ebb
Content-Type: text/plain; charset="UTF-8"

Hi, Mark!

On Fri, 17 May 2024 at 23:10, Mark Dilger <mark.dilger@enterprisedb.com>
wrote:

>
>
> > On May 17, 2024, at 11:51 AM, Pavel Borisov <pashkin.elfe@gmail.com>
> wrote:
> >
> > 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?
>
At the first glance it's not clear to me:
- why your test creates cross-page unique constraint violations?
- how do you know they are not detected?

--000000000000ca8c9d0618ab8ebb
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi, Mark!</div><br><div class=3D"gmail_quote"><div di=
r=3D"ltr" class=3D"gmail_attr">On Fri, 17 May 2024 at 23:10, Mark Dilger &l=
t;<a href=3D"mailto:mark.dilger@enterprisedb.com">mark.dilger@enterprisedb.=
com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x"><br>
<br>
&gt; On May 17, 2024, at 11:51 AM, Pavel Borisov &lt;<a href=3D"mailto:pash=
kin.elfe@gmail.com" target=3D"_blank">pashkin.elfe@gmail.com</a>&gt; wrote:=
<br>
&gt; <br>
&gt; Amcheck with checkunique option does check uniqueness violation betwee=
n pages. But it doesn&#39;t warranty detection of cross page uniqueness vio=
lations in extremely rare cases when the first equal index entry on the nex=
t page corresponds to tuple that is not visible (e.g. dead). In this, I fol=
lowed the Peter&#39;s notion [1] that checking across a number of dead equa=
l entries that could theoretically span even across many pages is an unneed=
ed code complication and amcheck is not a tool that provides any warranty w=
hen checking an index.<br>
<br>
This confuses me a bit.=C2=A0 The regression test creates a table and index=
 but never performs any DELETE nor any UPDATE operations, so none of the in=
dex entries should be dead.=C2=A0 If I am understanding you correct, I&#39;=
d be forced to conclude that the uniqueness checking code is broken.=C2=A0 =
Can you take a look?<br></blockquote><div>At the first glance it&#39;s not =
clear to me:=C2=A0</div><div>- why your test creates cross-page unique cons=
traint violations?</div><div>- how do you know they are not detected?</div>=
<div><br></div></div></div>

--000000000000ca8c9d0618ab8ebb--