Re: [DOC] Document concurrent index builds waiting on each other

On Wed, Sep 18, 2019 at 01:51:00PM -0400, James Coleman wrote:
> In my experience it's not immediately obvious (even after reading the
> documentation) the implications of how concurrent index builds manage
> transactions with respect to multiple concurrent index builds in
> flight at the same time.
> 
> Specifically, as I understand multiple concurrent index builds running
> at the same time will all return at the same time as the longest
> running one.
> 
> I've attached a small patch to call this caveat out specifically in
> the documentation. I think the description in the patch is accurate,
> but please let me know if there's some intricacies around how the
> various stages might change the results.

The CREATE INDEX docs already say:

    In a concurrent index build, the index is actually entered 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.
    After the second scan, the index build must wait for any transactions
--> that have a snapshot (see <xref linkend="mvcc"/>) predating the second
--> scan to terminate.  Then finally the index can be marked ready for use,

So, having multiple concurrent index scans is just a special case of
having to "wait for any transactions that have a snapshot", no?  I am
not sure adding a doc mention of other index builds really is helpful.

---------------------------------------------------------------------------

> commit 9e28e704820eebb81ff94c1c3cbfb7db087b2c45
> Author: James Coleman <jtc331@gmail.com>
> Date:   Wed Sep 18 13:36:22 2019 -0400
> 
>     Document concurrent indexes waiting on each other
>     
>     It's not immediately obvious that because concurrent index building
>     waits on previously running transactions to complete, running multiple
>     concurrent index builds at the same time will result in each of them
>     taking as long to return as the longest takes, so, document this caveat.
> 
> diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
> index 629a31ef79..35f15abb0e 100644
> --- a/doc/src/sgml/ref/create_index.sgml
> +++ b/doc/src/sgml/ref/create_index.sgml
> @@ -616,6 +616,13 @@ Indexes:
>      cannot.
>     </para>
>  
> +   <para>
> +    Because the second table scan must wait for any transactions having a
> +    snapshot preceding the start of that scan to finish before completing the
> +    scan, concurrent index builds on multiple tables at the same time will
> +    not return on any one table until all have completed.
> +   </para>
> +
>     <para>
>      Concurrent builds for indexes on partitioned tables are currently not
>      supported.  However, you may concurrently build the index on each


-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +