MIME-Version: 1.0
References: 
 <CADzfLwW9QczZW-E=McxcjUv0e5VMDctQNETbgao0K-SimVhFPA@mail.gmail.com>
 <202505181556.3n6oiowvntyr@alvherre.pgsql>
 <CADzfLwXKtriMnfCNVGNH2ahwXaByjo-QOMWiDTU-9WZqh+zQ5g@mail.gmail.com>
 <CADzfLwW5bDWSxjHK7mqX8Lewki3+5FBydBC+nVcxg4xMGKscyw@mail.gmail.com>
 <CAMAof6-4xaV3QE2ErYJaJhu6qjFn99sWyo_HQeBhHikZM3GexA@mail.gmail.com>
 <CADzfLwXocKhpW3eFP1oScz+m+1XJ3bpi9QmVpoqC9RX9oyX=UA@mail.gmail.com>
 <CAMAof695VA+mbVRhWCTus=E0WnsMAQyqXxfOTohbcb7VUHSP4g@mail.gmail.com>
 <CAMAof69JSL8MYWG2qRScs3RQDpfcyZT_wFwW4SoAvftW+K_p1g@mail.gmail.com>
 <CADzfLwVMtwjHh8KY9kP=_vcYPqHs=JDzuexO4RFQ2fM8VoqovA@mail.gmail.com>
 <CAMAof68L0GO0F0bwuXtLZAjh9k_Hj+o0-8mqfO6iEQyXr4PuVA@mail.gmail.com>
 <CADzfLwUrodAcOggK+3j3LbPLaSXemgHxa-n=LhZTwRAsaakL2g@mail.gmail.com>
 <CAMAof691D4O=3QTuPwJXBYxYpG6s3A=tVhL9vN=T3eeRTMnaig@mail.gmail.com>
 <CADzfLwVT3Y14g6Maz2y92sP2L7rPvpznt+MHM++xiy-U3XMLZQ@mail.gmail.com>
 <CADzfLwXQe9XfQfJs3W-DCPqeqG4rq-6FoYUpGbbpgjcT1Eotpg@mail.gmail.com>
 <CAMAof68kNgwWdkhmZd1ysfyU3PF66Wz+UaUr9g-LJg-_0xBV_Q@mail.gmail.com>
 <CADzfLwUtLqYrupZp4QQuWwv4W_LgYWBRStybvQ+S0SZiHrp62A@mail.gmail.com>
 <CADzfLwVYUBb8cUVQ_1mzVzNMyJH84VZKFCRyATvBZKbLW377CA@mail.gmail.com>
 <CADzfLwWbV1i7+cP_Hqr3qgQnBXkAqgrCQxd5PFzqp2AOTK=40w@mail.gmail.com>
 <CADzfLwXJc0jdDDS43-Fj0gKmwX-FURS3eY7MyLQ89qDPA6T5Ug@mail.gmail.com>
 <CADzfLwVaV15R2rUNZmKqLKweiN3SnUBg=6_qGE_ERb7cdQUD8g@mail.gmail.com>
 <CAEze2WgBffcC_SKGLmVxW8uRTEsrwWOHDQujN6zyxy1tSYLJ=Q@mail.gmail.com>
 <CADzfLwVon8ESWOkg+8KU0F9=Hg7QKriNVX-hqcm-v-XZmHkzig@mail.gmail.com>
 <CAEze2WiXYx1LKr=9d7PLsZOYrGytY9AN__tFFw4p_Ysgm1-e5g@mail.gmail.com>
 <CADzfLwUKXcXKZgX+e8ACsOXe_CgtWmNJY_6dyn8EO0AXYOn2pA@mail.gmail.com>
 <CAEze2WiiR2PeXg_vaURjjiiwvjQ=Um8wxWi1BcVS0BGyxiD2gQ@mail.gmail.com>
 <CAMT0RQQP9JiGqqB+pVBzPT7unG1BMBuLj=kGPk4BeS3g6VyT1A@mail.gmail.com>
 <CAMT0RQSbFJCpetFy22=O=gKR2ZfH=tMTQeCM743T4o3rMjaeTQ@mail.gmail.com>
 <8010.1764584989@localhost>
 <CADzfLwUjgOxk8dd8XLv3jn05gAxxwEZoAA7-2Owb2CczkSb6Tw@mail.gmail.com>
 <5778.1764660480@localhost>
 <CADzfLwXMzv4_2Mc54qpASJ7FrKwQ6OsG0d9GxiYDxEKZ2KqHSA@mail.gmail.com>
 <CAEze2Wg6d2M8hop4uwdQXeH-YkOmEHyqp83+aE7vEzKdmu7w-A@mail.gmail.com>
 <CADzfLwVPQOF7m5x4d6=1tRJO==FUZdc3LeO=N_TpmOzii7iH_Q@mail.gmail.com>
 <CADzfLwVHzfdD3WB0iNDKJtXQ37VCWrYnCULz3QKzwM7jJFHt-A@mail.gmail.com>
 <CADzfLwU4oHR9gMV+OHgerp2ZLi3BSfLRZcBsG8g3G0f_RLPrGg@mail.gmail.com>
 <CADzfLwVeQikArGV885zRMiSDW2-y=h=bvQiROvdq1Re1ojx9QA@mail.gmail.com>
 <CADzfLwVWEgcC2s4Fgrr1j8y4-MM2wXdcgg4_LxPprgDes2v8ew@mail.gmail.com>
 <CADzfLwWJGCRJsWdNH8x1nh_5anbCbGQDLSL3hA2+bj+--29ahg@mail.gmail.com>
 <CADzfLwWQ4DKRRF5w2XUR9dFUtkxvKxKzWa5ARw7O9mSYdoufRg@mail.gmail.com>
