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?

[Laurenz Albe]

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?

[Alvaro Herrera]

On 2022-May-20, Laurenz Albe wrote:

> Alvaro meant that my message already contained a patch, so there is no need to
> send another one, unless you have a different suggestion.

Right.  Being old-school, I can just save the email to a plain text file
and do "patch < /tmp/emailfile".  'patch' has smarts to detect
irrelevant parts of the message, so the complete email works fine as a
patch.

> Added value could be a suggested commit message with "git
> format-patch" that could make the committer's job easier.

Right -- especially so if it comes with Author/Discussion/etc lines.  (I
don't think I've seen any patch submitter do that.)

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

-- 
Álvaro Herrera PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"World domination is proceeding according to plan"(Andrew Morton)

Re: INVALID index while concurrent indexing in progress?

[Laurenz Albe]

On Fri, 2022-05-20 at 10:14 +0530, Rajakavitha Kodhandapani wrote:
> 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.
> > 
> 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

Alvaro meant that my message already contained a patch, so there is no need to
send another one, unless you have a different suggestion.
Added value could be a suggested commit message with "git format-patch" that 
could
make the committer's job easier.

One thing you could do to improve the conversation is to adhere to the project 
style
of adding your responses *below* the quoted text that you respond to (and prune 
away
unnecessary parts), like I am doing here.  I had to reformat your message for 
that
purpose.  That makes it much easier to follow the thread of the conversation.
Consider that these e-mails are archived for posterity.

What you could do to help getting this committed is to add a commitfest entry
for the patch, so that it doesn't get forgotten.  Unfortunately the commitfest
application has trouble finding a thread on -docs, so you may have to send a
reply in this thread to -hackers to make that work.

Yours,
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
   . 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?

[Alvaro Herrera]

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?

[Lauren Fliksteen]

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?

[Laurenz Albe]

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 
] 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 \
}