Create 'default_tablespace' GUC variable that supplies a TABLESPACE

clause implicitly whenever one is not given explicitly.  Remove concept
of a schema having an associated tablespace, and simplify the rules for
selecting a default tablespace for a table or index.  It's now just
(a) explicit TABLESPACE clause; (b) default_tablespace if that's not an
empty string; (c) database's default.  This will allow pg_dump to use
SET commands instead of tablespace clauses to determine object locations
(but I didn't actually make it do so).  All per recent discussions.

10 files changed, 95 insertions, 107 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index aef5a751bb6..e7cd4ec7e4a 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1,6 +1,6 @@
 <!--
  Documentation of the system catalogs, directed toward PostgreSQL developers
- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.90 2004/10/11 17:24:39 tgl Exp $
+ $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.91 2004/11/05 19:15:48 tgl Exp $
  -->
 
 <chapter id="catalogs">
@@ -2405,17 +2405,6 @@
      </row>
 
      <row>
-      <entry><structfield>nsptablespace</structfield></entry>
-      <entry><type>oid</type></entry>
-      <entry><literal><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.oid</literal></entry>
-      <entry>
-       The default tablespace in which to place relations created in this
-       namespace.  If zero, the database's default tablespace is implied.
-       (Changing this does not affect pre-existing relations.)
-      </entry>
-     </row>
-
-     <row>
       <entry><structfield>nspacl</structfield></entry>
       <entry><type>aclitem[]</type></entry>
       <entry></entry>
diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml
index 5db8e939e4d..0237e026b19 100644
--- a/doc/src/sgml/manage-ag.sgml
+++ b/doc/src/sgml/manage-ag.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/manage-ag.sgml,v 2.35 2004/10/29 02:11:18 neilc Exp $
+$PostgreSQL: pgsql/doc/src/sgml/manage-ag.sgml,v 2.36 2004/11/05 19:15:49 tgl Exp $
 -->
 
 <chapter id="managing-databases">
@@ -395,7 +395,7 @@ CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';
    </para>
 
    <para>
-    Databases, schemas, tables, and indexes can all be assigned to
+    Tables, indexes, and entire databases can be assigned to
     particular tablespaces. To do so, a user with the <literal>CREATE</>
     privilege on a given tablespace must pass the tablespace name as a 
     parameter to the relevant command. For example, the following creates 
@@ -406,37 +406,26 @@ CREATE TABLE foo(i int) TABLESPACE space1;
    </para>
 
    <para>
-    The tablespace associated with a database is used to store the system
-    catalogs of that database, as well as any temporary files created by
-    server processes using that database.  Furthermore, it is the default
-    tablespace selected for any objects created within the database, if
-    no specific <literal>TABLESPACE</> clause is given when those objects
-    are created.  If a database is created without specifying a tablespace
-    for it, it uses the same tablespace as the template database it is copied
-    from.
-   </para>
-
-   <para>
-    A schema does not in itself occupy any storage (other than a
-    system catalog entry), so assigning a schema to a tablespace does
-    not in itself do anything.  What this actually does is to set a
-    default tablespace for tables later created within the schema.  If
-    no tablespace is mentioned when creating a schema, it inherits its
-    default tablespace from the current database.
-   </para>
-
-   <para>
-    The default tablespace for an index is the tablespace associated
-    with the table the index is on.
+    Alternatively, use the <xref linkend="guc-default-tablespace"> parameter:
+<programlisting>
+SET default_tablespace = space1;
+CREATE TABLE foo(i int);
+</programlisting>
+    When <varname>default_tablespace</> is set to anything but an empty
+    string, it supplies an implicit <literal>TABLESPACE</> clause for
+    <command>CREATE TABLE</> and <command>CREATE INDEX</> commands that
+    do not have an explicit one.
    </para>
 
    <para>
-    Another way to state the above rules is that when a schema, table, or index
-    is created without specifying a tablespace, the object
-    inherits its logical parent's tablespace. A schema will be created in the
-    current database's tablespace; a table will be created in the
-    tablespace of the schema it is being created in; an index will be created
-    in the tablespace of the table underlying the index.
+    The tablespace associated with a database is used to store the system
+    catalogs of that database, as well as any temporary files created by
+    server processes using that database.  Furthermore, it is the default
+    tablespace selected for tables and indexes created within the database,
+    if no <literal>TABLESPACE</> clause is given (either explicitly or via
+    <varname>default_tablespace</>) when the objects are created.
+    If a database is created without specifying a tablespace for it,
+    it uses the same tablespace as the template database it is copied from.
    </para>
 
    <para>
@@ -444,9 +433,9 @@ CREATE TABLE foo(i int) TABLESPACE space1;
     <literal>pg_global</> tablespace is used for shared system catalogs. The
     <literal>pg_default</> tablespace is the default tablespace of the
     <literal>template1</> and <literal>template0</> databases (and, therefore,
-    will be the default tablespace for everything else as well, unless
-    explicit <literal>TABLESPACE</> clauses are used somewhere along the
-    line).
+    will be the default tablespace for other databases as well, unless
+    overridden by a <literal>TABLESPACE</> clause in <command>CREATE
+    DATABASE</>).
    </para>
 
    <para>
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index c45df0c5be0..7f53ad24b5a 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.49 2004/07/12 01:22:53 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.50 2004/11/05 19:15:51 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -186,7 +186,9 @@ CREATE [ UNIQUE ] INDEX <replaceable class="parameter">name</replaceable> ON <re
       <listitem>
        <para>
         The tablespace in which to create the index.  If not specified,
