Repair some REINDEX problems per recent discussions. The relcache is

now able to cope with assigning new relfilenode values to nailed-in-cache
indexes, so they can be reindexed using the fully crash-safe method.  This
leaves only shared system indexes as special cases.  Remove the 'index
deactivation' code, since it provides no useful protection in the shared-
index case.  Require reindexing of shared indexes to be done in standalone
mode, but remove other restrictions on REINDEX.  -P (IgnoreSystemIndexes)
now prevents using indexes for lookups, but does not disable index updates.
It is therefore safe to allow from PGOPTIONS.  Upshot: reindexing system catalogs
can be done without a standalone backend for all cases except
shared catalogs.

2 files changed, 97 insertions, 52 deletions

diff --git a/doc/src/sgml/ref/postgres-ref.sgml b/doc/src/sgml/ref/postgres-ref.sgml
index b80c9caafac..279d8875e0e 100644
--- a/doc/src/sgml/ref/postgres-ref.sgml
+++ b/doc/src/sgml/ref/postgres-ref.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/postgres-ref.sgml,v 1.36 2003/09/18 20:30:15 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/postgres-ref.sgml,v 1.37 2003/09/24 18:54:01 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -177,9 +177,9 @@ PostgreSQL documentation
       <term><option>-P</option></term>
       <listitem>
        <para>
-	Ignore system indexes while scanning/updating system tables. The
-	<command>REINDEX</command> command for system tables/indexes
-	requires this option to be used.
+	Ignore system indexes when reading system tables (but still update
+	the indexes when modifying the tables).  This is useful when
+	recovering from damaged system indexes.
        </para>
       </listitem>
      </varlistentry>
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 29b96e462c3..d945112de79 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/reindex.sgml,v 1.20 2003/09/11 21:42:20 momjian Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/reindex.sgml,v 1.21 2003/09/24 18:54:01 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -56,43 +56,6 @@ REINDEX { DATABASE | TABLE | INDEX } <replaceable class="PARAMETER">name</replac
     </listitem>
    </itemizedlist>
   </para>
-
-  <para>
-   If you suspect corruption of an index on a user table, you can
-   simply rebuild that index, or all indexes on the table, using
-   <command>REINDEX INDEX</command> or <command>REINDEX
-   TABLE</command>.  Another approach to dealing with a corrupted
-   user-table index is just to drop and recreate it.  This may in fact
-   be preferable if you would like to maintain some semblance of
-   normal operation on the table meanwhile.  <command>REINDEX</>
-   acquires exclusive lock on the table, while <command>CREATE
-   INDEX</> only locks out writes not reads of the table.
-  </para>
-
-  <para>
-   Things are more difficult if you need to recover from corruption of
-   an index on a system table.  In this case it's important for the
-   system to not have used any of the suspect indexes itself.
-   (Indeed, in this sort of scenario you may find that server
-   processes are crashing immediately at start-up, due to reliance on
-   the corrupted indexes.)  To recover safely, the server must be shut
-   down and a stand-alone <productname>PostgreSQL</productname> server
-   must be started instead with the command-line options
-   <option>-O</option> and <option>-P</option>.  (These options allow
-   system table modifications and prevent use of system indexes,
-   respectively.)  Then, <command>REINDEX DATABASE</>,
-   <command>REINDEX TABLE</>, or <command>REINDEX INDEX</> can be
-   issued, depending on how much you want to reconstruct.  If in
-   doubt, use <command>REINDEX DATABASE FORCE</> to force
-   reconstruction of all system indexes in the database.  Then quit
-   the standalone server session and restart the real server.
-  </para>
-
-  <para>
-   See the <xref linkend="app-postgres"> reference page for more
-   information about how to interact with the stand-alone server
-   interface.
-  </para>
  </refsect1>
   
  <refsect1>
@@ -104,8 +67,8 @@ REINDEX { DATABASE | TABLE | INDEX } <replaceable class="PARAMETER">name</replac
     <listitem>
      <para>
       Recreate all system indexes of a specified database. Indexes on
-      user tables are not included. This form of <command>REINDEX</>
-      can only be used in stand-alone mode (see above).
+      user tables are not processed.  Also, indexes on shared system
+      catalogs are skipped except in stand-alone mode (see below).
      </para>
     </listitem>
    </varlistentry>
@@ -114,7 +77,8 @@ REINDEX { DATABASE | TABLE | INDEX } <replaceable class="PARAMETER">name</replac
     <term><literal>TABLE</literal></term>
     <listitem>
      <para>
-      Recreate all indexes of a specified table.
+      Recreate all indexes of a specified table.  If the table has a
+      secondary <quote>TOAST</> table, that is reindexed as well.
      </para>
     </listitem>
    </varlistentry>
