Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: Lauren Fliksteen <dancernerd32@gmail.com>
Mime-Version: 1.0 (1.0)
Subject: Re: INVALID index while concurrent indexing in progress?
Date: Thu, 19 May 2022 09:15:45 -0700
Message-Id: <01987150-EDDC-4567-944C-370F46AE9807@gmail.com>
References: <c10cb5991b0214abe5e768ab5a87490d6aeca1d1.camel@cybertec.at>
Cc: Rajakavitha Kodhandapani <krajakavitha@gmail.com>,
 pgsql-docs@lists.postgresql.org
In-Reply-To: <c10cb5991b0214abe5e768ab5a87490d6aeca1d1.camel@cybertec.at>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Archived-At: 
 <https://www.postgresql.org/message-id/01987150-EDDC-4567-944C-370F46AE9807%40gmail.com>
Precedence: bulk

Thank you both! I think Laurenz=E2=80=99s changes make perfect sense!

Sent from my iPhone

> On May 19, 2022, at 8:37 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote=
:
>=20
> =EF=BB=BFOn Thu, 2022-05-19 at 19:02 +0530, Rajakavitha Kodhandapani wrote=
:
>>> I think the INVALID index can use further explanation, in particular, fr=
om
>>> my experience it seems like when building an index concurrently, the ind=
ex
>>> gets inserted and labeled invalid while the index is being built, and th=
en
>>> the label gets removed if it finishes successfully or gets left on the i=
ndex
>>> if there is a failure while building the index.  It is my current
>>> understanding, after experimenting, that INVALID means 'incomplete', whe=
ther
>>> that's because it's in progress or because it was unable to be completed=
,
>>> but prior to my experiment my understanding was that INVALID indicated
>>> failure.
>>>=20
>>> This was especially confusing when we were adding an index to a very lar=
ge
>>> table because we assumed the INVALID index indicated failure when we
>>> couldn't find any other sign of progress or failure.
>>=20
>> This is my first attempt at contributing to the documentation of PostgreS=
QL.
>> Here's the patch. Please let me know if any other changes need to be made=
.
>=20
> Thank you!  Please send patches as plain text and use bottom posting.
>=20
>> \cf3 @@ -665,11 +665,14 @@\cf5  Indexes:\
>>    <para>\
>>     Another caveat when building a unique index concurrently is that the\=

>>     uniqueness constraint is already being enforced against other transac=
tions\
>> \cf6 -    when the second table scan begins.  This means that constraint v=
iolations\cf5 \
>> \cf4 +    when the second table scan begins. This means that constraint v=
iolations\cf5 \
>>     could be reported in other queries prior to the index becoming availa=
ble\
>> \cf6 -    for use, or even in cases where the index build eventually fail=
s.  Also,\cf5 \
>> \cf6 -    if a failure does occur in the second scan, the <quote>invalid<=
/quote> index\cf5 \
>> \cf6 -    continues to enforce its uniqueness constraint afterwards.\cf5 \=

>> \cf4 +    for use, or even in cases where the index build eventually fail=
s. The index\cf5 \
>> \cf4 +    is inserted and labeled <quote>invalid</quote> while the index i=
s being built,\cf5 \
>> \cf4 +    and then the label is removed if the index builds successfully.=
 If the index does\cf5 \
>> \cf4 +    not build successfully, then the label <quote>invalid</quote> r=
emains. Also, if a\cf5 \
>> }
>=20
> I don't think that this information should be added to a paragraph that
> focuses on uniqueness checks in concurrent index builds.
>=20
> Actually, most of the information is already there.  To quote from the pag=
e:
>=20
>  If a problem arises while scanning the table, such as a deadlock or a uni=
queness violation
>  in a unique index, the CREATE INDEX command will fail but leave behind an=
 =E2=80=9Cinvalid=E2=80=9D index.
>=20
> How about the following patch to emphasize the role of "invalid":
>=20
> diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_=
index.sgml
> index d3102a87d9..fee2c61e5e 100644
> --- a/doc/src/sgml/ref/create_index.sgml
> +++ b/doc/src/sgml/ref/create_index.sgml
> @@ -622,7 +622,7 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EX=
ISTS ] <replaceable class=3D
>    </para>
>=20
>    <para>
> -    In a concurrent index build, the index is actually entered into
> +    In a concurrent index build, the index is actually entered as <quote>=
invalid</quote> index into
>     the system catalogs in one transaction, then two table scans occur in
>     two more transactions.  Before each table scan, the index build must
>     wait for existing transactions that have modified the table to termina=
te.
> @@ -631,7 +631,7 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EX=
ISTS ] <replaceable class=3D
>     scan to terminate, including transactions used by any phase of concurr=
ent
>     index builds on other tables, if the indexes involved are partial or h=
ave
>     columns that are not simple column references.
> -    Then finally the index can be marked ready for use,
> +    Then finally the index can be marked <quote>valid</quote> and ready f=
or use,
>     and the <command>CREATE INDEX</command> command terminates.
>     Even then, however, the index may not be immediately usable for querie=
s:
>     in the worst case, it cannot be used as long as transactions exist tha=
t
>=20
> Yours,
> Laurenz Albe