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
Appearance settings

Commit 76e91b3

Browse filesBrowse files
sfrostsfrost
committed
Add ALTER TABLESPACE ... MOVE command
This adds a 'MOVE' sub-command to ALTER TABLESPACE which allows moving sets of objects from one tablespace to another. This can be extremely handy and avoids a lot of error-prone scripting. ALTER TABLESPACE ... MOVE will only move objects the user owns, will notify the user if no objects were found, and can be used to move ALL objects or specific types of objects (TABLES, INDEXES, or MATERIALIZED VIEWS).
1 parent 6f25c62 commit 76e91b3
Copy full SHA for 76e91b3

File tree

Expand file treeCollapse file tree

13 files changed

+340
-8
lines changed
Open diff view settings
Filter options
Expand file treeCollapse file tree

13 files changed

+340
-8
lines changed
Open diff view settings
Collapse file

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/alter_tablespace.sgml
+55-4Lines changed: 55 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@ PostgreSQL documentation
1212

1313
<refnamediv>
1414
<refname>ALTER TABLESPACE</refname>
15-
<refpurpose>change the definition of a tablespace</refpurpose>
15+
<refpurpose>change the definition of a tablespace or affect objects of a tablespace</refpurpose>
1616
</refnamediv>
1717

1818
<indexterm zone="sql-altertablespace">
@@ -25,22 +25,42 @@ 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 } TO <replaceable>new_tablespace</replaceable> [ NOWAIT ]
2829
</synopsis>
2930
</refsynopsisdiv>
3031

3132
<refsect1>
3233
<title>Description</title>
3334

3435
<para>
35-
<command>ALTER TABLESPACE</command> changes the definition of
36-
a tablespace.
36+
<command>ALTER TABLESPACE</command> can be used to change the definition of
37+
a tablespace or to migrate all of the objects in the current database which
38+
are owned by the user out of a given tablespace.
3739
</para>
3840

3941
<para>
40-
You must own the tablespace to use <command>ALTER TABLESPACE</>.
42+
You must own the tablespace to change the definition of a tablespace.
4143
To alter the owner, you must also be a direct or indirect member of the new
4244
owning role.
4345
(Note that superusers have these privileges automatically.)
46+
47+
Users may use ALTER TABLESPACE ... MOVE to move either ALL of their objects,
48+
or just TABLES, INDEXES, or MATERIALIZED VIEWS, but they must have CREATE
49+
rights on the new tablespace and only objects, directly or indirectly, owned
50+
by the user will be moved. Note that the superuser is considered an owner
51+
of all objects and therefore an ALTER TABLESPACE ... MOVE ALL issued by the
52+
superuser will move all objects in the current database which are in the
53+
tablespace.
54+
55+
All objects to be moved will be locked immediately by the command. The
56+
NOWAIT option, if specified, will cause the command to fail if it is unable
57+
to acquire the locks.
58+
59+
System catalogs will not be moved by this command- individuals wishing to
60+
move a whole database should use ALTER DATABASE, or call ALTER TABLE on the
61+
individual system catalogs. Note that relations in <literal>information_schema</literal>
62+
will be moved, just as any other normal database objects, if the user is the
63+
superuser or considered an owner of the relations in <literal>information_schema</literal>.
4464
</para>
4565
</refsect1>
4666