In-Reply-To: 
 <CADzfLwWQ4DKRRF5w2XUR9dFUtkxvKxKzWa5ARw7O9mSYdoufRg@mail.gmail.com>
From: Josh Kupershmidt <schmiddy@gmail.com>
Date: Mon, 6 Apr 2026 21:42:17 -0400
Message-ID: 
 <CAK3UJRGmCgCS5_pZDvugRmGhRuND0TwXXNs-u2sZzRQOE9E_JQ@mail.gmail.com>
Subject: Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements
To: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>,
 Antonin Houska <ah@cybertec.at>,
	Hannu Krosing <hannuk@google.com>,
 Sergey Sargsyan <sergey.sargsyan.2001@gmail.com>,
	=?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>,
	Andres Freund <andres@anarazel.de>, Michael Paquier <michael@paquier.xyz>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 Andrey Borodin <amborodin86@gmail.com>,
	Melanie Plageman <melanieplageman@gmail.com>
Content-Type: multipart/alternative; boundary="0000000000008c517a064ed4e57e"
Archived-At: 
 <https://www.postgresql.org/message-id/CAK3UJRGmCgCS5_pZDvugRmGhRuND0TwXXNs-u2sZzRQOE9E_JQ%40mail.gmail.com>
Precedence: bulk

--0000000000008c517a064ed4e57e
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi,

I was interested in this feature, and took an initial look through the
patch. Sorry in advance that I'm missing some previous context from the
thread's history, I'm starting fresh here.

A few initial notes from looking at the v34 patches:

Usability and docs:
 * We're leaving behind two invalid indexes now that the user has to figure
out how to drop in case of an error - that seems like it could be confusing
for the user. Could we have some better way (error handler,
background worker) try to perform this cleanup automatically? If not, we
should at least tell the user clearly in the error message that both
invalid indexes are left behind (i.e. "idx" and "idx_ccaux" in the example)
 * Docs are inconsistent or confusing about whether there's one or two
indexes left behind in case of error - e.g. "command will fail but leave
behind *an* invalid index and its associated auxiliary index" - somewhat
implying there is only one invalid index, and somehow the auxiliary index
is valid?
 * Similarly, when the doc mentions e.g. "drop the index" - it's not
necessarily clear which index we're talking about when there are two
invalid indexes left behind that the user will see with `\d`
 * It would be nice to guard against users trying arbitrary CREATE INDEX
... USING stir(...) with a clear error