@@ -142,10 +106,7 @@ REINDEX { DATABASE | TABLE | INDEX } <replaceable class="PARAMETER">name</replac
     <term><literal>FORCE</literal></term>
     <listitem>
      <para>
-      Force rebuild of system indexes.  Without this key word,
-      <command>REINDEX</> skips system indexes that are not marked
-      invalid.  <literal>FORCE</> is irrelevant for <command>REINDEX
-      INDEX</> or when reindexing user indexes.
+      This is an obsolete option; it is ignored if specified.
      </para>
     </listitem>
    </varlistentry>
@@ -153,6 +114,86 @@ REINDEX { DATABASE | TABLE | INDEX } <replaceable class="PARAMETER">name</replac
  </refsect1>
 
  <refsect1>
+  <title>Notes</title>
+
+  <para>
+   If you suspect corruption of an index on a user table, you can
+   simply rebuild that index, or all indexes on the table, using
+   <command>REINDEX INDEX</command> or <command>REINDEX
+   TABLE</command>.  Another approach to dealing with a corrupted
+   user-table index is just to drop and recreate it.  This may in fact
+   be preferable if you would like to maintain some semblance of
+   normal operation on the table meanwhile.  <command>REINDEX</>
+   acquires exclusive lock on the table, while <command>CREATE
+   INDEX</> only locks out writes not reads of the table.
+  </para>
+
+  <para>
+   Things are more difficult if you need to recover from corruption of
+   an index on a system table.  In this case it's important for the
+   system to not have used any of the suspect indexes itself.
+   (Indeed, in this sort of scenario you may find that server
+   processes are crashing immediately at start-up, due to reliance on
+   the corrupted indexes.)  To recover safely, the server must be started
+   with the <option>-P</option> option, which prevents it from using
+   indexes for system catalog lookups.
+  </para>
+
+  <para>
+   One way to do this is to shut down the postmaster and start a stand-alone
+   <productname>PostgreSQL</productname> server
+   with the <option>-P</option> option included on its command line.
+   Then, <command>REINDEX DATABASE</>,
+   <command>REINDEX TABLE</>, or <command>REINDEX INDEX</> can be
+   issued, depending on how much you want to reconstruct.  If in
+   doubt, use <command>REINDEX DATABASE</> to select
+   reconstruction of all system indexes in the database.  Then quit
+   the standalone server session and restart the regular server.
+   See the <xref linkend="app-postgres"> reference page for more
+   information about how to interact with the stand-alone server
+   interface.
+  </para>
+
+  <para>
+   Alternatively, a regular server session can be started with
+   <option>-P</option> included in its command line options.
+   The method for doing this varies across clients, but in all
+   <application>libpq</>-based clients, it is possible to set
+   the <envar>PGOPTIONS</envar> environment variable to <literal>-P</>
+   before starting the client.  Note that while this method does not
+   require locking out other clients, it may still be wise to prevent
+   other users from connecting to the damaged database until repairs
+   have been completed.
+  </para>
+
+  <para>
+   If corruption is suspected in the indexes of any of the shared
+   system catalogs (<structname>pg_database</structname>,
+   <structname>pg_group</structname>, or
+   <structname>pg_shadow</structname>), then a standalone server
+   must be used to repair it.  <command>REINDEX</> will not process
+   shared catalogs in multiuser mode.
+  </para>
+
+  <para>
+   For all indexes except the shared system catalogs, <command>REINDEX</>
+   is crash-safe and transaction-safe.  <command>REINDEX</> is not
+   crash-safe for shared indexes, which is why this case is disallowed
+   during normal operation.  If a failure occurs while reindexing one
+   of these catalogs in standalone mode, it is important that the failure
+   be rectified and the <command>REINDEX</> operation redone
+   before attempting to restart the regular server.
+  </para>
+
+  <para>
+   Prior to <productname>PostgreSQL</productname> 7.4, <command>REINDEX
+   TABLE</> did not automatically process TOAST tables, and so those had
+   to be reindexed by separate commands.  This is still possible, but
+   redundant.
+  </para>
+ </refsect1>
+
+ <refsect1>
   <title>Examples</title>
 
   <para>
@@ -172,11 +213,15 @@ REINDEX INDEX my_index;
   </para>
 
   <para>
-   Rebuild all system indexes (this will only work in a stand-alone
-   server session):
+   Rebuild all system indexes in a particular database, without trusting them
+   to be valid already:
 
 <programlisting>
-REINDEX DATABASE my_database FORCE;
+$ <userinput>export PGOPTIONS="-P"</userinput>
+$ <userinput>psql broken_db</userinput>
+...
+broken_db=> REINDEX DATABASE broken_db;
+broken_db=> \q
 </programlisting>
   </para>
  </refsect1>