-        the tablespace of the parent table is used.
+        <xref linkend="guc-default-tablespace"> is used, or the database's
+        default tablespace if <varname>default_tablespace</> is an empty
+        string.
        </para>
       </listitem>
      </varlistentry>
diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml
index 188d53b68ed..195ab010423 100644
--- a/doc/src/sgml/ref/create_schema.sgml
+++ b/doc/src/sgml/ref/create_schema.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_schema.sgml,v 1.15 2004/09/01 14:09:19 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_schema.sgml,v 1.16 2004/11/05 19:15:51 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -20,8 +20,8 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-CREATE SCHEMA <replaceable class="parameter">schemaname</replaceable> [ AUTHORIZATION <replaceable class="parameter">username</replaceable> ] [ TABLESPACE <replaceable class="parameter">tablespace</replaceable> ] [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
-CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">username</replaceable> [ TABLESPACE <replaceable class="parameter">tablespace</replaceable> ] [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
+CREATE SCHEMA <replaceable class="parameter">schemaname</replaceable> [ AUTHORIZATION <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
+CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">username</replaceable> [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -83,17 +83,6 @@ CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">username</replaceable
      </varlistentry>
 
      <varlistentry>
-      <term><replaceable class="parameter">tablespace</replaceable></term>
-      <listitem>
-       <para>
-        The name of the tablespace that is to be the default tablespace
-        for all new objects created in the schema. If not supplied, the schema
-        will inherit the default tablespace of the database.
-       </para>
-      </listitem>
-     </varlistentry>
-
-     <varlistentry>
       <term><replaceable class="parameter">schema_element</replaceable></term>
       <listitem>
        <para>
@@ -116,9 +105,7 @@ CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">username</replaceable
   <para>
    To create a schema, the invoking user must have the
    <literal>CREATE</> privilege for the current database.
-   Also, the <literal>TABLESPACE</> option requires having
-   <literal>CREATE</> privilege for the specified tablespace.
-   (Of course, superusers bypass these checks.)
+   (Of course, superusers bypass this check.)
   </para>
  </refsect1>
 
@@ -161,15 +148,6 @@ CREATE VIEW hollywood.winners AS
 </programlisting>
   </para>
 
-  <para>
-   Create a schema <literal>sales</> whose tables and indexes
-   will be stored in the tablespace <literal>mirrorspace</> by default:
-
-<programlisting>
-CREATE SCHEMA sales TABLESPACE mirrorspace;
-</programlisting>
-  </para>
-
  </refsect1>
 
  <refsect1>
@@ -206,7 +184,6 @@ CREATE SCHEMA sales TABLESPACE mirrorspace;
   <simplelist type="inline">
    <member><xref linkend="sql-alterschema" endterm="sql-alterschema-title"></member>
    <member><xref linkend="sql-dropschema" endterm="sql-dropschema-title"></member>
-    <member><xref linkend="sql-createtablespace" endterm="sql-createtablespace-title"></member>
  </simplelist>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index e70f0c3dfd0..8b18d837c96 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.85 2004/10/21 21:33:59 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.86 2004/11/05 19:15:51 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -603,8 +603,11 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
     <listitem>
      <para>
       The <replaceable class="PARAMETER">tablespace</replaceable> is the name
-      of the tablespace in which the new table is to be created. If not
-      supplied, the default tablespace of the table's schema will be used.
+      of the tablespace in which the new table is to be created.
+      If not specified,
+      <xref linkend="guc-default-tablespace"> is used, or the database's
+      default tablespace if <varname>default_tablespace</> is an empty
+      string.
      </para>
     </listitem>
    </varlistentry>
@@ -615,8 +618,11 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
      <para>
       This clause allows selection of the tablespace in which the index
       associated with a <literal>UNIQUE</literal> or <literal>PRIMARY
-      KEY</literal> constraint will be created.  If not supplied, the index
-      will be created in the same tablespace as the table.
+      KEY</literal> constraint will be created.
+      If not specified,
+      <xref linkend="guc-default-tablespace"> is used, or the database's
+      default tablespace if <varname>default_tablespace</> is an empty
+      string.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/create_tablespace.sgml b/doc/src/sgml/ref/create_tablespace.sgml
index 76161eb1aed..87a44d00d0b 100644
--- a/doc/src/sgml/ref/create_tablespace.sgml
+++ b/doc/src/sgml/ref/create_tablespace.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_tablespace.sgml,v 1.4 2004/08/24 00:06:51 neilc Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_tablespace.sgml,v 1.5 2004/11/05 19:15:51 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -41,8 +41,8 @@ CREATE TABLESPACE <replaceable class="parameter">tablespacename</replaceable> [
 
   <para>
    A user with appropriate privileges can pass
-   <replaceable class="parameter">tablespacename</> to <command>CREATE
-   DATABASE</>, <command>CREATE SCHEMA</>, <command>CREATE TABLE</>,
+   <replaceable class="parameter">tablespacename</> to
+   <command>CREATE DATABASE</>, <command>CREATE TABLE</>,
    <command>CREATE INDEX</> or <command>ADD CONSTRAINT</> to have the data
    files for these objects stored within the specified tablespace.
   </para>
@@ -130,7 +130,6 @@ CREATE TABLESPACE indexspace OWNER genevieve LOCATION '/data/indexes';
 
   <simplelist type="inline">
    <member><xref linkend="sql-createdatabase" endterm="sql-createdatabase-title"></member>
-   <member><xref linkend="sql-createschema" endterm="sql-createschema-title"></member>
    <member><xref linkend="sql-createtable" endterm="sql-createtable-title"></member>
    <member><xref linkend="sql-createindex" endterm="sql-createindex-title"></member>
    <member><xref linkend="sql-droptablespace" endterm="sql-droptablespace-title"></member>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index d6a6ef94b6f..97854d55281 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.43 2004/09/01 04:13:11 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.44 2004/11/05 19:15:51 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -29,10 +29,6 @@ GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
     ON DATABASE <replaceable>dbname</replaceable> [, ...]
     TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 
-GRANT { CREATE | ALL [ PRIVILEGES ] }
-    ON TABLESPACE <replaceable>tablespacename</> [, ...]
-    TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
-
 GRANT { EXECUTE | ALL [ PRIVILEGES ] }
     ON FUNCTION <replaceable>funcname</replaceable> ([<replaceable>type</replaceable>, ...]) [, ...]
     TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
@@ -44,6 +40,10 @@ GRANT { USAGE | ALL [ PRIVILEGES ] }
 GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
     ON SCHEMA <replaceable>schemaname</replaceable> [, ...]
     TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
+GRANT { CREATE | ALL [ PRIVILEGES ] }
+    ON TABLESPACE <replaceable>tablespacename</> [, ...]
+    TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -52,8 +52,8 @@ GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
 
   <para>
    The <command>GRANT</command> command gives specific privileges on
-   an object (table, view, sequence, database, tablespace, function,
-   procedural language, or schema) to
+   an object (table, view, sequence, database, function,
+   procedural language, schema, or tablespace) to
    one or more users or groups of users.  These privileges are added
    to those already granted, if any.
   </para>
@@ -189,16 +189,16 @@ GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
        For databases, allows new schemas to be created within the database.
       </para>
       <para>
-       For tablespaces, allows tables to be created within the tablespace,
-       and allows databases and schemas to be created that have the tablespace
-       as their default tablespace.  (Note that revoking this privilege
-       will not alter the behavior of existing databases and schemas.)
-      </para>
-      <para>
        For schemas, allows new objects to be created within the schema.
        To rename an existing object, you must own the object <emphasis>and</>
        have this privilege for the containing schema.
       </para>
+      <para>
+       For tablespaces, allows tables and indexes to be created within the
+       tablespace, and allows databases to be created that have the tablespace
+       as their default tablespace.  (Note that revoking this privilege
+       will not alter the placement of existing objects.)
+      </para>
      </listitem>
     </varlistentry>
 
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index c6cd587ade7..5f94537e26e 100644
--- a/doc/src/sgml/ref/revoke.sgml
+++ b/doc/src/sgml/ref/revoke.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/revoke.sgml,v 1.31 2004/06/18 06:13:05 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/revoke.sgml,v 1.32 2004/11/05 19:15:52 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -34,12 +34,6 @@ REVOKE [ GRANT OPTION FOR ]
     [ CASCADE | RESTRICT ]
 
 REVOKE [ GRANT OPTION FOR ]
-    { CREATE | ALL [ PRIVILEGES ] }
-    ON TABLESPACE <replaceable>tablespacename</replaceable> [, ...]
-    FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...]
-    [ CASCADE | RESTRICT ]
-
-REVOKE [ GRANT OPTION FOR ]
     { EXECUTE | ALL [ PRIVILEGES ] }
     ON FUNCTION <replaceable>funcname</replaceable> ([<replaceable>type</replaceable>, ...]) [, ...]
     FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...]
@@ -56,6 +50,12 @@ REVOKE [ GRANT OPTION FOR ]
     ON SCHEMA <replaceable>schemaname</replaceable> [, ...]
     FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...]
     [ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+    { CREATE | ALL [ PRIVILEGES ] }
+    ON TABLESPACE <replaceable>tablespacename</replaceable> [, ...]
+    FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...]
+    [ CASCADE | RESTRICT ]
 </synopsis>
  </refsynopsisdiv>
 
diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml
index 466754b11ff..1ec93a215f1 100644
--- a/doc/src/sgml/release.sgml
+++ b/doc/src/sgml/release.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.303 2004/10/24 22:43:56 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.304 2004/11/05 19:15:49 tgl Exp $
 -->
 
 <appendix id="release">
@@ -96,8 +96,8 @@ $PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.303 2004/10/24 22:43:56 tgl Exp
       <listitem>
        <para>
         Tablespaces allow administrators to select the file systems
-        used for storage of databases, schemas, tables, or
-        indexes. This improves performance and control over disk space
+        used for storage of tables, indexes, and entire databases.
+        This improves performance and control over disk space
         usage. Prior releases used <application>initlocation</> and
         manual symlink management for such tasks.
        </para>
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
index be2ced298d3..91cdec32284 100644
--- a/doc/src/sgml/runtime.sgml
+++ b/doc/src/sgml/runtime.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.290 2004/11/04 19:08:30 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.291 2004/11/05 19:15:49 tgl Exp $
 -->
 
 <Chapter Id="runtime">
@@ -2720,6 +2720,32 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"'  # Win32
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-default-tablespace" xreflabel="default_tablespace">
+      <term><varname>default_tablespace</varname> (<type>string</type>)</term>
+      <indexterm><primary>default_tablespace</></>
+      <indexterm><primary>tablespace</><secondary>default</></>
+      <listitem>
+       <para>
+        This variable specifies the default tablespace in which to create
+        objects (tables and indexes) when a <command>CREATE</> command does
+        not explicitly specify a tablespace.
+       </para>
+
+       <para>
+        The value is either the name of a tablespace, or an empty string
+        to specify using the default tablespace of the current database.
+        If the value does not match the name of any existing tablespace,
+        <productname>PostgreSQL</> will automatically use the default
+        tablespace of the current database.
+       </para>
+
+       <para>
+        For more information on tablespaces,
+        see <xref linkend="manage-ag-tablespaces">.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
       <term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
       <listitem>