@@ -94,6 +114,30 @@ ALTER TABLESPACE <replaceable>name</replaceable> RESET ( <replaceable class="PAR
94114
</para>
95115
</listitem>
96116
</varlistentry>
117+
118+
<varlistentry>
119+
<term><replaceable class="parameter">new_tablespace</replaceable></term>
120+
<listitem>
121+
<para>
122+
The name of the tablespace to move objects into. The user must have
123+
CREATE rights on the new tablespace to move objects into that
124+
tablespace, unless the tablespace being moved into is the default
125+
tablespace for the database connected to.
126+
</para>
127+
</listitem>
128+
</varlistentry>
129+
130+
<varlistentry>
131+
<term><replaceable class="parameter">NOWAIT</replaceable></term>
132+
<listitem>
133+
<para>
134+
The NOWAIT option causes the ALTER TABLESPACE command to fail immediately
135+
if it is unable to acquire the necessary lock on all of the objects being
136+
move.
137+
</para>
138+
</listitem>
139+
</varlistentry>
140+
97141
</variablelist>
98142
</refsect1>
99143

@@ -111,6 +155,13 @@ ALTER TABLESPACE index_space RENAME TO fast_raid;
111155
Change the owner of tablespace <literal>index_space</literal>:
112156
<programlisting>
113157
ALTER TABLESPACE index_space OWNER TO mary;
158+
</programlisting></para>
159+
160+
<para>
161+
Move all of the objects which I own from the default tablespace to
162+
the <literal>fast_raid</literal> tablespace:
163+
<programlisting>
164+
ALTER TABLESPACE pg_default MOVE ALL TO fast_raid;
114165
</programlisting></para>
115166
</refsect1>
116167

Collapse file

‎src/backend/commands/tablespace.c‎

Copy file name to clipboardExpand all lines: src/backend/commands/tablespace.c
+171Lines changed: 171 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -59,20 +59,25 @@
5959
#include "catalog/catalog.h"
6060
#include "catalog/dependency.h"
6161
#include "catalog/indexing.h"
62+
#include "catalog/namespace.h"
6263
#include "catalog/objectaccess.h"
64+
#include "catalog/pg_namespace.h"
6365
#include "catalog/pg_tablespace.h"
6466
#include "commands/comment.h"
6567
#include "commands/seclabel.h"
68+
#include "commands/tablecmds.h"
6669
#include "commands/tablespace.h"
6770
#include "common/relpath.h"
6871
#include "miscadmin.h"
6972
#include "postmaster/bgwriter.h"
7073
#include "storage/fd.h"
74+
#include "storage/lmgr.h"
7175
#include "storage/standby.h"
7276
#include "utils/acl.h"
7377
#include "utils/builtins.h"
7478
#include "utils/fmgroids.h"
7579
#include "utils/guc.h"
80+
#include "utils/lsyscache.h"
7681
#include "utils/memutils.h"
7782
#include "utils/rel.h"
7883
#include "utils/tqual.h"
@@ -955,6 +960,172 @@ AlterTableSpaceOptions(AlterTableSpaceOptionsStmt *stmt)
955960
return tablespaceoid;
956961
}
957962