Few behavior notes and questions:
 * One of the testcases (line 2478 of patch 0004) does `DELETE FROM
concur_reindex_tab4 WHERE c1 =3D 1;` but the table `concur_reindex_tab4`
looks like it has been dropped a few lines above that?
 * The StirPageGetFreeSpace macro from patch 0002 reads
`StirPageGetMaxOffset(page)` which seems like it could cause an unsafe read
of opaque->maxoff if used on the metapage
 * A comment explains "No predicate evaluation is needed here" , i.e. we
are skipping predicate evaluation in the validation scan step, assuming
that the auxiliary index contains only qualifying TIDs. Is this really
bulletproof for e.g. partial indexes which may no longer satisfy the
predicate at the time of the validation scan due to conflicting HOT updates=
?

Thanks
Josh

On Mon, Apr 6, 2026 at 2:22=E2=80=AFPM Mihail Nikalayeu <mihailnikalayeu@gm=
ail.com>
wrote:

> Rebased once again.
>

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

<div dir=3D"ltr">Hi,<div><br></div><div>I was interested in this feature, a=
nd took an initial look through the patch. Sorry in advance that I&#39;m mi=
ssing some previous context from the thread&#39;s history, I&#39;m starting=
 fresh here.</div><div><br></div><div>A few initial notes from looking at t=
he v34 patches:<div><br></div><div>Usability and docs:</div><div>=C2=A0* We=
&#39;re leaving behind two invalid indexes now that the user has to figure =
out how to drop in case of an error - that seems like it could be confusing=
 for the user. Could we have some better way (error handler, background=C2=
=A0worker) try to perform this cleanup automatically? If not, we should at =
least tell the user clearly in the error message that both invalid indexes =
are left behind (i.e. &quot;idx&quot; and &quot;idx_ccaux&quot; in the exam=
ple)</div><div>=C2=A0* Docs are inconsistent or confusing about whether the=
re&#39;s one or two indexes left behind in case of error - e.g. &quot;comma=
nd will fail but leave behind *an* invalid index and its associated auxilia=
ry index&quot; - somewhat implying there is only one invalid index, and som=
ehow the auxiliary index is valid?</div><div>=C2=A0* Similarly, when the do=
c mentions e.g. &quot;drop the index&quot; - it&#39;s not necessarily clear=
 which index we&#39;re talking about when there are two invalid indexes lef=
t behind that the user will see with `\d`=C2=A0</div><div>=C2=A0* It would =
be nice to guard against users trying arbitrary CREATE INDEX ... USING stir=
(...) with a clear error</div><div><br></div></div><div>Few behavior notes =
and questions:</div><div>=C2=A0* One of the testcases=C2=A0(line 2478 of pa=
tch 0004) does `DELETE FROM concur_reindex_tab4 WHERE c1 =3D 1;` but the ta=
ble `concur_reindex_tab4` looks like it has been dropped a few lines above =
that?</div><div>=C2=A0* The=C2=A0StirPageGetFreeSpace macro from patch 0002=
 reads `StirPageGetMaxOffset(page)` which seems like it could cause an unsa=
fe read of opaque-&gt;maxoff if used on the metapage</div><div>=C2=A0* A co=
mment explains &quot;No predicate evaluation is needed here&quot; , i.e. we=
 are skipping predicate evaluation in the validation scan step, assuming th=
at the auxiliary index contains only qualifying TIDs. Is this really bullet=
proof for e.g. partial indexes which may no longer satisfy the predicate at=
 the time of the validation scan due to conflicting HOT updates?</div><div>=
<br></div><div>Thanks</div><div>Josh</div></div><br><div class=3D"gmail_quo=
te gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Apr=
 6, 2026 at 2:22=E2=80=AFPM Mihail Nikalayeu &lt;<a href=3D"mailto:mihailni=
kalayeu@gmail.com">mihailnikalayeu@gmail.com</a>&gt; wrote:<br></div><block=
quote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1=
px solid rgb(204,204,204);padding-left:1ex">Rebased once again.<br>
</blockquote></div>

--0000000000008c517a064ed4e57e--