Re: INVALID index while concurrent indexing in progress?

[Rajakavitha Kodhandapani]

>
>
> > Anyway, I pushed the patch.  I appreciate your willingness to
> > collaborate and look forward to your future participation, for sure.


Thank you Laurenz and  Alvaro.

Regards,
Rajie

On Fri, May 20, 2022 at 3:55 PM Laurenz Albe 
wrote:

> On Fri, 2022-05-20 at 10:30 +0200, Alvaro Herrera wrote:
> >
> > Anyway, I pushed the patch.  I appreciate your willingness to
> > collaborate and look forward to your future participation, for sure.
>
> Thanks!
>
> Laurenz Albe
>

Re: INVALID index while concurrent indexing in progress?

[Rajakavitha Kodhandapani]

Hi Alvaro,

I am new to PostgreSQL and still in the process of getting familiar with
the workflow.
Please let me know if I am missing something?
My understanding was based on:
https://wiki.postgresql.org/wiki/Submitting_a_Patch

Submitting patch updates

When submitting a new version of a previously submitted patch, you should
do a few additional things:

   - Uniquely identify the new version. You can use git format-patch -vN,
   incrementing N each time; or you can add an incrementing numerical suffix
   manually. Using the ".patch" extension allows some reviewers to more easily
   read it in their email client/code editor.


   - Make sure it's easy to find any earlier discussion of the patch, by
   providing Message-Id-based links to the mailing list posts
   <https://www.postgresql.org/list/pgsql-hackers/>. Don't expect that
   everyone will still be able to find previous submissions on their own. You
   can usually get the message ID of your email by looking for the header
   *Message-Id* in your email client. Try *View message source* or *View
   original* if it's not obviously visible.


Regards,
Rajie

On Thu, May 19, 2022 at 10:06 PM Alvaro Herrera 
wrote:

> On 2022-May-19, Rajakavitha Kodhandapani wrote:
>
> > Thank you, Laurenz. The changes that you suggested make a lot more sense.
> > I will make the updates and submit the changes.
>
> Hmm, but they're already submitted.
>
> --
> Álvaro Herrera   48°01'N 7°57'E  —
> https://www.EnterpriseDB.com/
> "La victoria es para quien se atreve a estar solo"
>

Re: INVALID index while concurrent indexing in progress?

[Rajakavitha Kodhandapani]

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 
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 
> 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:\
> >>\
> >> 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
> invalid 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 invalid 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 invalid
> 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 ]  >
> >
> >
> > -In a concurrent index build, the index is actually entered into
> > +In a concurrent index build, the index is actually entered as
> invalid 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 ]  > 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 valid and ready
> for use,
> > and the CREATE INDEX 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
>

Re: INVALID index while concurrent indexing in progress?

[Rajakavitha Kodhandapani]

Hi,

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.

Regards,
Rajie

On Thu, May 19, 2022 at 1:10 AM PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/14/sql-createindex.html
> Description:
>
> 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.
>
{\rtf1\ansi\ansicpg1252\cocoartf2636
\cocoatextscaling0\cocoaplatform0{\fonttbl\f0\fnil\fcharset0 Menlo-Regular;\f1\fnil\fcharset0 Menlo-Bold;}
{\colortbl;\red255\green255\blue255;\red159\green160\blue28;\red46\green174\blue187;\red47\green180\blue29;
\red0\green0\blue0;\red180\green36\blue25;}
{\*\expandedcolortbl;;\cssrgb\c68469\c68012\c14211;\cssrgb\c20199\c73241\c78251;\cssrgb\c20241\c73898\c14950;
\csgray\c0;\cssrgb\c76411\c21697\c12527;}
\paperw11900\paperh16840\margl1440\margr1440\vieww16300\viewh8400\viewkind0
\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\pardirnatural\partightenfactor0

\f0\fs22 \cf2 \CocoaLigature0 commit 0929611f11a3d326383204337359efef1cfed964 (
\f1\b \cf3 HEAD -> \cf4 update-index
\f0\b0 \cf2 )\cf5 \
Author: Rajakavitha1 \
Date:   Thu May 19 18:33:04 2022 +0530\
\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\pardirnatural\partightenfactor0

\f1\b diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
\f0\b0 \

\f1\b index d3102a87d9..709039c0f8 100644
\f0\b0 \

\f1\b --- a/doc/src/sgml/ref/create_index.sgml
\f0\b0 \

\f1\b +++ b/doc/src/sgml/ref/create_index.sgml
\f0\b0 \
\cf3 @@ -665,11 +665,14 @@\cf5  Indexes:\
\
 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 invalid 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 invalid 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 invalid remains. Also, if a\cf5 \
}

Re: Spelling Mistake

[Rajakavitha Kodhandapani]

On Wed, May 18, 2022 at 03:02:04AM +, PG Doc comments form wrote:
> There are 3 periods instead of 2 which I believe is incorrect.

Also, as mentioned in the conventions


"The following conventions are used in the synopsis of a command: brackets (
[ and ]) indicate optional parts. Braces ({ and }) and vertical lines (|)
indicate that you must choose one alternative. Dots (...) mean that the
preceding element can be repeated. All other symbols, including
parentheses, should be taken literally."


Regards,
Rajie


On Wed, May 18, 2022 at 2:54 PM Michael Paquier  wrote:

> On Wed, May 18, 2022 at 03:02:04AM +, PG Doc comments form wrote:
> > There are 3 periods instead of 2 which I believe is incorrect.
>
> It seems to me that those three periods refer to the root path of the
> source tree that you'd need to type by yourself to reach it.
> --
> Michael
>