20130214_3_reindex_concurrently_docs_v11.patch

diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 7222665..6d2cc53 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">name</replaceable> [ FORCE ]
+REINDEX { INDEX | TABLE | DATABASE | SYSTEM } [ CONCURRENTLY ] <replaceable class="PARAMETER">name</replaceable> [ FORCE ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -68,9 +68,12 @@ REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">nam
       An index build with the <literal>CONCURRENTLY</> option failed, leaving
       an <quote>invalid</> index. Such indexes are useless but it can be
       convenient to use <command>REINDEX</> to rebuild them. Note that
-      <command>REINDEX</> will not perform a concurrent build. To build the
-      index without interfering with production you should drop the index and
-      reissue the <command>CREATE INDEX CONCURRENTLY</> command.
+      <command>REINDEX</> will perform a concurrent build if <literal>
+      CONCURRENTLY</> is specified. To build the index without interfering
+      with production you should drop the index and reissue either the
+      <command>CREATE INDEX CONCURRENTLY</> or <command>REINDEX CONCURRENTLY</>
+      command. Indexes of toast relations can be rebuilt with <command>REINDEX
+      CONCURRENTLY</>.
      </para>
     </listitem>
 
@@ -139,6 +142,21 @@ REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">nam
    </varlistentry>
 
    <varlistentry>
+    <term><literal>CONCURRENTLY</literal></term>
+    <listitem>
+     <para>
+      When this option is used, <productname>PostgreSQL</> will rebuild the
+      index without taking any locks that prevent concurrent inserts,
+      updates, or deletes on the table; whereas a standard reindex build
+      locks out writes (but not reads) on the table until it's done.
+      There are several caveats to be aware of when using this option
+      &mdash; see <xref linkend="SQL-REINDEX-CONCURRENTLY"
+      endterm="SQL-REINDEX-CONCURRENTLY-title">.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><literal>FORCE</literal></term>
     <listitem>
      <para>
@@ -231,6 +249,111 @@ REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">nam
    to be reindexed by separate commands.  This is still possible, but
    redundant.
   </para>
+
+
+  <refsect2 id="SQL-REINDEX-CONCURRENTLY">
+   <title id="SQL-REINDEX-CONCURRENTLY-title">Rebuilding Indexes Concurrently</title>
+
+   <indexterm zone="SQL-REINDEX-CONCURRENTLY">
+   <primary>index</primary>
+   <secondary>rebuilding concurrently</secondary>
+   </indexterm>
+
+   <para>
+    Rebuilding an index can interfere with regular operation of a database.
+    Normally <productname>PostgreSQL</> locks the table whose index is rebuilt
+    against writes and performs the entire index build with a single scan of the
+    table. Other transactions can still read the table, but if they try to
+    insert, update, or delete rows in the table they will block until the
+    index rebuild is finished. This could have a severe effect if the system is
+    a live production database.  Very large tables can take many hours to be
+    indexed, and even for smaller tables, an index rebuild can lock out writers
+    for periods that are unacceptably long for a production system.
+   </para>
+
+   <para>
+    <productname>PostgreSQL</> supports rebuilding indexes without locking
+    out writes.  This method is invoked by specifying the
+    <literal>CONCURRENTLY</> option of <command>REINDEX</>.
+    When this option is used, <productname>PostgreSQL</> must perform two
+    scans of the table for each index that needs to be rebuild and in
+    addition it must wait for all existing transactions that could potentially
+    use the index to terminate. This method requires more total work than a
+    standard index rebuild and takes significantly longer to complete as it
+    needs to wait for unfinished transactiions that might modify the index.
+    However, since it allows normal operations to continue while the index
+    is rebuilt, this method is useful for rebuilding indexes in a production
+    environment.  Of course, the extra CPU, memory and I/O load imposed by
+    the index rebuild might slow other operations.
+   </para>
+
+   <para>
+    In a concurrent index build, a new index that will replace the one to
+    be rebuild is actually entered into the system catalogs in one transaction,
+    then two table scans occur in two more transactions and to make the new
+    index valid from the other backends.  Once this is performed, the old
+    and fresh indexes are swapped in, and the old index is marked as invalid
+    in a third transaction. Finally two additional transactions are used to mark
+    the old index as not ready and then drop it.
+   </para>
+
+   <para>
+    If a problem arises while rebuilding the indexes, such as a
+    uniqueness violation in a unique index, the <command>REINDEX</>
+    command will fail but leave behind an <quote>invalid</> new index on top
+    of the existing one. This index will be ignored for querying purposes
+    because it might be incomplete; however it will still consume update
+    overhead. The <application>psql</> <command>\d</> command will report
+    such an index as <literal>INVALID</>:
+
+<programlisting>
+postgres=# \d tab
+       Table "public.tab"
+ Column |  Type   | Modifiers
+--------+---------+-----------
+ col    | integer |
+Indexes:
+    "idx" btree (col)
+    "idx_cct" btree (col) INVALID
+</programlisting>
+
+    The recommended recovery method in such cases is to drop the concurrent
+    index and try again to perform <command>REINDEX CONCURRENTLY</> once again.
+    The concurrent index created during the processing has a name finishing by
+    the suffix cct. This works as well with indexes of toast relations.
+   </para>
+
+   <para>
+    Regular index builds permit other regular index builds on the
+    same table to occur in parallel, but only one concurrent index build
+    can occur on a table at a time.  In both cases, no other types of schema
+    modification on the table are allowed meanwhile.  Another difference
+    is that a regular <command>REINDEX TABLE</> or <command>REINDEX INDEX</>
+    command can be performed within a transaction block, but
+    <command>REINDEX CONCURRENTLY</> cannot. <command>REINDEX DATABASE</> is
+    by default not allowed to run inside a transaction block, so in this case
+    <command>CONCURRENTLY</> is not supported.
+   </para>
+
+   <para>
+    Invalid indexes of toast relations can be dropped if a failure occurred
+    during <command>REINDEX CONCURRENTLY</>. Live indexes of toast relations
+    cannot be dropped.
+   </para>
+
+   <para>
+    <command>REINDEX DATABASE</command> used with <command>CONCURRENTLY
+    </command> rebuilds concurrently only the non-system relations. System
+    relations are rebuilt with a non-concurrent context. Toast indexes are
+    rebuilt concurrently if the relation they depend on is a non-system
+    relation.
+   </para>
+
+   <para>
+    <command>REINDEX SYSTEM</command> does not support <command>CONCURRENTLY
+    </command>.
+   </para>
+  </refsect2>
  </refsect1>
 
  <refsect1>
@@ -262,7 +385,17 @@ $ <userinput>psql broken_db</userinput>
 ...
 broken_db=&gt; REINDEX DATABASE broken_db;
 broken_db=&gt; \q
-</programlisting></para>
+</programlisting>
+  </para>
+
+  <para>
+   Rebuild a table concurrently:
+
+<programlisting>
+REINDEX TABLE CONCURRENTLY my_broken_table;
+</programlisting>
+  </para>
+
  </refsect1>
 
  <refsect1>