Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit98e8b48

Browse files
committed
Create 'default_tablespace' GUC variable that supplies a TABLESPACE
clause implicitly whenever one is not given explicitly. Remove conceptof a schema having an associated tablespace, and simplify the rules forselecting a default tablespace for a table or index. It's now just(a) explicit TABLESPACE clause; (b) default_tablespace if that's not anempty string; (c) database's default. This will allow pg_dump to useSET commands instead of tablespace clauses to determine object locations(but I didn't actually make it do so). All per recent discussions.
1 parent0ed3c76 commit98e8b48

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
chartodo[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
chartodo[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
chartodo[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,andindexes can all be assigned to
398+
Tables, indexes,andentire 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 foreverything else as well, unless
448-
explicit<literal>TABLESPACE</>clauses are used somewhere along the
449-
line).
436+
will be the default tablespace forother 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:51neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_tablespace.sgml,v 1.5 2004/11/05 19:15:51tgl 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>CREATESCHEMA</>, <command>CREATE TABLE</>,
44+
<replaceable class="parameter">tablespacename</> to
45+
<command>CREATEDATABASE</>, <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, orschema) to
55+
an object (table, view, sequence, database, function,
56+
procedural language,schema,ortablespace) 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)
[8]ページ先頭
©2009-2025 Movatter.jp