Skip to content

Commit 98e8b48

Browse files
tglsfdctglsfdc
committed
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.
1 parent 0ed3c76 commit 98e8b48

37 files changed

+370
-585
lines changed

‎contrib/oid2name/oid2name.c

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -367,7 +367,8 @@ sql_exec_dumpalldbs(PGconn *conn, struct options *opts)
367367
char todo[1024];
368368

369369
/* get the oid and database name from the system pg_database table */
370-
snprintf(todo, 1024, "SELECT d.oid AS \"Oid\", datname AS \"Database Name\", "
370+
snprintf(todo, sizeof(todo),
371+
"SELECT d.oid AS \"Oid\", datname AS \"Database Name\", "
371372
"spcname AS \"Tablespace\" FROM pg_database d JOIN pg_tablespace t ON "
372373
"(dattablespace = t.oid) ORDER BY 2");
373374

@@ -383,7 +384,7 @@ sql_exec_dumpalltables(PGconn *conn, struct options *opts)
383384
char todo[1024];
384385
char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" ";
385386

386-
snprintf(todo, 1024,
387+
snprintf(todo, sizeof(todo),
387388
"SELECT relfilenode as \"Filenode\", relname as \"Table Name\" %s "
388389
"FROM pg_class c "
389390
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "
@@ -393,8 +394,7 @@ sql_exec_dumpalltables(PGconn *conn, struct options *opts)
393394
" %s"
394395
" t.oid = CASE"
395396
" WHEN reltablespace <> 0 THEN reltablespace"
396-
" WHEN n.nsptablespace <> 0 THEN nsptablespace"
397-
" WHEN d.dattablespace <> 0 THEN dattablespace"
397+
" ELSE dattablespace"
398398
" END "
399399
"ORDER BY relname",
400400
opts->extended ? addfields : "",
@@ -451,7 +451,7 @@ sql_exec_searchtables(PGconn *conn, struct options *opts)
451451

452452
/* now build the query */
453453
todo = (char *) myalloc(650 + strlen(qualifiers));
454-
snprintf(todo, 1024,
454+
snprintf(todo, 650 + strlen(qualifiers),
455455
"SELECT relfilenode as \"Filenode\", relname as \"Table Name\" %s\n"
456456
"FROM pg_class c \n"
457457
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \n"
@@ -460,8 +460,7 @@ sql_exec_searchtables(PGconn *conn, struct options *opts)
460460
"WHERE relkind IN ('r', 'i', 'S', 't') AND \n"
461461
" t.oid = CASE\n"
462462
" WHEN reltablespace <> 0 THEN reltablespace\n"
463-
" WHEN n.nsptablespace <> 0 THEN nsptablespace\n"
464-
" WHEN d.dattablespace <> 0 THEN dattablespace\n"
463+
" ELSE dattablespace\n"
465464
" END AND \n"
466465
" (%s) \n"
467466
"ORDER BY relname\n",
@@ -478,7 +477,8 @@ sql_exec_dumpalltbspc(PGconn *conn, struct options *opts)
478477
{
479478
char todo[1024];
480479

481-
snprintf(todo, 1024, "SELECT oid AS \"Oid\", spcname as \"Tablespace Name\"\n"
480+
snprintf(todo, sizeof(todo),
481+
"SELECT oid AS \"Oid\", spcname as \"Tablespace Name\"\n"
482482
"FROM pg_tablespace");
483483

484484
sql_exec(conn, todo, opts->quiet);

‎doc/src/sgml/catalogs.sgml

Lines changed: 1 addition & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
<!--
22
Documentation of the system catalogs, directed toward PostgreSQL developers
3-
$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.90 2004/10/11 17:24:39 tgl Exp $
3+
$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.91 2004/11/05 19:15:48 tgl Exp $
44
-->
55

66
<chapter id="catalogs">
@@ -2404,17 +2404,6 @@
24042404
<entry>Owner of the namespace</entry>
24052405
</row>
24062406

2407-
<row>
2408-
<entry><structfield>nsptablespace</structfield></entry>
2409-
<entry><type>oid</type></entry>
2410-
<entry><literal><link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.oid</literal></entry>
2411-
<entry>
2412-
The default tablespace in which to place relations created in this
2413-
namespace. If zero, the database's default tablespace is implied.
2414-
(Changing this does not affect pre-existing relations.)
2415-
</entry>
2416-
</row>
2417-
24182407
<row>
24192408
<entry><structfield>nspacl</structfield></entry>
24202409
<entry><type>aclitem[]</type></entry>

‎doc/src/sgml/manage-ag.sgml

Lines changed: 22 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/manage-ag.sgml,v 2.35 2004/10/29 02:11:18 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/manage-ag.sgml,v 2.36 2004/11/05 19:15:49 tgl Exp $
33
-->
44

55
<chapter id="managing-databases">
@@ -395,7 +395,7 @@ CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';
395395
</para>
396396

397397
<para>
398-
Databases, schemas, tables, and indexes can all be assigned to
398+
Tables, indexes, and entire databases can be assigned to
399399
particular tablespaces. To do so, a user with the <literal>CREATE</>
400400
privilege on a given tablespace must pass the tablespace name as a
401401
parameter to the relevant command. For example, the following creates
@@ -406,47 +406,36 @@ CREATE TABLE foo(i int) TABLESPACE space1;
406406
</para>
407407

408408
<para>
409-
The tablespace associated with a database is used to store the system
410-
catalogs of that database, as well as any temporary files created by
411-
server processes using that database. Furthermore, it is the default
412-
tablespace selected for any objects created within the database, if
413-
no specific <literal>TABLESPACE</> clause is given when those objects
414-
are created. If a database is created without specifying a tablespace
415-
for it, it uses the same tablespace as the template database it is copied
416-
from.
417-
</para>
418-
419-
<para>
420-
A schema does not in itself occupy any storage (other than a
421-
system catalog entry), so assigning a schema to a tablespace does
422-
not in itself do anything. What this actually does is to set a
423-
default tablespace for tables later created within the schema. If
424-
no tablespace is mentioned when creating a schema, it inherits its
425-
default tablespace from the current database.
426-
</para>
427-
428-
<para>
429-
The default tablespace for an index is the tablespace associated
430-
with the table the index is on.
409+
Alternatively, use the <xref linkend="guc-default-tablespace"> parameter:
410+
<programlisting>
411+
SET default_tablespace = space1;
412+
CREATE TABLE foo(i int);
413+
</programlisting>
414+
When <varname>default_tablespace</> is set to anything but an empty
415+
string, it supplies an implicit <literal>TABLESPACE</> clause for
416+
<command>CREATE TABLE</> and <command>CREATE INDEX</> commands that
417+
do not have an explicit one.
431418
</para>
432419

433420
<para>
434-
Another way to state the above rules is that when a schema, table, or index
435-
is created without specifying a tablespace, the object
436-
inherits its logical parent's tablespace. A schema will be created in the
437-
current database's tablespace; a table will be created in the
438-
tablespace of the schema it is being created in; an index will be created
439-
in the tablespace of the table underlying the index.
421+
The tablespace associated with a database is used to store the system
422+
catalogs of that database, as well as any temporary files created by
423+
server processes using that database. Furthermore, it is the default
424+
tablespace selected for tables and indexes created within the database,
425+
if no <literal>TABLESPACE</> clause is given (either explicitly or via
426+
<varname>default_tablespace</>) when the objects are created.
427+
If a database is created without specifying a tablespace for it,
428+
it uses the same tablespace as the template database it is copied from.
440429
</para>
441430

442431
<para>
443432
Two tablespaces are automatically created by <literal>initdb</>. The
444433
<literal>pg_global</> tablespace is used for shared system catalogs. The
445434
<literal>pg_default</> tablespace is the default tablespace of the
446435
<literal>template1</> and <literal>template0</> databases (and, therefore,
447-
will be the default tablespace for everything else as well, unless
448-
explicit <literal>TABLESPACE</> clauses are used somewhere along the
449-
line).
436+
will be the default tablespace for other databases as well, unless
437+
overridden by a <literal>TABLESPACE</> clause in <command>CREATE
438+
DATABASE</>).
450439
</para>
451440

452441
<para>

‎doc/src/sgml/ref/create_index.sgml

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.49 2004/07/12 01:22:53 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.50 2004/11/05 19:15:51 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -186,7 +186,9 @@ CREATE [ UNIQUE ] INDEX <replaceable class="parameter">name</replaceable> ON <re
186186
<listitem>
187187
<para>
188188
The tablespace in which to create the index. If not specified,
189-
the tablespace of the parent table is used.
189+
<xref linkend="guc-default-tablespace"> is used, or the database's
190+
default tablespace if <varname>default_tablespace</> is an empty
191+
string.
190192
</para>
191193
</listitem>
192194
</varlistentry>

‎doc/src/sgml/ref/create_schema.sgml

Lines changed: 4 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_schema.sgml,v 1.15 2004/09/01 14:09:19 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_schema.sgml,v 1.16 2004/11/05 19:15:51 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -20,8 +20,8 @@ PostgreSQL documentation
2020

2121
<refsynopsisdiv>
2222
<synopsis>
23-
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> [ ... ] ]
24-
CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">username</replaceable> [ TABLESPACE <replaceable class="parameter">tablespace</replaceable> ] [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
23+
CREATE SCHEMA <replaceable class="parameter">schemaname</replaceable> [ AUTHORIZATION <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
24+
CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">username</replaceable> [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
2525
</synopsis>
2626
</refsynopsisdiv>
2727

@@ -82,17 +82,6 @@ CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">username</replaceable
8282
</listitem>
8383
</varlistentry>
8484

85-
<varlistentry>
86-
<term><replaceable class="parameter">tablespace</replaceable></term>
87-
<listitem>
88-
<para>
89-
The name of the tablespace that is to be the default tablespace
90-
for all new objects created in the schema. If not supplied, the schema
91-
will inherit the default tablespace of the database.
92-
</para>
93-
</listitem>
94-
</varlistentry>
95-
9685
<varlistentry>
9786
<term><replaceable class="parameter">schema_element</replaceable></term>
9887
<listitem>
@@ -116,9 +105,7 @@ CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">username</replaceable
116105
<para>
117106
To create a schema, the invoking user must have the
118107
<literal>CREATE</> privilege for the current database.
119-
Also, the <literal>TABLESPACE</> option requires having
120-
<literal>CREATE</> privilege for the specified tablespace.
121-
(Of course, superusers bypass these checks.)
108+
(Of course, superusers bypass this check.)
122109
</para>
123110
</refsect1>
124111

@@ -161,15 +148,6 @@ CREATE VIEW hollywood.winners AS
161148
</programlisting>
162149
</para>
163150

164-
<para>
165-
Create a schema <literal>sales</> whose tables and indexes
166-
will be stored in the tablespace <literal>mirrorspace</> by default:
167-
168-
<programlisting>
169-
CREATE SCHEMA sales TABLESPACE mirrorspace;
170-
</programlisting>
171-
</para>
172-
173151
</refsect1>
174152

175153
<refsect1>
@@ -206,7 +184,6 @@ CREATE SCHEMA sales TABLESPACE mirrorspace;
206184
<simplelist type="inline">
207185
<member><xref linkend="sql-alterschema" endterm="sql-alterschema-title"></member>
208186
<member><xref linkend="sql-dropschema" endterm="sql-dropschema-title"></member>
209-
<member><xref linkend="sql-createtablespace" endterm="sql-createtablespace-title"></member>
210187
</simplelist>
211188
</refsect1>
212189

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 11 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.85 2004/10/21 21:33:59 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.86 2004/11/05 19:15:51 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -603,8 +603,11 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
603603
<listitem>
604604
<para>
605605
The <replaceable class="PARAMETER">tablespace</replaceable> is the name
606-
of the tablespace in which the new table is to be created. If not
607-
supplied, the default tablespace of the table's schema will be used.
606+
of the tablespace in which the new table is to be created.
607+
If not specified,
608+
<xref linkend="guc-default-tablespace"> is used, or the database's
609+
default tablespace if <varname>default_tablespace</> is an empty
610+
string.
608611
</para>
609612
</listitem>
610613
</varlistentry>
@@ -615,8 +618,11 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
615618
<para>
616619
This clause allows selection of the tablespace in which the index
617620
associated with a <literal>UNIQUE</literal> or <literal>PRIMARY
618-
KEY</literal> constraint will be created. If not supplied, the index
619-
will be created in the same tablespace as the table.
621+
KEY</literal> constraint will be created.
622+
If not specified,
623+
<xref linkend="guc-default-tablespace"> is used, or the database's
624+
default tablespace if <varname>default_tablespace</> is an empty
625+
string.
620626
</para>
621627
</listitem>
622628
</varlistentry>

‎doc/src/sgml/ref/create_tablespace.sgml

Lines changed: 3 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_tablespace.sgml,v 1.4 2004/08/24 00:06:51 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_tablespace.sgml,v 1.5 2004/11/05 19:15:51 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -41,8 +41,8 @@ CREATE TABLESPACE <replaceable class="parameter">tablespacename</replaceable> [
4141

4242
<para>
4343
A user with appropriate privileges can pass
44-
<replaceable class="parameter">tablespacename</> to <command>CREATE
45-
DATABASE</>, <command>CREATE SCHEMA</>, <command>CREATE TABLE</>,
44+
<replaceable class="parameter">tablespacename</> to
45+
<command>CREATE DATABASE</>, <command>CREATE TABLE</>,
4646
<command>CREATE INDEX</> or <command>ADD CONSTRAINT</> to have the data
4747
files for these objects stored within the specified tablespace.
4848
</para>
@@ -130,7 +130,6 @@ CREATE TABLESPACE indexspace OWNER genevieve LOCATION '/data/indexes';
130130

131131
<simplelist type="inline">
132132
<member><xref linkend="sql-createdatabase" endterm="sql-createdatabase-title"></member>
133-
<member><xref linkend="sql-createschema" endterm="sql-createschema-title"></member>
134133
<member><xref linkend="sql-createtable" endterm="sql-createtable-title"></member>
135134
<member><xref linkend="sql-createindex" endterm="sql-createindex-title"></member>
136135
<member><xref linkend="sql-droptablespace" endterm="sql-droptablespace-title"></member>

‎doc/src/sgml/ref/grant.sgml

Lines changed: 13 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.43 2004/09/01 04:13:11 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.44 2004/11/05 19:15:51 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -29,10 +29,6 @@ GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
2929
ON DATABASE <replaceable>dbname</replaceable> [, ...]
3030
TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
3131

32-
GRANT { CREATE | ALL [ PRIVILEGES ] }
33-
ON TABLESPACE <replaceable>tablespacename</> [, ...]
34-
TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
35-
3632
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
3733
ON FUNCTION <replaceable>funcname</replaceable> ([<replaceable>type</replaceable>, ...]) [, ...]
3834
TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
@@ -44,6 +40,10 @@ GRANT { USAGE | ALL [ PRIVILEGES ] }
4440
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
4541
ON SCHEMA <replaceable>schemaname</replaceable> [, ...]
4642
TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
43+
44+
GRANT { CREATE | ALL [ PRIVILEGES ] }
45+
ON TABLESPACE <replaceable>tablespacename</> [, ...]
46+
TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
4747
</synopsis>
4848
</refsynopsisdiv>
4949

@@ -52,8 +52,8 @@ GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
5252

5353
<para>
5454
The <command>GRANT</command> command gives specific privileges on
55-
an object (table, view, sequence, database, tablespace, function,
56-
procedural language, or schema) to
55+
an object (table, view, sequence, database, function,
56+
procedural language, schema, or tablespace) to
5757
one or more users or groups of users. These privileges are added
5858
to those already granted, if any.
5959
</para>
@@ -188,17 +188,17 @@ GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
188188
<para>
189189
For databases, allows new schemas to be created within the database.
190190
</para>
191-
<para>
192-
For tablespaces, allows tables to be created within the tablespace,
193-
and allows databases and schemas to be created that have the tablespace
194-
as their default tablespace. (Note that revoking this privilege
195-
will not alter the behavior of existing databases and schemas.)
196-
</para>
197191
<para>
198192
For schemas, allows new objects to be created within the schema.
199193
To rename an existing object, you must own the object <emphasis>and</>
200194
have this privilege for the containing schema.
201195
</para>
196+
<para>
197+
For tablespaces, allows tables and indexes to be created within the
198+
tablespace, and allows databases to be created that have the tablespace
199+
as their default tablespace. (Note that revoking this privilege
200+
will not alter the placement of existing objects.)
201+
</para>
202202
</listitem>
203203
</varlistentry>
204204

0 commit comments

Comments
 (0)