Skip to content

Commit 3c4cf08

Browse files
sfrostsfrost
committed
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.
1 parent f577919 commit 3c4cf08

File tree

18 files changed

+305
-392
lines changed

18 files changed

+305
-392
lines changed

‎doc/src/sgml/ref/alter_index.sgml

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,8 @@ ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> RENA
2525
ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> SET TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable>
2626
ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
2727
ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )
28+
ALTER INDEX ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable> [ OWNED BY <replaceable class="PARAMETER">role_name</replaceable> [, ... ] ]
29+
SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable> [ NOWAIT ]
2830
</synopsis>
2931
</refsynopsisdiv>
3032

@@ -63,6 +65,17 @@ ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> RESE
6365
<para>
6466
This form changes the index's tablespace to the specified tablespace and
6567
moves the data file(s) associated with the index to the new tablespace.
68+
To change the tablespace of an index, you must own the index and have
69+
<literal>CREATE</literal> privilege on the new tablespace.
70+
All indexes in the current database in a tablespace can be moved by using
71+
the <literal>ALL IN TABLESPACE</literal> form, which will lock all
72+
indexes to be moved and then move each one. This form also supports
73+
<literal>OWNED BY</literal>, which will only move indexes owned by the
74+
roles specified. If the <literal>NOWAIT</literal> option is specified
75+
then the command will fail if it is unable to acquire all of the locks
76+
required immediately. Note that system catalogs will not be moved by
77+
this command, use <command>ALTER DATABASE</command> or explicit
78+
<command>ALTER INDEX</command> invocations instead if desired.
6679
See also
6780
<xref linkend="SQL-CREATETABLESPACE">.
6881
</para>

‎doc/src/sgml/ref/alter_materialized_view.sgml

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,8 @@ ALTER MATERIALIZED VIEW [ IF EXISTS ] <replaceable class="parameter">name</repla
2929
RENAME TO <replaceable class="parameter">new_name</replaceable>
3030
ALTER MATERIALIZED VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
3131
SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
32+
ALTER MATERIALIZED VIEW ALL IN TABLESPACE <replaceable class="parameter">name</replaceable> [ OWNED BY <replaceable class="PARAMETER">role_name</replaceable> [, ... ] ]
33+
SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable> [ NOWAIT ]
3234

3335
<phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase>
3436

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 17 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,8 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
3131
RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
3232
ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
3333
SET SCHEMA <replaceable class="PARAMETER">new_schema</replaceable>
34+
ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable> [ OWNED BY <replaceable class="PARAMETER">role_name</replaceable> [, ... ] ]
35+
SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable> [ NOWAIT ]
3436

3537
<phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase>
3638

@@ -597,6 +599,17 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
597599
moves the data file(s) associated with the table to the new tablespace.
598600
Indexes on the table, if any, are not moved; but they can be moved
599601
separately with additional <literal>SET TABLESPACE</literal> commands.
602+
All tables in the current database in a tablespace can be moved by using
603+
the <literal>ALL IN TABLESPACE</literal> form, which will lock all tables
604+
to be moved first and then move each one. This form also supports
605+
<literal>OWNED BY</literal>, which will only move tables owned by the
606+
roles specified. If the <literal>NOWAIT</literal> option is specified
607+
then the command will fail if it is unable to acquire all of the locks
608+
required immediately. Note that system catalogs are not moved by this
609+
command, use <command>ALTER DATABASE</command> or explicit
610+
<command>ALTER TABLE</command> invocations instead if desired. The
611+
<literal>information_schema</literal> relations are not considered part
612+
of the system catalogs and will be moved.
600613
See also
601614
<xref linkend="SQL-CREATETABLESPACE">.
602615
</para>
@@ -649,7 +662,8 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
649662
</para>
650663

651664
<para>
652-
All the actions except <literal>RENAME</literal> and <literal>SET SCHEMA</>
665+
All the actions except <literal>RENAME</literal>,
666+
<literal>SET TABLESPACE</literal> and <literal>SET SCHEMA</literal>
653667
can be combined into
654668
a list of multiple alterations to apply in parallel. For example, it
655669
is possible to add several columns and/or alter the type of several
@@ -659,8 +673,8 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
659673

660674
<para>
661675
You must own the table to use <command>ALTER TABLE</>.
662-
To change the schema of a table, you must also have
663-
<literal>CREATE</literal> privilege on the new schema.
676+
To change the schema or tablespace of a table, you must also have
677+
<literal>CREATE</literal> privilege on the new schema or tablespace.
664678
To add the table as a new child of a parent table, you must own the
665679
parent table as well.
666680
To alter the owner, you must also be a direct or indirect member of the new

‎doc/src/sgml/ref/alter_tablespace.sgml

