From d9b2bc45cf75f913490f1b3ce9b9263509b26704 Mon Sep 17 00:00:00 2001 From: Stephen Frost Date: Thu, 21 Aug 2014 19:06:17 -0400 Subject: Rework 'MOVE ALL' to 'ALTER .. ALL IN TABLESPACE' As 'ALTER TABLESPACE .. MOVE ALL' really didn't change the tablespace but instead changed objects inside tablespaces, it made sense to rework the syntax and supporting functions to operate under the 'ALTER (TABLE|INDEX|MATERIALIZED VIEW)' syntax and to be in tablecmds.c. Pointed out by Alvaro, who also suggested the new syntax. Back-patch to 9.4. --- doc/src/sgml/ref/alter_index.sgml | 13 +++++ doc/src/sgml/ref/alter_materialized_view.sgml | 2 + doc/src/sgml/ref/alter_table.sgml | 20 +++++-- doc/src/sgml/ref/alter_tablespace.sgml | 78 --------------------------- doc/src/sgml/release-9.4.sgml | 5 +- 5 files changed, 36 insertions(+), 82 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml index 94a7af0429c..ee3e3de4d6f 100644 --- a/doc/src/sgml/ref/alter_index.sgml +++ b/doc/src/sgml/ref/alter_index.sgml @@ -25,6 +25,8 @@ ALTER INDEX [ IF EXISTS ] name RENA ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] ) ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] ) +ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ] + SET TABLESPACE new_tablespace [ NOWAIT ] @@ -63,6 +65,17 @@ ALTER INDEX [ IF EXISTS ] name RESE This form changes the index's tablespace to the specified tablespace and moves the data file(s) associated with the index to the new tablespace. + To change the tablespace of an index, you must own the index and have + CREATE privilege on the new tablespace. + All indexes in the current database in a tablespace can be moved by using + the ALL IN TABLESPACE form, which will lock all + indexes to be moved and then move each one. This form also supports + OWNED BY, which will only move indexes owned by the + roles specified. If the NOWAIT option is specified + then the command will fail if it is unable to acquire all of the locks + required immediately. Note that system catalogs will not be moved by + this command, use ALTER DATABASE or explicit + ALTER INDEX invocations instead if desired. See also . diff --git a/doc/src/sgml/ref/alter_materialized_view.sgml b/doc/src/sgml/ref/alter_materialized_view.sgml index 1932eeb84d4..b0759fc5dca 100644 --- a/doc/src/sgml/ref/alter_materialized_view.sgml +++ b/doc/src/sgml/ref/alter_materialized_view.sgml @@ -29,6 +29,8 @@ ALTER MATERIALIZED VIEW [ IF EXISTS ] namenew_name ALTER MATERIALIZED VIEW [ IF EXISTS ] name SET SCHEMA new_schema +ALTER MATERIALIZED VIEW ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ] + SET TABLESPACE new_tablespace [ NOWAIT ] where action is one of: diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 69a1e14bce3..0e7b99c934c 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -31,6 +31,8 @@ ALTER TABLE [ IF EXISTS ] name RENAME TO new_name ALTER TABLE [ IF EXISTS ] name SET SCHEMA new_schema +ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ] + SET TABLESPACE new_tablespace [ NOWAIT ] where action is one of: @@ -597,6 +599,17 @@ ALTER TABLE [ IF EXISTS ] name moves the data file(s) associated with the table to the new tablespace. Indexes on the table, if any, are not moved; but they can be moved separately with additional SET TABLESPACE commands. + All tables in the current database in a tablespace can be moved by using + the ALL IN TABLESPACE form, which will lock all tables + to be moved first and then move each one. This form also supports + OWNED BY, which will only move tables owned by the + roles specified. If the NOWAIT option is specified + then the command will fail if it is unable to acquire all of the locks + required immediately. Note that system catalogs are not moved by this + command, use ALTER DATABASE or explicit + ALTER TABLE invocations instead if desired. The + information_schema relations are not considered part + of the system catalogs and will be moved. See also . @@ -649,7 +662,8 @@ ALTER TABLE [ IF EXISTS ] name - All the actions except RENAME and SET SCHEMA + All the actions except RENAME, + SET TABLESPACE and SET SCHEMA can be combined into a list of multiple alterations to apply in parallel. For example, it is possible to add several columns and/or alter the type of several @@ -659,8 +673,8 @@ ALTER TABLE [ IF EXISTS ] name You must own the table to use ALTER TABLE. - To change the schema of a table, you must also have - CREATE privilege on the new schema. + To change the schema or tablespace of a table, you must also have + CREATE privilege on the new schema or tablespace. To add the table as a new child of a parent table, you must own the parent table as well. To alter the owner, you must also be a direct or indirect member of the new diff --git a/doc/src/sgml/ref/alter_tablespace.sgml b/doc/src/sgml/ref/alter_tablespace.sgml index bd1afb4b727..7c4aabc5826 100644 --- a/doc/src/sgml/ref/alter_tablespace.sgml +++ b/doc/src/sgml/ref/alter_tablespace.sgml @@ -25,7 +25,6 @@ ALTER TABLESPACE name RENAME TO new_name ALTER TABLESPACE name OWNER TO new_owner ALTER TABLESPACE name SET ( tablespace_option = value [, ... ] ) ALTER TABLESPACE name RESET ( tablespace_option [, ... ] ) -ALTER TABLESPACE name MOVE { ALL | TABLES | INDEXES | MATERIALIZED VIEWS } [ OWNED BY role_name [, ...] ] TO new_tablespace [ NOWAIT ] @@ -45,44 +44,6 @@ ALTER TABLESPACE name MOVE { ALL | TABLES | INDEXES | (Note that superusers have these privileges automatically.) - - ALTER TABLESPACE ... MOVE moves objects between - tablespaces. ALL will move all tables, indexes and - materialized views; specifying TABLES will move only - tables (but not their indexes), INDEXES will only move - indexes (including those underneath materialized views, but not tables), - and MATERIALIZED VIEWS will only move the table relation - of the materialized view (but no indexes associated with it). Users can - also specify a list of roles whose objects are to be moved, using - OWNED BY. - - - - Users must have CREATE rights on the new tablespace and - be considered an owner (either directly or indirectly) of all objects to 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 that are in the - tablespace. (Attempting to move objects without the required rights will - result in an error. Non-superusers can use OWNED BY in - such cases, to restrict the set of objects moved to those with the required - rights.) - - - - All objects to be moved will be locked immediately by the command. If the - NOWAIT is specified, it will cause the command to fail - if it is unable to acquire the locks. - - - - System catalogs will not be moved by this command. To move a whole - database, use ALTER DATABASE, or call ALTER - TABLE on the individual system catalogs. Note that relations in - information_schema will be moved, just as any other - normal database objects, if the user is the superuser or considered an - owner of the relations in information_schema. - @@ -136,38 +97,6 @@ ALTER TABLESPACE name MOVE { ALL | TABLES | INDEXES | - - role_name - - - Role whose objects are to be moved. - - - - - - new_tablespace - - - 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. - - - - - - NOWAIT - - - 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 - moved. - - - - @@ -185,13 +114,6 @@ ALTER TABLESPACE index_space RENAME TO fast_raid; Change the owner of tablespace index_space: ALTER TABLESPACE index_space OWNER TO mary; - - - - Move all of the objects from the default tablespace to - the fast_raid tablespace: - -ALTER TABLESPACE pg_default MOVE ALL TO fast_raid; diff --git a/doc/src/sgml/release-9.4.sgml b/doc/src/sgml/release-9.4.sgml index e338554995d..5233ed256ae 100644 --- a/doc/src/sgml/release-9.4.sgml +++ b/doc/src/sgml/release-9.4.sgml @@ -1224,7 +1224,10 @@ Allow moving groups of objects from one tablespace to another - using ... MOVE + using ALL IN TABLESPACE ... SET TABLESPACE with + ALTER TABLE + ALTER INDEX and + ALTER MATERIALIZED VIEW (Stephen Frost) -- cgit v1.2.3