Re: INVALID index while concurrent indexing in progress?

Thank you, Laurenz. The changes that you suggested make a lot more sense.
I will make the updates and submit the changes.

Regards,
Rajie

On Thu, May 19, 2022 at 9:45 PM Lauren Fliksteen <dancernerd32(at)gmail(dot)com>
wrote:

> Thank you both! I think Laurenz’s changes make perfect sense!
>
> Sent from my iPhone
>
> > On May 19, 2022, at 8:37 AM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
> wrote:
> >
> > On Thu, 2022-05-19 at 19:02 +0530, Rajakavitha Kodhandapani wrote:
> >>> I think the INVALID index can use further explanation, in particular,
> from
> >>> my experience it seems like when building an index concurrently, the
> index
> >>> gets inserted and labeled invalid while the index is being built, and
> then
> >>> the label gets removed if it finishes successfully or gets left on the
> index
> >>> if there is a failure while building the index. It is my current
> >>> understanding, after experimenting, that INVALID means 'incomplete',
> whether
> >>> 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.
> >>>
> >>> This was especially confusing when we were adding an index to a very
> large
> >>> table because we assumed the INVALID index indicated failure when we
> >>> couldn't find any other sign of progress or failure.
> >>
> >> This is my first attempt at contributing to the documentation of
> PostgreSQL.
> >> Here's the patch. Please let me know if any other changes need to be
> made.
> >
> > Thank you! Please send patches as plain text and use bottom posting.
> >
> >> \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
> transactions\
> >> \cf6 - when the second table scan begins. This means that
> constraint violations\cf5 \
> >> \cf4 + when the second table scan begins. This means that constraint
> violations\cf5 \
> >> could be reported in other queries prior to the index becoming
> available\
> >> \cf6 - for use, or even in cases where the index build eventually
> fails. 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
> fails. The index\cf5 \
> >> \cf4 + is inserted and labeled <quote>invalid</quote> while the
> index is 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>
> remains. Also, if a\cf5 \
> >> }
> >
> > I don't think that this information should be added to a paragraph that
> > focuses on uniqueness checks in concurrent index builds.
> >
> > Actually, most of the information is already there. To quote from the
> page:
> >
> > If a problem arises while scanning the table, such as a deadlock or a
> uniqueness violation
> > in a unique index, the CREATE INDEX command will fail but leave behind
> an “invalid” index.
> >
> > How about the following patch to emphasize the role of "invalid":
> >
> > 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
> EXISTS ] <replaceable class=
> > </para>
> >
> > <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
> terminate.
> > @@ -631,7 +631,7 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT
> EXISTS ] <replaceable class=
> > scan to terminate, including transactions used by any phase of
> concurrent
> > index builds on other tables, if the indexes involved are partial or
> have
> > 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
> for use,
> > and the <command>CREATE INDEX</command> command terminates.
> > Even then, however, the index may not be immediately usable for
> queries:
> > in the worst case, it cannot be used as long as transactions exist
> that
> >
> > Yours,
> > Laurenz Albe
>