Greetings, It's a day late and I'm a dollar short, but attached is a (very) minor patch to allow users to more easily move their various objects from one tablespace to another. Included are docs and a regression test; I'm happy to improve on both should folks send me suggestions.
As we use tablespaces quite a bit, this can be extremely handy for us
and I expect others will find it useful too.
Thoughts?
Thanks,
Stephen
diff --git a/doc/src/sgml/ref/alter_tablespace.sgml b/doc/src/sgml/ref/alter_tablespace.sgml
new file mode 100644
index 7d3ee2c..fcf4155 100644
*** a/doc/src/sgml/ref/alter_tablespace.sgml
--- b/doc/src/sgml/ref/alter_tablespace.sgml
*************** PostgreSQL documentation
*** 12,18 ****
<refnamediv>
<refname>ALTER TABLESPACE</refname>
! <refpurpose>change the definition of a tablespace</refpurpose>
</refnamediv>
<indexterm zone="sql-altertablespace">
--- 12,18 ----
<refnamediv>
<refname>ALTER TABLESPACE</refname>
! <refpurpose>change the definition of a tablespace or affect objects of a tablespace</refpurpose>
</refnamediv>
<indexterm zone="sql-altertablespace">
*************** ALTER TABLESPACE <replaceable>name</repl
*** 25,30 ****
--- 25,31 ----
ALTER TABLESPACE <replaceable>name</replaceable> OWNER TO <replaceable>new_owner</replaceable>
ALTER TABLESPACE <replaceable>name</replaceable> SET ( <replaceable class="PARAMETER">tablespace_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
ALTER TABLESPACE <replaceable>name</replaceable> RESET ( <replaceable class="PARAMETER">tablespace_option</replaceable> [, ... ] )
+ ALTER TABLESPACE <replaceable>name</replaceable> MOVE ALL TO <replaceable>new_tablespace</replaceable> [ NOWAIT ]
</synopsis>
</refsynopsisdiv>
*************** ALTER TABLESPACE <replaceable>name</repl
*** 32,46 ****
<title>Description</title>
<para>
! <command>ALTER TABLESPACE</command> changes the definition of
! a tablespace.
</para>
<para>
! You must own the tablespace to use <command>ALTER TABLESPACE</>.
To alter the owner, you must also be a direct or indirect member of the new
owning role.
(Note that superusers have these privileges automatically.)
</para>
</refsect1>
--- 33,60 ----
<title>Description</title>
<para>
! <command>ALTER TABLESPACE</command> can be used to change the definition of
! a tablespace or to migrate all of the objects in the current database which
! are owned by the user out of a given tablespace.
</para>
<para>
! You must own the tablespace to change the definition of a tablespace.
To alter the owner, you must also be a direct or indirect member of the new
owning role.
(Note that superusers have these privileges automatically.)
+
+ Users may use ALTER TABLESPACE ... MOVE ALL, but they must have CREATE
+ rights on the new tablespace and only objects, directly or indirectly, owned
+ by the user will 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 which are in the
+ tablespace.
+
+ System catalogs will not be moved by this command- individuals wishing to
+ move a whole database should use ALTER DATABASE, or call ALTER TABLE on the
+ individual system catalogs. Note that relations in <literal>information_schema</literal>
+ will be moved, just as any other normal database objects.
</para>
</refsect1>
*************** ALTER TABLESPACE <replaceable>name</repl
*** 94,99 ****
--- 108,137 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_tablespace</replaceable></term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">NOWAIT</replaceable></term>
+ <listitem>
+ <para>
+ 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
+ move.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect1>
*************** ALTER TABLESPACE index_space RENAME TO f
*** 112,117 ****
--- 150,162 ----
<programlisting>
ALTER TABLESPACE index_space OWNER TO mary;
</programlisting></para>
+
+ <para>
+ Move all of the objects which I own from the default tablespace to
+ the <literal>fast_raid</literal> tablespace:
+ <programlisting>
+ ALTER TABLESPACE pg_default MOVE ALL TO fast_raid;
+ </programlisting></para>
</refsect1>
<refsect1>
diff --git a/src/backend/commands/tablespace.c b/src/backend/commands/tablespace.c
new file mode 100644
index 07f5221..c47d13c 100644
*** a/src/backend/commands/tablespace.c
--- b/src/backend/commands/tablespace.c
***************
*** 59,78 ****
--- 59,83 ----
#include "catalog/catalog.h"
#include "catalog/dependency.h"
#include "catalog/indexing.h"
+ #include "catalog/namespace.h"
#include "catalog/objectaccess.h"
+ #include "catalog/pg_namespace.h"
#include "catalog/pg_tablespace.h"
#include "commands/comment.h"
#include "commands/seclabel.h"
+ #include "commands/tablecmds.h"
#include "commands/tablespace.h"
#include "common/relpath.h"
#include "miscadmin.h"
#include "postmaster/bgwriter.h"
#include "storage/fd.h"
+ #include "storage/lmgr.h"
#include "storage/standby.h"
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
#include "utils/guc.h"
+ #include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
#include "utils/tqual.h"
*************** AlterTableSpaceOptions(AlterTableSpaceOp
*** 956,961 ****
--- 961,1101 ----
}
/*
+ * Alter table space move all
+ */
+ Oid
+ AlterTableSpaceMove(AlterTableSpaceMoveStmt *stmt)
+ {
+ List *relations = NIL;
+ ListCell *l;
+ ScanKeyData key[1];
+ Relation rel;
+ HeapScanDesc scan;
+ HeapTuple tuple;
+ Oid orig_tablespaceoid;
+ Oid new_tablespaceoid;
+
+ /* Get the orig and new tablespace OIDs */
+ orig_tablespaceoid = get_tablespace_oid(stmt->orig_tablespacename, false);
+ new_tablespaceoid = get_tablespace_oid(stmt->new_tablespacename, false);
+
+ /* Can't move shared relations in to or out of pg_global */
+ /* This is also checked by ATExecSetTableSpace, but nice to stop earlier */
+ if (orig_tablespaceoid == GLOBALTABLESPACE_OID ||
+ new_tablespaceoid == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot move relations in to or out of pg_global tablespace")));
+
+ /*
+ * Must have CREATE rights on the new tablespace if not going to
+ * the database default tablespace
+ */
+ if (OidIsValid(new_tablespaceoid) && new_tablespaceoid != MyDatabaseTableSpace)
+ {
+ AclResult aclresult;
+
+ aclresult = pg_tablespace_aclcheck(new_tablespaceoid, GetUserId(),
+ ACL_CREATE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
+ get_tablespace_name(new_tablespaceoid));
+ }
+
+ /*
+ * Now that the checks are done, check if we should set either to InvalidOid
+ * because it is our database's default tablespace.
+ */
+ if (orig_tablespaceoid == MyDatabaseTableSpace)
+ orig_tablespaceoid = InvalidOid;
+
+ if (new_tablespaceoid == MyDatabaseTableSpace)
+ new_tablespaceoid = InvalidOid;
+
+ /* no-op */
+ if (orig_tablespaceoid == new_tablespaceoid)
+ return new_tablespaceoid;
+
+ /* Walk the list of objects in our database in the tablespace and move them */
+ ScanKeyInit(&key[0],
+ Anum_pg_class_reltablespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(orig_tablespaceoid));
+
+ rel = heap_open(RelationRelationId, AccessShareLock);
+ scan = heap_beginscan_catalog(rel, 1, key);
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ Oid relOid = HeapTupleGetOid(tuple);
+ Form_pg_class relForm;
+
+ relForm = (Form_pg_class) GETSTRUCT(tuple);
+
+ /*
+ * Do not move objects in pg_catalog as part of this, if an admin
+ * really wishes to do so, they can issue the individual ALTER
+ * commands directly.
+ *
+ * Also, explicitly avoid any shared tables, temp tables, or TOAST
+ * (TOAST will be moved with the main table).
+ */
+ if (IsSystemNamespace(relForm->relnamespace) || relForm->relisshared ||
+ isAnyTempNamespace(relForm->relnamespace) ||
+ relForm->relnamespace == PG_TOAST_NAMESPACE)
+ continue;
+
+ /*
+ * Only move objects that we are considered an owner of and only
+ * objects which can actually have a tablespace.
+ */
+ if (!pg_class_ownercheck(relOid, GetUserId()) ||
+ (relForm->relkind != RELKIND_RELATION &&
+ relForm->relkind != RELKIND_INDEX &&
+ relForm->relkind != RELKIND_MATVIEW))
+ continue;
+
+ if (stmt->nowait &&
+ !ConditionalLockRelationOid(relOid, AccessExclusiveLock))
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_IN_USE),
+ errmsg("aborting due to \"%s\".\"%s\" --- lock not available",
+ get_namespace_name(relForm->relnamespace),
+ NameStr(relForm->relname))));
+ else
+ LockRelationOid(relOid, AccessExclusiveLock);
+
+ /* Add to our list of objects to move */
+ relations = lappend_oid(relations, relOid);
+ }
+
+ heap_endscan(scan);
+ heap_close(rel, AccessShareLock);
+
+ if (relations == NIL)
+ ereport(NOTICE,
+ (errcode(ERRCODE_NO_DATA_FOUND),
+ errmsg("no relations in tablespace \"%s\" found",
+ orig_tablespaceoid == InvalidOid ? "(database default)" :
+ get_tablespace_name(orig_tablespaceoid))));
+
+ /* Everything is locked, loop through and move all of the relations */
+ foreach(l, relations)
+ {
+ List *cmds = NIL;
+ AlterTableCmd *cmd = makeNode(AlterTableCmd);
+
+ cmd->subtype = AT_SetTableSpace;
+ cmd->name = stmt->new_tablespacename;
+
+ cmds = lappend(cmds, cmd);
+
+ AlterTableInternal(lfirst_oid(l), cmds, false);
+ }
+
+ return new_tablespaceoid;
+ }
+
+ /*
* Routines for handling the GUC variable 'default_tablespace'.
*/
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
new file mode 100644
index fb4ce2c..19e5f04 100644
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
*************** _copyAlterTableSpaceOptionsStmt(const Al
*** 3397,3402 ****
--- 3397,3414 ----
return newnode;
}
+ static AlterTableSpaceMoveStmt *
+ _copyAlterTableSpaceMoveStmt(const AlterTableSpaceMoveStmt *from)
+ {
+ AlterTableSpaceMoveStmt *newnode = makeNode(AlterTableSpaceMoveStmt);
+
+ COPY_STRING_FIELD(orig_tablespacename);
+ COPY_STRING_FIELD(new_tablespacename);
+ COPY_SCALAR_FIELD(nowait);
+
+ return newnode;
+ }
+
static CreateExtensionStmt *
_copyCreateExtensionStmt(const CreateExtensionStmt *from)
{
*************** copyObject(const void *from)
*** 4408,4413 ****
--- 4420,4428 ----
case T_AlterTableSpaceOptionsStmt:
retval = _copyAlterTableSpaceOptionsStmt(from);
break;
+ case T_AlterTableSpaceMoveStmt:
+ retval = _copyAlterTableSpaceMoveStmt(from);
+ break;
case T_CreateExtensionStmt:
retval = _copyCreateExtensionStmt(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
new file mode 100644
index ccf7267..55c548d 100644
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
*************** _equalAlterTableSpaceOptionsStmt(const A
*** 1635,1640 ****
--- 1635,1651 ----
}
static bool
+ _equalAlterTableSpaceMoveStmt(const AlterTableSpaceMoveStmt *a,
+ const AlterTableSpaceMoveStmt *b)
+ {
+ COMPARE_STRING_FIELD(orig_tablespacename);
+ COMPARE_STRING_FIELD(new_tablespacename);
+ COMPARE_SCALAR_FIELD(nowait);
+
+ return true;
+ }
+
+ static bool
_equalCreateExtensionStmt(const CreateExtensionStmt *a, const CreateExtensionStmt *b)
{
COMPARE_STRING_FIELD(extname);
*************** equal(const void *a, const void *b)
*** 2877,2882 ****
--- 2888,2896 ----
case T_AlterTableSpaceOptionsStmt:
retval = _equalAlterTableSpaceOptionsStmt(a, b);
break;
+ case T_AlterTableSpaceMoveStmt:
+ retval = _equalAlterTableSpaceMoveStmt(a, b);
+ break;
case T_CreateExtensionStmt:
retval = _equalCreateExtensionStmt(a, b);
break;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
new file mode 100644
index f0b9507..9f5dcc4 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** RenameStmt: ALTER AGGREGATE func_name ag
*** 7319,7324 ****
--- 7319,7333 ----
n->missing_ok = false;
$$ = (Node *)n;
}
+ | ALTER TABLESPACE name MOVE ALL TO name opt_nowait
+ {
+ AlterTableSpaceMoveStmt *n =
+ makeNode(AlterTableSpaceMoveStmt);
+ n->orig_tablespacename = $3;
+ n->new_tablespacename = $7;
+ n->nowait = $8;
+ $$ = (Node *)n;
+ }
| ALTER TABLESPACE name SET reloptions
{
AlterTableSpaceOptionsStmt *n =
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
new file mode 100644
index 36cf72d..f4d25bd 100644
*** a/src/backend/tcop/utility.c
--- b/src/backend/tcop/utility.c
*************** check_xact_readonly(Node *parsetree)
*** 243,248 ****
--- 243,249 ----
case T_AlterUserMappingStmt:
case T_DropUserMappingStmt:
case T_AlterTableSpaceOptionsStmt:
+ case T_AlterTableSpaceMoveStmt:
case T_CreateForeignTableStmt:
case T_SecLabelStmt:
PreventCommandIfReadOnly(CreateCommandTag(parsetree));
*************** standard_ProcessUtility(Node *parsetree,
*** 548,553 ****
--- 549,559 ----
AlterTableSpaceOptions((AlterTableSpaceOptionsStmt *) parsetree);
break;
+ case T_AlterTableSpaceMoveStmt:
+ /* no event triggers for global objects */
+ AlterTableSpaceMove((AlterTableSpaceMoveStmt *) parsetree);
+ break;
+
case T_TruncateStmt:
ExecuteTruncate((TruncateStmt *) parsetree);
break;
*************** CreateCommandTag(Node *parsetree)
*** 1822,1827 ****
--- 1828,1837 ----
tag = "ALTER TABLESPACE";
break;
+ case T_AlterTableSpaceMoveStmt:
+ tag = "ALTER TABLESPACE";
+ break;
+
case T_CreateExtensionStmt:
tag = "CREATE EXTENSION";
break;
*************** GetCommandLogLevel(Node *parsetree)
*** 2514,2519 ****
--- 2524,2533 ----
lev = LOGSTMT_DDL;
break;
+ case T_AlterTableSpaceMoveStmt:
+ lev = LOGSTMT_DDL;
+ break;
+
case T_CreateExtensionStmt:
case T_AlterExtensionStmt:
case T_AlterExtensionContentsStmt:
diff --git a/src/include/commands/tablespace.h b/src/include/commands/tablespace.h
new file mode 100644
index c7af559..1603f67 100644
*** a/src/include/commands/tablespace.h
--- b/src/include/commands/tablespace.h
*************** extern Oid CreateTableSpace(CreateTableS
*** 43,48 ****
--- 43,49 ----
extern void DropTableSpace(DropTableSpaceStmt *stmt);
extern Oid RenameTableSpace(const char *oldname, const char *newname);
extern Oid AlterTableSpaceOptions(AlterTableSpaceOptionsStmt *stmt);
+ extern Oid AlterTableSpaceMove(AlterTableSpaceMoveStmt *stmt);
extern void TablespaceCreateDbspace(Oid spcNode, Oid dbNode, bool isRedo);
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
new file mode 100644
index ae12c0d..dfcc013 100644
*** a/src/include/nodes/nodes.h
--- b/src/include/nodes/nodes.h
*************** typedef enum NodeTag
*** 354,359 ****
--- 354,360 ----
T_AlterUserMappingStmt,
T_DropUserMappingStmt,
T_AlterTableSpaceOptionsStmt,
+ T_AlterTableSpaceMoveStmt,
T_SecLabelStmt,
T_CreateForeignTableStmt,
T_CreateExtensionStmt,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
new file mode 100644
index 9a3a5d7..2079cd9 100644
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
*************** typedef struct AlterTableSpaceOptionsStm
*** 1686,1691 ****
--- 1686,1699 ----
bool isReset;
} AlterTableSpaceOptionsStmt;
+ typedef struct AlterTableSpaceMoveStmt
+ {
+ NodeTag type;
+ char *orig_tablespacename;
+ char *new_tablespacename;
+ bool nowait;
+ } AlterTableSpaceMoveStmt;
+
/* ----------------------
* Create/Alter Extension Statements
* ----------------------
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
new file mode 100644
index 4f17b09..8ee7efa 100644
*** a/src/test/regress/input/tablespace.source
--- b/src/test/regress/input/tablespace.source
*************** CREATE TABLE tablespace_table (i int) TA
*** 66,75 ****
ALTER TABLESPACE testspace RENAME TO testspace_renamed;
! DROP SCHEMA testschema CASCADE;
-- Should succeed
DROP TABLESPACE testspace_renamed;
DROP ROLE tablespace_testuser1;
DROP ROLE tablespace_testuser2;
--- 66,80 ----
ALTER TABLESPACE testspace RENAME TO testspace_renamed;
! ALTER TABLESPACE testspace_renamed MOVE ALL TO pg_default;
!
! -- Should show notice that nothing was done
! ALTER TABLESPACE testspace_renamed MOVE ALL TO pg_default;
-- Should succeed
DROP TABLESPACE testspace_renamed;
+ DROP SCHEMA testschema CASCADE;
+
DROP ROLE tablespace_testuser1;
DROP ROLE tablespace_testuser2;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
new file mode 100644
index 2868169..5035ab0 100644
*** a/src/test/regress/output/tablespace.source
--- b/src/test/regress/output/tablespace.source
*************** CREATE TABLE tablespace_table (i int) TA
*** 80,92 ****
ERROR: permission denied for tablespace testspace
\c -
ALTER TABLESPACE testspace RENAME TO testspace_renamed;
DROP SCHEMA testschema CASCADE;
NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to table testschema.foo
drop cascades to table testschema.asselect
drop cascades to table testschema.asexecute
drop cascades to table testschema.atable
- -- Should succeed
- DROP TABLESPACE testspace_renamed;
DROP ROLE tablespace_testuser1;
DROP ROLE tablespace_testuser2;
--- 80,96 ----
ERROR: permission denied for tablespace testspace
\c -
ALTER TABLESPACE testspace RENAME TO testspace_renamed;
+ ALTER TABLESPACE testspace_renamed MOVE ALL TO pg_default;
+ -- Should show notice that nothing was done
+ ALTER TABLESPACE testspace_renamed MOVE ALL TO pg_default;
+ NOTICE: no relations in tablespace "testspace_renamed" found
+ -- Should succeed
+ DROP TABLESPACE testspace_renamed;
DROP SCHEMA testschema CASCADE;
NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to table testschema.foo
drop cascades to table testschema.asselect
drop cascades to table testschema.asexecute
drop cascades to table testschema.atable
DROP ROLE tablespace_testuser1;
DROP ROLE tablespace_testuser2;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
new file mode 100644
index e3058be..1f735b7 100644
*** a/src/tools/pgindent/typedefs.list
--- b/src/tools/pgindent/typedefs.list
*************** AlterTSConfigurationStmt
*** 76,81 ****
--- 76,82 ----
AlterTSDictionaryStmt
AlterTableCmd
AlterTableSpaceOptionsStmt
+ AlterTableSpaceMoveStmt
AlterTableStmt
AlterTableType
AlterUserMappingStmt
signature.asc
Description: Digital signature
