Add ALTER TABLESPACE ... MOVE command

This adds a 'MOVE' sub-command to ALTER TABLESPACE which allows moving sets of
objects from one tablespace to another.  This can be extremely handy and avoids
a lot of error-prone scripting.  ALTER TABLESPACE ... MOVE will only move
objects the user owns, will notify the user if no objects were found, and can
be used to move ALL objects or specific types of objects (TABLES, INDEXES, or
MATERIALIZED VIEWS).

1 files changed, 55 insertions, 4 deletions

diff --git a/doc/src/sgml/ref/alter_tablespace.sgml b/doc/src/sgml/ref/alter_tablespace.sgml
index 7d3ee2ce813..0dfa4652fd7 100644
--- a/doc/src/sgml/ref/alter_tablespace.sgml
+++ b/doc/src/sgml/ref/alter_tablespace.sgml
@@ -12,7 +12,7 @@ PostgreSQL documentation
 
  <refnamediv>
   <refname>ALTER TABLESPACE</refname>
-  <refpurpose>change the definition of a tablespace</refpurpose>
+  <refpurpose>change the definition of a tablespace or affect objects of a tablespace</refpurpose>
  </refnamediv>
 
  <indexterm zone="sql-altertablespace">
@@ -25,6 +25,7 @@ ALTER TABLESPACE <replaceable>name</replaceable> RENAME TO <replaceable>new_name
 ALTER TABLESPACE <replaceable>name</replaceable> OWNER TO <replaceable>new_owner</replaceable>
 ALTER TABLESPACE <replaceable>name</replaceable> SET ( <replaceable class="PARAMETER">tablespace_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
 ALTER TABLESPACE <replaceable>name</replaceable> RESET ( <replaceable class="PARAMETER">tablespace_option</replaceable> [, ... ] )
+ALTER TABLESPACE <replaceable>name</replaceable> MOVE { ALL | TABLES | INDEXES | MATERIALIZED VIEWS } TO <replaceable>new_tablespace</replaceable> [ NOWAIT ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -32,15 +33,34 @@ ALTER TABLESPACE <replaceable>name</replaceable> RESET ( <replaceable class="PAR
   <title>Description</title>
 
   <para>
-   <command>ALTER TABLESPACE</command> changes the definition of
-   a tablespace.
+   <command>ALTER TABLESPACE</command> can be used to change the definition of
+   a tablespace or to migrate all of the objects in the current database which
+   are owned by the user out of a given tablespace.
   </para>
 
   <para>
-   You must own the tablespace to use <command>ALTER TABLESPACE</>.
+   You must own the tablespace to change the definition of a tablespace.
    To alter the owner, you must also be a direct or indirect member of the new
    owning role.
    (Note that superusers have these privileges automatically.)
+
+   Users may use ALTER TABLESPACE ... MOVE to move either ALL of their objects,
+   or just TABLES, INDEXES, or MATERIALIZED VIEWS, but they must have CREATE
+   rights on the new tablespace and only objects, directly or indirectly, owned
+   by the user will be moved.  Note that the superuser is considered an owner
+   of all objects and therefore an ALTER TABLESPACE ... MOVE ALL issued by the
+   superuser will move all objects in the current database which are in the
+   tablespace.
+
+   All objects to be moved will be locked immediately by the command.  The
+   NOWAIT option, if specified, will cause the command to fail if it is unable
+   to acquire the locks.
+
+   System catalogs will not be moved by this command- individuals wishing to
+   move a whole database should use ALTER DATABASE, or call ALTER TABLE on the
+   individual system catalogs.  Note that relations in <literal>information_schema</literal>
+   will be moved, just as any other normal database objects, if the user is the
+   superuser or considered an owner of the relations in <literal>information_schema</literal>.
   </para>
  </refsect1>
 
@@ -94,6 +114,30 @@ ALTER TABLESPACE <replaceable>name</replaceable> RESET ( <replaceable class="PAR
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">new_tablespace</replaceable></term>
+    <listitem>
+     <para>
+      The name of the tablespace to move objects into.  The user must have
+      CREATE rights on the new tablespace to move objects into that
+      tablespace, unless the tablespace being moved into is the default
+      tablespace for the database connected to.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">NOWAIT</replaceable></term>
+    <listitem>
+     <para>
+      The NOWAIT option causes the ALTER TABLESPACE command to fail immediately
+      if it is unable to acquire the necessary lock on all of the objects being
+      move.
+     </para>
+    </listitem>
+   </varlistentry>
+
   </variablelist>
  </refsect1>
 
@@ -112,6 +156,13 @@ ALTER TABLESPACE index_space RENAME TO fast_raid;
 <programlisting>
 ALTER TABLESPACE index_space OWNER TO mary;
 </programlisting></para>
+
+  <para>
+   Move all of the objects which I own from the default tablespace to
+   the <literal>fast_raid</literal> tablespace:
+<programlisting>
+ALTER TABLESPACE pg_default MOVE ALL TO fast_raid;
+</programlisting></para>
  </refsect1>
 
  <refsect1>