963+
/*
964+
* Alter table space move
965+
*
966+
* Allows a user to move all of their objects in a given tablespace in the
967+
* current database to another tablespace. Only objects which the user is
968+
* considered to be an owner of are moved and the user must have CREATE rights
969+
* on the new tablespace. These checks should mean that ALTER TABLE will never
970+
* fail due to permissions, but note that permissions will also be checked at
971+
* that level. Objects can be ALL, TABLES, INDEXES, or MATERIALIZED VIEWS.
972+
*
973+
* All to-be-moved objects are locked first. If NOWAIT is specified and the
974+
* lock can't be acquired then we ereport(ERROR).
975+
*/
976+
Oid
977+
AlterTableSpaceMove(AlterTableSpaceMoveStmt *stmt)
978+
{
979+
List *relations = NIL;
980+
ListCell *l;
981+
ScanKeyData key[1];
982+
Relation rel;
983+
HeapScanDesc scan;
984+
HeapTuple tuple;
985+
Oid orig_tablespaceoid;
986+
Oid new_tablespaceoid;
987+
988+
/* Ensure we were not asked to move something we can't */
989+
if (!stmt->move_all && stmt->objtype != OBJECT_TABLE &&
990+
stmt->objtype != OBJECT_INDEX && stmt->objtype != OBJECT_MATVIEW)
991+
ereport(ERROR,
992+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
993+
errmsg("only tables, indexes, and materialized views exist in tablespaces")));
994+
995+
/* Get the orig and new tablespace OIDs */
996+
orig_tablespaceoid = get_tablespace_oid(stmt->orig_tablespacename, false);
997+
new_tablespaceoid = get_tablespace_oid(stmt->new_tablespacename, false);
998+
999+
/* Can't move shared relations in to or out of pg_global */
1000+
/* This is also checked by ATExecSetTableSpace, but nice to stop earlier */
1001+
if (orig_tablespaceoid == GLOBALTABLESPACE_OID ||
1002+
new_tablespaceoid == GLOBALTABLESPACE_OID)
1003+
ereport(ERROR,
1004+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1005+
errmsg("cannot move relations in to or out of pg_global tablespace")));
1006+
1007+
/*
1008+
* Must have CREATE rights on the new tablespace, unless it is the
1009+
* database default tablespace (which all users implicitly have CREATE
1010+
* rights on).
1011+
*/
1012+
if (OidIsValid(new_tablespaceoid) && new_tablespaceoid != MyDatabaseTableSpace)
1013+
{
1014+
AclResult aclresult;
1015+
1016+
aclresult = pg_tablespace_aclcheck(new_tablespaceoid, GetUserId(),
1017+
ACL_CREATE);
1018+
if (aclresult != ACLCHECK_OK)
1019+
aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
1020+
get_tablespace_name(new_tablespaceoid));
1021+
}
1022+
1023+
/*
1024+
* Now that the checks are done, check if we should set either to
1025+
* InvalidOid because it is our database's default tablespace.
1026+
*/
1027+
if (orig_tablespaceoid == MyDatabaseTableSpace)
1028+
orig_tablespaceoid = InvalidOid;
1029+
1030+
if (new_tablespaceoid == MyDatabaseTableSpace)
1031+
new_tablespaceoid = InvalidOid;
1032+
1033+
/* no-op */
1034+
if (orig_tablespaceoid == new_tablespaceoid)
1035+
return new_tablespaceoid;
1036+
1037+
/*
1038+
* Walk the list of objects in the tablespace and move them. This will
1039+
* only find objects in our database, of course.
1040+
*/
1041+
ScanKeyInit(&key[0],
1042+
Anum_pg_class_reltablespace,
1043+
BTEqualStrategyNumber, F_OIDEQ,
1044+
ObjectIdGetDatum(orig_tablespaceoid));
1045+
1046+
rel = heap_open(RelationRelationId, AccessShareLock);
1047+
scan = heap_beginscan_catalog(rel, 1, key);
1048+
while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
1049+
{
1050+
Oid relOid = HeapTupleGetOid(tuple);
1051+
Form_pg_class relForm;
1052+
1053+
relForm = (Form_pg_class) GETSTRUCT(tuple);
1054+
1055+
/*
1056+
* Do not move objects in pg_catalog as part of this, if an admin
1057+
* really wishes to do so, they can issue the individual ALTER
1058+
* commands directly.
1059+
*
1060+
* Also, explicitly avoid any shared tables, temp tables, or TOAST
1061+
* (TOAST will be moved with the main table).
1062+
*/
1063+
if (IsSystemNamespace(relForm->relnamespace) || relForm->relisshared ||
1064+
isAnyTempNamespace(relForm->relnamespace) ||
1065+
relForm->relnamespace == PG_TOAST_NAMESPACE)
1066+
continue;
1067+
1068+
/*
1069+
* Only move objects that we are considered an owner of and only
1070+
* objects which can actually have a tablespace.
1071+
*/
1072+
if (!pg_class_ownercheck(relOid, GetUserId()) ||
1073+
(relForm->relkind != RELKIND_RELATION &&
1074+
relForm->relkind != RELKIND_INDEX &&
1075+
relForm->relkind != RELKIND_MATVIEW))
1076+
continue;
1077+
1078+
/* Check if we were asked to only move a certain type of object */
1079+
if (!stmt->move_all &&
1080+
((stmt->objtype == OBJECT_TABLE &&
1081+
relForm->relkind != RELKIND_RELATION) ||
1082+
(stmt->objtype == OBJECT_INDEX &&
1083+
relForm->relkind != RELKIND_INDEX) ||
1084+
(stmt->objtype == OBJECT_MATVIEW &&
1085+
relForm->relkind != RELKIND_MATVIEW)))
1086+
continue;
1087+
1088+
if (stmt->nowait &&
1089+
!ConditionalLockRelationOid(relOid, AccessExclusiveLock))
1090+
ereport(ERROR,
1091+
(errcode(ERRCODE_OBJECT_IN_USE),
1092+
errmsg("aborting due to \"%s\".\"%s\" --- lock not available",
1093+
get_namespace_name(relForm->relnamespace),
1094+
NameStr(relForm->relname))));
1095+
else
1096+
LockRelationOid(relOid, AccessExclusiveLock);
1097+
1098+
/* Add to our list of objects to move */
1099+
relations = lappend_oid(relations, relOid);
1100+
}
1101+
1102+
heap_endscan(scan);
1103+
heap_close(rel, AccessShareLock);
1104+
1105+
if (relations == NIL)
1106+
ereport(NOTICE,
1107+
(errcode(ERRCODE_NO_DATA_FOUND),
1108+
errmsg("no matching relations in tablespace \"%s\" found",
1109+
orig_tablespaceoid == InvalidOid ? "(database default)" :
1110+
get_tablespace_name(orig_tablespaceoid))));
1111+
1112+
/* Everything is locked, loop through and move all of the relations. */
1113+
foreach(l, relations)
1114+
{
1115+
List *cmds = NIL;
1116+
AlterTableCmd *cmd = makeNode(AlterTableCmd);
1117+
1118+
cmd->subtype = AT_SetTableSpace;
1119+
cmd->name = stmt->new_tablespacename;
1120+
1121+
cmds = lappend(cmds, cmd);
1122+
1123+
AlterTableInternal(lfirst_oid(l), cmds, false);
1124+
}
1125+
1126+
return new_tablespaceoid;
1127+
}
1128+
9581129
/*
9591130
* Routines for handling the GUC variable 'default_tablespace'.
9601131
*/
Collapse file

