Make REINDEX DATABASE do what one would expect, namely reindex all indexes

in the database.  The old behavior (reindex system catalogs only) is now
available as REINDEX SYSTEM.  I did not add the complementary REINDEX USER
case since there did not seem to be consensus for this, but it would be
trivial to add later.  Per recent discussions.

2 files changed, 47 insertions, 21 deletions

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index d68cd817c31..b9ce518554b 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.44 2005/06/13 02:40:04 neilc Exp $
+$PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.45 2005/06/22 21:14:28 tgl Exp $
 -->
 
 <chapter id="maintenance">
@@ -468,8 +468,7 @@ HINT:  Stop the postmaster and use a standalone backend to VACUUM in "mydb".
 
   <para>
    In some situations it is worthwhile to rebuild indexes periodically
-   with the <command>REINDEX</> command. (There is also
-   <filename>contrib/reindexdb</> which can reindex an entire database.)
+   with the <command>REINDEX</> command.
    However, <productname>PostgreSQL</> 7.4 has substantially reduced the need
    for this activity compared to earlier releases.
   </para>
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 9579b95e237..4bb6b9ff756 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/reindex.sgml,v 1.26 2005/01/22 23:22:19 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/reindex.sgml,v 1.27 2005/06/22 21:14:28 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -20,7 +20,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-REINDEX { DATABASE | TABLE | INDEX } <replaceable class="PARAMETER">name</replaceable> [ FORCE ]
+REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">name</replaceable> [ FORCE ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -28,7 +28,7 @@ REINDEX { DATABASE | TABLE | INDEX } <replaceable class="PARAMETER">name</replac
   <title>Description</title>
 
   <para>
-   <command>REINDEX</command> rebuilds an index based on the data
+   <command>REINDEX</command> rebuilds an index using the data
    stored in the index's table, replacing the old copy of the index. There are
    two main reasons to use <command>REINDEX</command>:
 
@@ -63,12 +63,10 @@ REINDEX { DATABASE | TABLE | INDEX } <replaceable class="PARAMETER">name</replac
 
   <variablelist>
    <varlistentry>
-    <term><literal>DATABASE</literal></term>
+    <term><literal>INDEX</literal></term>
     <listitem>
      <para>
-      Recreate all system indexes of a specified database. Indexes on
-      user tables are not processed.  Also, indexes on shared system
-      catalogs are skipped except in stand-alone mode (see below).
+      Recreate the specified index.
      </para>
     </listitem>
    </varlistentry>
@@ -77,17 +75,30 @@ REINDEX { DATABASE | TABLE | INDEX } <replaceable class="PARAMETER">name</replac
     <term><literal>TABLE</literal></term>
     <listitem>
      <para>
-      Recreate all indexes of a specified table.  If the table has a
+      Recreate all indexes of the specified table.  If the table has a
       secondary <quote>TOAST</> table, that is reindexed as well.
      </para>
     </listitem>
    </varlistentry>
 
    <varlistentry>
-    <term><literal>INDEX</literal></term>
+    <term><literal>DATABASE</literal></term>
     <listitem>
      <para>
-      Recreate a specified index.
+      Recreate all indexes within the current database.
+      Indexes on shared system catalogs are skipped except in stand-alone mode
+      (see below).
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>SYSTEM</literal></term>
+    <listitem>
+     <para>
+      Recreate all indexes on system catalogs within the current database.
+      Indexes on user tables are not processed.  Also, indexes on shared
+      system catalogs are skipped except in stand-alone mode (see below).
      </para>
     </listitem>
    </varlistentry>
@@ -96,10 +107,11 @@ REINDEX { DATABASE | TABLE | INDEX } <replaceable class="PARAMETER">name</replac
     <term><replaceable class="PARAMETER">name</replaceable></term>
     <listitem>
      <para>
-      The name of the specific database, table, or index to be
-      reindexed.  Table and index names may be schema-qualified.
-      Presently, <command>REINDEX DATABASE</> can only reindex the current
-      database, so its parameter must match the current database's name.
+      The name of the specific index, table, or database to be
+      reindexed.  Index and table names may be schema-qualified.
+      Presently, <command>REINDEX DATABASE</> and <command>REINDEX SYSTEM</>
+      can only reindex the current database, so their parameter must match
+      the current database's name. 
      </para>
     </listitem>
    </varlistentry>
@@ -139,10 +151,10 @@ REINDEX { DATABASE | TABLE | INDEX } <replaceable class="PARAMETER">name</replac
    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</>,
+   Then, <command>REINDEX DATABASE</>, <command>REINDEX SYSTEM</>,
    <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
+   doubt, use <command>REINDEX SYSTEM</> 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
@@ -200,6 +212,21 @@ REINDEX { DATABASE | TABLE | INDEX } <replaceable class="PARAMETER">name</replac
   </para>
 
   <para>
+   Reindexing a single index or table requires being the owner of that
+   index or table.  Reindexing a database requires being the owner of
+   the database (note that the owner can therefore rebuild indexes of
+   tables owned by other users).  Of course, superusers can always
+   reindex anything.
+  </para>
+
+  <para>
+   Prior to <productname>PostgreSQL</productname> 8.1, <command>REINDEX
+   DATABASE</> processed only system indexes, not all indexes as one would
+   expect from the name.  This has been changed to reduce the surprise
+   factor.  The old behavior is available as <command>REINDEX SYSTEM</>.
+  </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
@@ -227,8 +254,8 @@ REINDEX INDEX my_index;
   </para>
 
   <para>
-   Rebuild all system indexes in a particular database, without trusting them
-   to be valid already:
+   Rebuild all indexes in a particular database, without trusting the
+   system indexes to be valid already:
 
 <programlisting>
 $ <userinput>export PGOPTIONS="-P"</userinput>