Lines changed: 0 additions & 78 deletions
Original file line numberDiff line numberDiff line change
@@ -25,7 +25,6 @@ ALTER TABLESPACE <replaceable>name</replaceable> RENAME TO <replaceable>new_name
2525
ALTER TABLESPACE <replaceable>name</replaceable> OWNER TO <replaceable>new_owner</replaceable>
2626
ALTER TABLESPACE <replaceable>name</replaceable> SET ( <replaceable class="PARAMETER">tablespace_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
2727
ALTER TABLESPACE <replaceable>name</replaceable> RESET ( <replaceable class="PARAMETER">tablespace_option</replaceable> [, ... ] )
28-
ALTER TABLESPACE <replaceable>name</replaceable> MOVE { ALL | TABLES | INDEXES | MATERIALIZED VIEWS } [ OWNED BY <replaceable class="PARAMETER">role_name</replaceable> [, ...] ] TO <replaceable>new_tablespace</replaceable> [ NOWAIT ]
2928
</synopsis>
3029
</refsynopsisdiv>
3130

@@ -45,44 +44,6 @@ ALTER TABLESPACE <replaceable>name</replaceable> MOVE { ALL | TABLES | INDEXES |
4544
(Note that superusers have these privileges automatically.)
4645
</para>
4746

48-
<para>
49-
<literal>ALTER TABLESPACE ... MOVE</literal> moves objects between
50-
tablespaces. <literal>ALL</literal> will move all tables, indexes and
51-
materialized views; specifying <literal>TABLES</literal> will move only
52-
tables (but not their indexes), <literal>INDEXES</literal> will only move
53-
indexes (including those underneath materialized views, but not tables),
54-
and <literal>MATERIALIZED VIEWS</literal> will only move the table relation
55-
of the materialized view (but no indexes associated with it). Users can
56-
also specify a list of roles whose objects are to be moved, using
57-
<literal>OWNED BY</literal>.
58-
</para>
59-
60-
<para>
61-
Users must have <literal>CREATE</literal> rights on the new tablespace and
62-
be considered an owner (either directly or indirectly) of all objects to be
63-
moved. Note that the superuser is considered an owner of all objects, and
64-
therefore an <literal>ALTER TABLESPACE ... MOVE ALL</literal> issued by the
65-
superuser will move all objects in the current database that are in the
66-
tablespace. (Attempting to move objects without the required rights will
67-
result in an error. Non-superusers can use <literal>OWNED BY</literal> in
68-
such cases, to restrict the set of objects moved to those with the required
69-
rights.)
70-
</para>
71-
72-
<para>
73-
All objects to be moved will be locked immediately by the command. If the
74-
<literal>NOWAIT</literal> is specified, it will cause the command to fail
75-
if it is unable to acquire the locks.
76-
</para>
77-
78-
<para>
79-
System catalogs will not be moved by this command. To move a whole
80-
database, use <command>ALTER DATABASE</command>, or call <command>ALTER
81-
TABLE</command> on the individual system catalogs. Note that relations in
82-
<literal>information_schema</literal> will be moved, just as any other
83-
normal database objects, if the user is the superuser or considered an
84-
owner of the relations in <literal>information_schema</literal>.
85-
</para>
8647
</refsect1>
8748

8849
<refsect1>
@@ -136,38 +97,6 @@ ALTER TABLESPACE <replaceable>name</replaceable> MOVE { ALL | TABLES | INDEXES |
13697
</listitem>
13798
</varlistentry>
13899

139-
<varlistentry>
140-
<term><replaceable class="parameter">role_name</replaceable></term>
141-
<listitem>
142-
<para>
143-
Role whose objects are to be moved.
144-
</para>
145-
</listitem>
146-
</varlistentry>
147-
148-
<varlistentry>
149-
<term><replaceable class="parameter">new_tablespace</replaceable></term>
150-
<listitem>
151-
<para>
152-
The name of the tablespace to move objects into. The user must have
153-
<literal>CREATE</literal> rights on the new tablespace to move objects into that
154-
tablespace, unless the tablespace being moved into is the default
155-
tablespace for the database connected to.
156-
</para>
157-
</listitem>
158-
</varlistentry>
159-
160-
<varlistentry>
161-
<term>NOWAIT</term>
162-
<listitem>
163-
<para>
164-
The <literal>NOWAIT</literal> option causes the <command>ALTER TABLESPACE</command> command to fail immediately
165-
if it is unable to acquire the necessary lock on all of the objects being
166-
moved.
167-
</para>
168-
</listitem>
169-
</varlistentry>
170-
171100
</variablelist>
172101
</refsect1>
173102

@@ -185,13 +114,6 @@ ALTER TABLESPACE index_space RENAME TO fast_raid;
185114
Change the owner of tablespace <literal>index_space</literal>:
186115
<programlisting>
187116
ALTER TABLESPACE index_space OWNER TO mary;
188-
</programlisting></para>
189-
190-
<para>
191-
Move all of the objects from the default tablespace to
192-
the <literal>fast_raid</literal> tablespace:
193-
<programlisting>
194-
ALTER TABLESPACE pg_default MOVE ALL TO fast_raid;
195117
</programlisting></para>
196118
</refsect1>
197119

‎doc/src/sgml/release-9.4.sgml

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1224,7 +1224,10 @@
12241224
<listitem>
12251225
<para>
12261226
Allow moving groups of objects from one tablespace to another
1227-
using <xref linkend="SQL-ALTERTABLESPACE"> ... <literal>MOVE</>
1227+
using <literal>ALL IN TABLESPACE ... SET TABLESPACE</> with
1228+
<link linkend="SQL-ALTERTABLE"><command>ALTER TABLE</></link>
1229+
<link linkend="SQL-ALTERINDEX"><command>ALTER INDEX</></link> and
1230+
<link linkend="SQL-ALTERMATERIALIZEDVIEW"><command>ALTER MATERIALIZED VIEW</></link>
12281231
(Stephen Frost)
12291232
</para>
12301233
</listitem>

‎src/backend/commands/tablecmds.c

Lines changed: 171 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -50,6 +50,7 @@
5050
#include "commands/tablespace.h"
5151
#include "commands/trigger.h"
5252
#include "commands/typecmds.h"
53+
#include "commands/user.h"
5354
#include "executor/executor.h"
5455
#include "foreign/foreign.h"
5556
#include "miscadmin.h"
@@ -9203,6 +9204,176 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
92039204
list_free(reltoastidxids);
92049205
}
92059206

9207+
/*
9208+
* Alter Table ALL ... SET TABLESPACE
9209+
*
9210+
* Allows a user to move all objects of some type in a given tablespace in the
9211+
* current database to another tablespace. Objects can be chosen based on the
9212+
* owner of the object also, to allow users to move only their objects.
9213+
* The user must have CREATE rights on the new tablespace, as usual. The main
9214+
* permissions handling is done by the lower-level table move function.
9215+
*
9216+
* All to-be-moved objects are locked first. If NOWAIT is specified and the
9217+
* lock can't be acquired then we ereport(ERROR).
9218+
*/
9219+
Oid
9220+
AlterTableMoveAll(AlterTableMoveAllStmt *stmt)
9221+
{
9222+
List *relations = NIL;
9223+
ListCell *l;
9224+
ScanKeyData key[1];
9225+
Relation rel;
9226+
HeapScanDesc scan;
9227+
HeapTuple tuple;
9228+
Oid orig_tablespaceoid;
9229+
Oid new_tablespaceoid;
9230+
List *role_oids = roleNamesToIds(stmt->roles);
9231+
9232+
/* Ensure we were not asked to move something we can't */
9233+
if (stmt->objtype != OBJECT_TABLE && stmt->objtype != OBJECT_INDEX &&
9234+
stmt->objtype != OBJECT_MATVIEW)
9235+
ereport(ERROR,
9236+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
9237+
errmsg("only tables, indexes, and materialized views exist in tablespaces")));
9238+
9239+
/* Get the orig and new tablespace OIDs */
9240+
orig_tablespaceoid = get_tablespace_oid(stmt->orig_tablespacename, false);
9241+
new_tablespaceoid = get_tablespace_oid(stmt->new_tablespacename, false);
9242+
9243+
/* Can't move shared relations in to or out of pg_global */
9244+
/* This is also checked by ATExecSetTableSpace, but nice to stop earlier */
9245+
if (orig_tablespaceoid == GLOBALTABLESPACE_OID ||
9246+
new_tablespaceoid == GLOBALTABLESPACE_OID)
9247+
ereport(ERROR,
9248+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
9249+
errmsg("cannot move relations in to or out of pg_global tablespace")));
9250+
9251+
/*
9252+
* Must have CREATE rights on the new tablespace, unless it is the
9253+
* database default tablespace (which all users implicitly have CREATE
9254+
* rights on).
9255+
*/
9256+
if (OidIsValid(new_tablespaceoid) && new_tablespaceoid != MyDatabaseTableSpace)
9257+
{
9258+
AclResult aclresult;
9259+
9260+
aclresult = pg_tablespace_aclcheck(new_tablespaceoid, GetUserId(),
9261+
ACL_CREATE);
9262+
if (aclresult != ACLCHECK_OK)
9263+
aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
9264+
get_tablespace_name(new_tablespaceoid));
9265+
}
9266+
9267+
/*
9268+
* Now that the checks are done, check if we should set either to
9269+
* InvalidOid because it is our database's default tablespace.
9270+
*/
9271+
if (orig_tablespaceoid == MyDatabaseTableSpace)
9272+
orig_tablespaceoid = InvalidOid;
9273+
9274+
if (new_tablespaceoid == MyDatabaseTableSpace)
9275+
new_tablespaceoid = InvalidOid;
9276+
9277+
/* no-op */
9278+
if (orig_tablespaceoid == new_tablespaceoid)
9279+
return new_tablespaceoid;
9280+
9281+
/*
9282+
* Walk the list of objects in the tablespace and move them. This will
9283+
* only find objects in our database, of course.
9284+
*/
9285+
ScanKeyInit(&key[0],
9286+
Anum_pg_class_reltablespace,
9287+
BTEqualStrategyNumber, F_OIDEQ,
9288+
ObjectIdGetDatum(orig_tablespaceoid));
9289+
9290+
rel = heap_open(RelationRelationId, AccessShareLock);
9291+
scan = heap_beginscan_catalog(rel, 1, key);
9292+
while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
9293+
{
9294+
Oid relOid = HeapTupleGetOid(tuple);
9295+
Form_pg_class relForm;
9296+
9297+
relForm = (Form_pg_class) GETSTRUCT(tuple);
9298+
9299+
/*
9300+
* Do not move objects in pg_catalog as part of this, if an admin
9301+
* really wishes to do so, they can issue the individual ALTER
9302+
* commands directly.
9303+
*
9304+
* Also, explicitly avoid any shared tables, temp tables, or TOAST
9305+
* (TOAST will be moved with the main table).
9306+
*/
9307+
if (IsSystemNamespace(relForm->relnamespace) || relForm->relisshared ||
9308+
isAnyTempNamespace(relForm->relnamespace) ||
9309+
relForm->relnamespace == PG_TOAST_NAMESPACE)
9310+
continue;
9311+
9312+
/* Only move the object type requested */
9313+
if ((stmt->objtype == OBJECT_TABLE &&
9314+
relForm->relkind != RELKIND_RELATION) ||
9315+
(stmt->objtype == OBJECT_INDEX &&
9316+
relForm->relkind != RELKIND_INDEX) ||
9317+
(stmt->objtype == OBJECT_MATVIEW &&
9318+
relForm->relkind != RELKIND_MATVIEW))
9319+
continue;
9320+
9321+
/* Check if we are only moving objects owned by certain roles */
9322+
if (role_oids != NIL && !list_member_oid(role_oids, relForm->relowner))
9323+
continue;
9324+
9325+
/*
9326+
* Handle permissions-checking here since we are locking the tables
9327+
* and also to avoid doing a bunch of work only to fail part-way. Note
9328+
* that permissions will also be checked by AlterTableInternal().
9329+
*
9330+
* Caller must be considered an owner on the table to move it.
9331+
*/
9332+
if (!pg_class_ownercheck(relOid, GetUserId()))
9333+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
9334+
NameStr(relForm->relname));
9335+
9336+
if (stmt->nowait &&
9337+
!ConditionalLockRelationOid(relOid, AccessExclusiveLock))
9338+
ereport(ERROR,
9339+
(errcode(ERRCODE_OBJECT_IN_USE),
9340+
errmsg("aborting due to \"%s\".\"%s\" --- lock not available",
9341+
get_namespace_name(relForm->relnamespace),
9342+
NameStr(relForm->relname))));
9343+
else
9344+
LockRelationOid(relOid, AccessExclusiveLock);
9345+
9346+
/* Add to our list of objects to move */
9347+
relations = lappend_oid(relations, relOid);
9348+
}
9349+
9350+
heap_endscan(scan);
9351+
heap_close(rel, AccessShareLock);
9352+
9353+
if (relations == NIL)
9354+
ereport(NOTICE,
9355+
(errcode(ERRCODE_NO_DATA_FOUND),
9356+
errmsg("no matching relations in tablespace \"%s\" found",
9357+
orig_tablespaceoid == InvalidOid ? "(database default)" :
9358+
get_tablespace_name(orig_tablespaceoid))));
9359+
9360+
/* Everything is locked, loop through and move all of the relations. */
9361+
foreach(l, relations)
9362+
{
9363+
List *cmds = NIL;
9364+
AlterTableCmd *cmd = makeNode(AlterTableCmd);
9365+
9366+
cmd->subtype = AT_SetTableSpace;
9367+
cmd->name = stmt->new_tablespacename;
9368+
9369+
cmds = lappend(cmds, cmd);
9370+
9371+
AlterTableInternal(lfirst_oid(l), cmds, false);
9372+
}
9373+
9374+
return new_tablespaceoid;
9375+
}
9376+
92069377
/*
92079378
* Copy data, block by block
92089379
*/

0 commit comments

Comments
 (0)