From 98e8b4805324d8ba0b196b8ffaafd5ddd3051ea1 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 5 Nov 2004 19:17:13 +0000 Subject: 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. --- doc/src/sgml/catalogs.sgml | 13 +------- doc/src/sgml/manage-ag.sgml | 55 +++++++++++++-------------------- doc/src/sgml/ref/create_index.sgml | 6 ++-- doc/src/sgml/ref/create_schema.sgml | 31 +++---------------- doc/src/sgml/ref/create_table.sgml | 16 +++++++--- doc/src/sgml/ref/create_tablespace.sgml | 7 ++--- doc/src/sgml/ref/grant.sgml | 26 ++++++++-------- doc/src/sgml/ref/revoke.sgml | 14 ++++----- doc/src/sgml/release.sgml | 6 ++-- doc/src/sgml/runtime.sgml | 28 ++++++++++++++++- 10 files changed, 95 insertions(+), 107 deletions(-) (limited to 'doc/src') 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 @@ @@ -2404,17 +2404,6 @@ Owner of the namespace - - nsptablespace - oid - pg_tablespace.oid - - 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.) - - - nspacl aclitem[] 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 @@ @@ -395,7 +395,7 @@ CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data'; - 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 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; - 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 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. - - - - 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. - - - - The default tablespace for an index is the tablespace associated - with the table the index is on. + Alternatively, use the parameter: + +SET default_tablespace = space1; +CREATE TABLE foo(i int); + + When default_tablespace is set to anything but an empty + string, it supplies an implicit TABLESPACE clause for + CREATE TABLE and CREATE INDEX commands that + do not have an explicit one. - 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 TABLESPACE clause is given (either explicitly or via + 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. @@ -444,9 +433,9 @@ CREATE TABLE foo(i int) TABLESPACE space1; pg_global tablespace is used for shared system catalogs. The pg_default tablespace is the default tablespace of the template1 and template0 databases (and, therefore, - will be the default tablespace for everything else as well, unless - explicit TABLESPACE clauses are used somewhere along the - line). + will be the default tablespace for other databases as well, unless + overridden by a TABLESPACE clause in CREATE + DATABASE). 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 @@ @@ -186,7 +186,9 @@ CREATE [ UNIQUE ] INDEX name ON The tablespace in which to create the index. If not specified, - the tablespace of the parent table is used. + is used, or the database's + default tablespace if default_tablespace is an empty + string. 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 @@ @@ -20,8 +20,8 @@ PostgreSQL documentation -CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ TABLESPACE tablespace ] [ schema_element [ ... ] ] -CREATE SCHEMA AUTHORIZATION username [ TABLESPACE tablespace ] [ schema_element [ ... ] ] +CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [ ... ] ] +CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ] @@ -82,17 +82,6 @@ CREATE SCHEMA AUTHORIZATION username - - tablespace - - - 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. - - - - schema_element @@ -116,9 +105,7 @@ CREATE SCHEMA AUTHORIZATION username To create a schema, the invoking user must have the CREATE privilege for the current database. - Also, the TABLESPACE option requires having - CREATE privilege for the specified tablespace. - (Of course, superusers bypass these checks.) + (Of course, superusers bypass this check.) @@ -161,15 +148,6 @@ CREATE VIEW hollywood.winners AS - - Create a schema sales whose tables and indexes - will be stored in the tablespace mirrorspace by default: - - -CREATE SCHEMA sales TABLESPACE mirrorspace; - - - @@ -206,7 +184,6 @@ CREATE SCHEMA sales TABLESPACE mirrorspace; - 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 @@ @@ -603,8 +603,11 @@ and table_constraint is: The tablespace 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, + is used, or the database's + default tablespace if default_tablespace is an empty + string. @@ -615,8 +618,11 @@ and table_constraint is: This clause allows selection of the tablespace in which the index associated with a UNIQUE or PRIMARY - KEY constraint will be created. If not supplied, the index - will be created in the same tablespace as the table. + KEY constraint will be created. + If not specified, + is used, or the database's + default tablespace if default_tablespace is an empty + string. 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 @@ @@ -41,8 +41,8 @@ CREATE TABLESPACE tablespacename [ A user with appropriate privileges can pass - tablespacename to CREATE - DATABASE, CREATE SCHEMA, CREATE TABLE, + tablespacename to + CREATE DATABASE, CREATE TABLE, CREATE INDEX or ADD CONSTRAINT to have the data files for these objects stored within the specified tablespace. @@ -130,7 +130,6 @@ CREATE TABLESPACE indexspace OWNER genevieve LOCATION '/data/indexes'; - 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 @@ @@ -29,10 +29,6 @@ GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE dbname [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] -GRANT { CREATE | ALL [ PRIVILEGES ] } - ON TABLESPACE tablespacename [, ...] - TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] - GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ([type, ...]) [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] @@ -44,6 +40,10 @@ GRANT { USAGE | ALL [ PRIVILEGES ] } GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } ON SCHEMA schemaname [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] + +GRANT { CREATE | ALL [ PRIVILEGES ] } + ON TABLESPACE tablespacename [, ...] + TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] @@ -52,8 +52,8 @@ GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } The GRANT 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. @@ -188,17 +188,17 @@ GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } For databases, allows new schemas to be created within the database. - - 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.) - For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema. + + 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.) + 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 @@ @@ -33,12 +33,6 @@ REVOKE [ GRANT OPTION FOR ] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ] -REVOKE [ GRANT OPTION FOR ] - { CREATE | ALL [ PRIVILEGES ] } - ON TABLESPACE tablespacename [, ...] - FROM { username | GROUP groupname | PUBLIC } [, ...] - [ CASCADE | RESTRICT ] - REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ([type, ...]) [, ...] @@ -56,6 +50,12 @@ REVOKE [ GRANT OPTION FOR ] ON SCHEMA schemaname [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ] + +REVOKE [ GRANT OPTION FOR ] + { CREATE | ALL [ PRIVILEGES ] } + ON TABLESPACE tablespacename [, ...] + FROM { username | GROUP groupname | PUBLIC } [, ...] + [ CASCADE | RESTRICT ] 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 @@ @@ -96,8 +96,8 @@ $PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.303 2004/10/24 22:43:56 tgl Exp 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 initlocation and manual symlink management for such tasks. 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 @@ @@ -2720,6 +2720,32 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Win32 + + default_tablespace (string) + default_tablespace + tablespacedefault + + + This variable specifies the default tablespace in which to create + objects (tables and indexes) when a CREATE command does + not explicitly specify a tablespace. + + + + 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, + PostgreSQL will automatically use the default + tablespace of the current database. + + + + For more information on tablespaces, + see . + + + + check_function_bodies (boolean) -- cgit v1.2.3