‎src/backend/nodes/copyfuncs.c‎

Copy file name to clipboardExpand all lines: src/backend/nodes/copyfuncs.c
+15Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3397,6 +3397,18 @@ _copyAlterTableSpaceOptionsStmt(const AlterTableSpaceOptionsStmt *from)
33973397
return newnode;
33983398
}
33993399

3400+
static AlterTableSpaceMoveStmt *
3401+
_copyAlterTableSpaceMoveStmt(const AlterTableSpaceMoveStmt *from)
3402+
{
3403+
AlterTableSpaceMoveStmt *newnode = makeNode(AlterTableSpaceMoveStmt);
3404+
3405+
COPY_STRING_FIELD(orig_tablespacename);
3406+
COPY_STRING_FIELD(new_tablespacename);
3407+
COPY_SCALAR_FIELD(nowait);
3408+
3409+
return newnode;
3410+
}
3411+
34003412
static CreateExtensionStmt *
34013413
_copyCreateExtensionStmt(const CreateExtensionStmt *from)
34023414
{
@@ -4408,6 +4420,9 @@ copyObject(const void *from)
44084420
case T_AlterTableSpaceOptionsStmt:
44094421
retval = _copyAlterTableSpaceOptionsStmt(from);
44104422
break;
4423+
case T_AlterTableSpaceMoveStmt:
4424+
retval = _copyAlterTableSpaceMoveStmt(from);
4425+
break;
44114426
case T_CreateExtensionStmt:
44124427
retval = _copyCreateExtensionStmt(from);
44134428
break;
Collapse file

‎src/backend/nodes/equalfuncs.c‎

Copy file name to clipboardExpand all lines: src/backend/nodes/equalfuncs.c
+14Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1634,6 +1634,17 @@ _equalAlterTableSpaceOptionsStmt(const AlterTableSpaceOptionsStmt *a,
16341634
return true;
16351635
}
16361636

1637+
static bool
1638+
_equalAlterTableSpaceMoveStmt(const AlterTableSpaceMoveStmt *a,
1639+
const AlterTableSpaceMoveStmt *b)
1640+
{
1641+
COMPARE_STRING_FIELD(orig_tablespacename);
1642+
COMPARE_STRING_FIELD(new_tablespacename);
1643+
COMPARE_SCALAR_FIELD(nowait);
1644+
1645+
return true;
1646+
}
1647+
16371648
static bool
16381649
_equalCreateExtensionStmt(const CreateExtensionStmt *a, const CreateExtensionStmt *b)
16391650
{
@@ -2877,6 +2888,9 @@ equal(const void *a, const void *b)
28772888
case T_AlterTableSpaceOptionsStmt:
28782889
retval = _equalAlterTableSpaceOptionsStmt(a, b);
28792890
break;
2891+
case T_AlterTableSpaceMoveStmt:
2892+
retval = _equalAlterTableSpaceMoveStmt(a, b);
2893+
break;
28802894
case T_CreateExtensionStmt:
28812895
retval = _equalCreateExtensionStmt(a, b);
28822896
break;

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.