Subject: [PATCH v10 2/5] Allow CLUSTER and VACUUM FULL to change tablespace.

From: Alexey Kondratov <[email-masked]>
Date: Fri, 21 Dec 2018 14:54:10 +0300

---
 doc/src/sgml/ref/cluster.sgml             | 11 ++++-
 doc/src/sgml/ref/vacuum.sgml              | 13 +++++-
 src/backend/commands/cluster.c            | 28 +++++++----
 src/backend/commands/tablecmds.c          | 57 +++++++++++++----------
 src/backend/commands/vacuum.c             | 39 ++++++++++++++--
 src/backend/parser/gram.y                 | 42 +++++++++++++++--
 src/include/commands/cluster.h            |  2 +-
 src/include/commands/defrem.h             | 18 +++----
 src/include/commands/tablecmds.h          |  2 +
 src/include/commands/vacuum.h             |  2 +
 src/include/nodes/parsenodes.h            |  2 +
 src/test/regress/input/tablespace.source  | 27 ++++++++---
 src/test/regress/output/tablespace.source | 43 ++++++++++++-----
 13 files changed, 219 insertions(+), 67 deletions(-)

diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml
index 4da60d8d56..ebf23b8434 100644
--- a/doc/src/sgml/ref/cluster.sgml
+++ b/doc/src/sgml/ref/cluster.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-CLUSTER [VERBOSE] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">index_name</replaceable> ]
+CLUSTER [VERBOSE] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">index_name</replaceable> ] [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ]
 CLUSTER [VERBOSE]
 </synopsis>
  </refsynopsisdiv>
@@ -99,6 +99,15 @@ CLUSTER [VERBOSE]
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><replaceable class="parameter">new_tablespace</replaceable></term>
+    <listitem>
+     <para>
+      The name of the specific tablespace to store clustered relations.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>VERBOSE</literal></term>
     <listitem>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 846056a353..e688edb06d 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -22,7 +22,8 @@ PostgreSQL documentation
  <refsynopsisdiv>
 <synopsis>
 VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
-VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
+VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
+VACUUM ( FULL [, ...] ) [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
 
 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
 
@@ -299,6 +300,16 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">new_tablespace</replaceable></term>
+    <listitem>
+     <para>
+      The name of the specific tablespace to write new copy of the table.
+     </para>
+    </listitem>
+   </varlistentry>
+
   </variablelist>
  </refsect1>
 
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 9e5b28903a..0e13c5192e 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -37,6 +37,7 @@
 #include "commands/cluster.h"
 #include "commands/progress.h"
 #include "commands/tablecmds.h"
+#include "commands/tablespace.h"
 #include "commands/vacuum.h"
 #include "miscadmin.h"
 #include "optimizer/optimizer.h"
@@ -67,10 +68,10 @@ typedef struct
 } RelToCluster;

-static void rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose);
+static void rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTableSpaceOid, bool verbose);
 static void copy_table_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex,
-							bool verbose, bool *pSwapToastByContent,
-							TransactionId *pFreezeXid, MultiXactId *pCutoffMulti);
+			   bool verbose, bool *pSwapToastByContent,
+			   TransactionId *pFreezeXid, MultiXactId *pCutoffMulti);
 static List *get_tables_to_cluster(MemoryContext cluster_context);

@@ -101,6 +102,13 @@ static List *get_tables_to_cluster(MemoryContext cluster_context);
 void
 cluster(ClusterStmt *stmt, bool isTopLevel)
 {
+	/* Oid of tablespace to use for clustered relation. */
+	Oid tableSpaceOid = InvalidOid;
+
+	/* Select tablespace Oid to use. */
+	if (stmt->tablespacename)
+		tableSpaceOid = get_tablespace_oid(stmt->tablespacename, false);
+
 	if (stmt->relation != NULL)
 	{
 		/* This is the single-relation case. */
@@ -182,7 +190,7 @@ cluster(ClusterStmt *stmt, bool isTopLevel)
 		table_close(rel, NoLock);
 
 		/* Do the job. */
-		cluster_rel(tableOid, indexOid, stmt->options);
+		cluster_rel(tableOid, indexOid, tableSpaceOid, stmt->options);
 	}
 	else
 	{
@@ -230,7 +238,7 @@ cluster(ClusterStmt *stmt, bool isTopLevel)
 			/* functions in indexes may want a snapshot set */
 			PushActiveSnapshot(GetTransactionSnapshot());
 			/* Do the job. */
-			cluster_rel(rvtc->tableOid, rvtc->indexOid,
+			cluster_rel(rvtc->tableOid, rvtc->indexOid, tableSpaceOid,
 						stmt->options | CLUOPT_RECHECK);
 			PopActiveSnapshot();
 			CommitTransactionCommand();
@@ -262,7 +270,7 @@ cluster(ClusterStmt *stmt, bool isTopLevel)
  * and error messages should refer to the operation as VACUUM not CLUSTER.
  */
 void
-cluster_rel(Oid tableOid, Oid indexOid, int options)
+cluster_rel(Oid tableOid, Oid indexOid, Oid tableSpaceOid, int options)
 {
 	Relation	OldHeap;
 	bool		verbose = ((options & CLUOPT_VERBOSE) != 0);
@@ -425,7 +433,7 @@ cluster_rel(Oid tableOid, Oid indexOid, int options)
 	TransferPredicateLocksToHeapRelation(OldHeap);
 
 	/* rebuild_relation does all the dirty work */
-	rebuild_relation(OldHeap, indexOid, verbose);
+	rebuild_relation(OldHeap, indexOid, tableSpaceOid, verbose);
 
 	/* NB: rebuild_relation does table_close() on OldHeap */
 
@@ -584,7 +592,7 @@ mark_index_clustered(Relation rel, Oid indexOid, bool is_internal)
  * NB: this routine closes OldHeap at the right time; caller should not.
  */
 static void
-rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
+rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTableSpaceOid, bool verbose)
 {
 	Oid			tableOid = RelationGetRelid(OldHeap);
 	Oid			tableSpace = OldHeap->rd_rel->reltablespace;
@@ -595,6 +603,10 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
 	TransactionId frozenXid;
 	MultiXactId cutoffMulti;
 
+	/* Use new TeableSpace if passed. */
+	if (OidIsValid(NewTableSpaceOid))
+		tableSpace = NewTableSpaceOid;
+
 	/* Mark the correct index as clustered */
 	if (OidIsValid(indexOid))
 		mark_index_clustered(OldHeap, indexOid, true);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 30ac18c0ce..ed8e405618 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -12861,30 +12861,7 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
 		return;
 	}
 
-	/*
-	 * We cannot support moving mapped relations into different tablespaces.
-	 * (In particular this eliminates all shared catalogs.)
-	 */
-	if (RelationIsMapped(rel))
-		ereport(ERROR,
-				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-				 errmsg("cannot move system relation \"%s\"",
-						RelationGetRelationName(rel))));
-
-	/* Can't move a non-shared relation into pg_global */
-	if (newTableSpace == GLOBALTABLESPACE_OID)
-		ereport(ERROR,
-				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-				 errmsg("only shared relations can be placed in pg_global tablespace")));
-
-	/*
-	 * Don't allow moving temp tables of other backends ... their local buffer
-	 * manager is not going to cope.
-	 */
-	if (RELATION_IS_OTHER_TEMP(rel))
-		ereport(ERROR,
-				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-				 errmsg("cannot move temporary tables of other sessions")));
+	check_relation_is_movable(rel, newTableSpace);
 
 	reltoastrelid = rel->rd_rel->reltoastrelid;
 	/* Fetch the list of indexes on toast relation if necessary */
@@ -13481,6 +13458,38 @@ CreateInheritance(Relation child_rel, Relation parent_rel)
 	table_close(catalogRelation, RowExclusiveLock);
 }
 
+/*
+ * Validate that relation can be moved to the specified tablespace.
+ */
+extern void
+check_relation_is_movable(Relation rel, Oid tablespaceOid)
+{
+	/*
+	 * We cannot support moving mapped relations into different tablespaces.
+	 * (In particular this eliminates all shared catalogs.)
+	 */
+	if (RelationIsMapped(rel))
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				errmsg("cannot move system relation \"%s\"",
+						RelationGetRelationName(rel))));
+
+	/* Can't move a non-shared relation into pg_global */
+	if (tablespaceOid == GLOBALTABLESPACE_OID)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				errmsg("only shared relations can be placed in pg_global tablespace")));
+
+	/*
+	 * Don't allow moving temp tables of other backends ... their local buffer
+	 * manager is not going to cope.
+	 */
+	if (RELATION_IS_OTHER_TEMP(rel))
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				errmsg("cannot move temporary tables of other sessions")));
+}
+
 /*
  * Obtain the source-text form of the constraint expression for a check
  * constraint, given its pg_constraint tuple
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index d625d17bf4..cfa64b80fd 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -31,6 +31,7 @@
 #include "access/tableam.h"
 #include "access/transam.h"
 #include "access/xact.h"
+#include "catalog/catalog.h"
 #include "catalog/namespace.h"
 #include "catalog/pg_database.h"
 #include "catalog/pg_inherits.h"
@@ -38,6 +39,7 @@
 #include "commands/cluster.h"
 #include "commands/defrem.h"
 #include "commands/vacuum.h"
+#include "commands/tablespace.h"
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
 #include "pgstat.h"
@@ -107,6 +109,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 	bool		parallel_option = false;
 	ListCell   *lc;
 
+	Oid tableSpaceOid = InvalidOid; /* Oid of tablespace to use for relations
+									 * store after VACUUM FULL. */
 	/* Set default value */
 	params.index_cleanup = VACOPT_TERNARY_DEFAULT;
 	params.truncate = VACOPT_TERNARY_DEFAULT;
@@ -241,6 +245,18 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 		params.multixact_freeze_table_age = -1;
 	}
 
+	/* Get tablespace Oid to use. */
+	if (vacstmt->tablespacename)
+	{
+		if (params.options & VACOPT_FULL)
+			tableSpaceOid = get_tablespace_oid(vacstmt->tablespacename, false);
+		else
+			ereport(ERROR,
+				(errmsg("incompatible SET TABLESPACE option"),
+				errdetail("You can only use SET TABLESPACE with VACUUM FULL.")));
+	}
+	params.tablespace_oid = tableSpaceOid;
+
 	/* user-invoked vacuum is never "for wraparound" */
 	params.is_wraparound = false;
 
@@ -1672,8 +1688,9 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	LOCKMODE	lmode;
 	Relation	onerel;
 	LockRelId	onerelid;
-	Oid			toast_relid;
-	Oid			save_userid;
+	Oid			toast_relid,
+				save_userid,
+				new_tablespaceoid = InvalidOid;
 	int			save_sec_context;
 	int			save_nestlevel;
 
@@ -1807,6 +1824,22 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 		return true;
 	}
 
+	/*
+	 * We cannot support moving system relations into different tablespaces.
+	 */
+	if (params->options & VACOPT_FULL && OidIsValid(params->tablespace_oid) && IsSystemRelation(onerel))
+	{
+		ereport(WARNING,
+			(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+			errmsg("skipping tablespace change of \"%s\"",
+					RelationGetRelationName(onerel)),
+			errdetail("Cannot move system relation, only VACUUM is performed.")));
+	}
+	else
+	{
+		new_tablespaceoid = params->tablespace_oid;
+	}
+
 	/*
 	 * Get a session-level lock too. This will protect our access to the
 	 * relation across multiple transactions, so that we can vacuum the
@@ -1876,7 +1909,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 			cluster_options |= CLUOPT_VERBOSE;
 
 		/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
-		cluster_rel(relid, InvalidOid, cluster_options);
+		cluster_rel(relid, InvalidOid, new_tablespaceoid, cluster_options);
 	}
 	else
 		table_relation_vacuum(onerel, params, vac_strategy);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index df8f63f989..1f388017be 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10589,14 +10589,14 @@ CreateConversionStmt:
 /*****************************************************************************
  *
  *		QUERY:
- *				CLUSTER [VERBOSE] <qualified_name> [ USING <index_name> ]
- *				CLUSTER [VERBOSE]
+ *				CLUSTER [VERBOSE] <qualified_name> [ USING <index_name> ] [ TABLESPACE <tablespace_name> ]
+ *				CLUSTER [VERBOSE] [ TABLESPACE <tablespace_name> ]
  *				CLUSTER [VERBOSE] <index_name> ON <qualified_name> (for pre-8.3)
  *
  *****************************************************************************/
 
 ClusterStmt:
-			CLUSTER opt_verbose qualified_name cluster_index_specification
+			CLUSTER opt_verbose qualified_name cluster_index_specification OptTableSpace
 				{
 					ClusterStmt *n = makeNode(ClusterStmt);
 					n->relation = $3;
@@ -10604,6 +10604,7 @@ ClusterStmt:
 					n->options = 0;
 					if ($2)
 						n->options |= CLUOPT_VERBOSE;
+					n->tablespacename = $5;
 					$$ = (Node*)n;
 				}
 			| CLUSTER opt_verbose
@@ -10625,6 +10626,7 @@ ClusterStmt:
 					n->options = 0;
 					if ($2)
 						n->options |= CLUOPT_VERBOSE;
+					n->tablespacename = NULL;
 					$$ = (Node*)n;
 				}
 		;
@@ -10639,6 +10641,8 @@ cluster_index_specification:
  *
  *		QUERY:
  *				VACUUM
+ *				VACUUM FULL [ TABLESPACE <tablespace_name> ] [ <table_and_columns> [, ...] ]
+ *				VACUUM (FULL) [ TABLESPACE <tablespace_name> ] [ <table_and_columns> [, ...] ]
  *				ANALYZE
  *
  *****************************************************************************/
@@ -10661,6 +10665,28 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose opt_analyze opt_vacuum_relati
 											 makeDefElem("analyze", NULL, @5));
 					n->rels = $6;
 					n->is_vacuumcmd = true;
+					n->tablespacename = NULL;
+					$$ = (Node *)n;
+				}
+			| VACUUM opt_full opt_freeze opt_verbose opt_analyze TABLESPACE name opt_vacuum_relation_list
+				{
+					VacuumStmt *n = makeNode(VacuumStmt);
+					n->options = NIL;
+					if ($2)
+						n->options = lappend(n->options,
+								makeDefElem("full", NULL, @2));
+					if ($3)
+						n->options = lappend(n->options,
+								makeDefElem("freeze", NULL, @3));
+					if ($4)
+						n->options = lappend(n->options,
+								makeDefElem("verbose", NULL, @4));
+					if ($5)
+						n->options = lappend(n->options,
+								makeDefElem("analyze", NULL, @5));
+					n->tablespacename = $7;
+					n->rels = $8;
+					n->is_vacuumcmd = true;
 					$$ = (Node *)n;
 				}
 			| VACUUM '(' vac_analyze_option_list ')' opt_vacuum_relation_list
@@ -10669,6 +10695,16 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose opt_analyze opt_vacuum_relati
 					n->options = $3;
 					n->rels = $5;
 					n->is_vacuumcmd = true;
+					n->tablespacename = NULL;
+					$$ = (Node *) n;
+				}
+			| VACUUM '(' vac_analyze_option_list ')' TABLESPACE name opt_vacuum_relation_list
+				{
+					VacuumStmt *n = makeNode(VacuumStmt);
+					n->options = $3;
+					n->tablespacename = $6;
+					n->rels = $7;
+					n->is_vacuumcmd = true;
 					$$ = (Node *) n;
 				}
 		;
diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h
index e05884781b..ab7a0ad642 100644
--- a/src/include/commands/cluster.h
+++ b/src/include/commands/cluster.h
@@ -19,7 +19,7 @@

 extern void cluster(ClusterStmt *stmt, bool isTopLevel);
-extern void cluster_rel(Oid tableOid, Oid indexOid, int options);
+extern void cluster_rel(Oid tableOid, Oid indexOid, Oid tableSpaceOid, int options);
 extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid,
 									   bool recheck, LOCKMODE lockmode);
 extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal);
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index c3a906b387..0eb9f99120 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -25,15 +25,15 @@ extern void RemoveObjects(DropStmt *stmt);
 
 /* commands/indexcmds.c */
 extern ObjectAddress DefineIndex(Oid relationId,
-								 IndexStmt *stmt,
-								 Oid indexRelationId,
-								 Oid parentIndexId,
-								 Oid parentConstraintId,
-								 bool is_alter_table,
-								 bool check_rights,
-								 bool check_not_in_use,
-								 bool skip_build,
-								 bool quiet);
+			IndexStmt *stmt,
+			Oid indexRelationId,
+			Oid parentIndexId,
+			Oid parentConstraintId,
+			bool is_alter_table,
+			bool check_rights,
+			bool check_not_in_use,
+			bool skip_build,
+			bool quiet);
 extern void ReindexIndex(RangeVar *indexRelation, char *newTableSpaceName, int options, bool concurrent);
 extern Oid	ReindexTable(RangeVar *relation, char *newTableSpaceName, int options, bool concurrent);
 extern void ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index c1581ad178..21f9cef535 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -55,6 +55,8 @@ extern void AlterRelationNamespaceInternal(Relation classRel, Oid relOid,
 
 extern void CheckTableNotInUse(Relation rel, const char *stmt);
 
+extern void check_relation_is_movable(Relation rel, Oid tablespaceOid);
+
 extern void ExecuteTruncate(TruncateStmt *stmt);
 extern void ExecuteTruncateGuts(List *explicit_rels, List *relids, List *relids_logged,
 								DropBehavior behavior, bool restart_seqs);
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index c27d255d8d..8d9e862cd6 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -229,6 +229,8 @@ typedef struct VacuumParams
 	 * disabled.
 	 */
 	int			nworkers;
+	Oid			tablespace_oid; /* tablespace Oid to use for store relations
+								 * after VACUUM FULL */
 } VacuumParams;
 
 /* GUC parameters */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 5075a79bd3..5fb0f72141 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3202,6 +3202,7 @@ typedef struct ClusterStmt
 	NodeTag		type;
 	RangeVar   *relation;		/* relation being indexed, or NULL if all */
 	char	   *indexname;		/* original index defined */
+	char	   *tablespacename; /* tablespace name to use for clustered relation. */
 	int			options;		/* OR of ClusterOption flags */
 } ClusterStmt;
 
@@ -3218,6 +3219,7 @@ typedef struct VacuumStmt
 	List	   *options;		/* list of DefElem nodes */
 	List	   *rels;			/* list of VacuumRelation, or NIL for all */
 	bool		is_vacuumcmd;	/* true for VACUUM, false for ANALYZE */
+	char	   *tablespacename; /* tablespace name to use for vacuumed relation. */
 } VacuumStmt;
 
 /*
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 7d698d4294..8ed97afb9c 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -17,7 +17,7 @@ ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true);  -- f
 ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
 ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
 
--- create table to test REINDEX with TABLESPACE change
+-- create table to test REINDEX, CLUSTER and VACUUM FULL with TABLESPACE change
 CREATE TABLE regress_tblspace_test_tbl (num1 bigint, num2 double precision, num3 double precision);
 INSERT INTO regress_tblspace_test_tbl (num1, num2, num3)
   SELECT round(random()*100), random(), random()*42
@@ -45,23 +45,38 @@ WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspa
 REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok
 REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; -- ok
 REINDEX TABLE pg_authid TABLESPACE regress_tblspace; -- fail
-REINDEX SYSTEM CONCURRENTLY postgres TABLESPACE regress_tblspace; -- fail
-REINDEX TABLE CONCURRENTLY pg_am TABLESPACE regress_tblspace; -- fail
+REINDEX SCHEMA pg_catalog TABLESPACE regress_tblspace; -- fail
+REINDEX DATABASE postgres TABLESPACE regress_tblspace; -- fail
+REINDEX SYSTEM postgres TABLESPACE regress_tblspace; -- fail
 REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail
-REINDEX TABLE pg_am TABLESPACE regress_tblspace; -- ok
+
+REINDEX SCHEMA pg_catalog TABLESPACE regress_tblspace; -- fail
+REINDEX DATABASE postgres TABLESPACE regress_tblspace; -- fail
+REINDEX SYSTEM postgres TABLESPACE regress_tblspace; -- fail
+
+-- check CLUSTER with TABLESPACE change
+CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok
+CLUSTER pg_authid USING pg_authid_rolname_index TABLESPACE regress_tblspace; -- fail
+
+-- check VACUUM with TABLESPACE change
+VACUUM (FULL) TABLESPACE regress_tblspace pg_authid; -- skip with warning
+VACUUM (ANALYSE) TABLESPACE regress_tblspace; -- fail
 
 -- check that all relations moved to new tablespace
 SELECT relname FROM pg_class
 WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+AND relname IN ('regress_tblspace_test_tbl_idx', 'regress_tblspace_test_tbl')
 ORDER BY relname;
 
 -- move back to pg_default tablespace
 REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl TABLESPACE pg_default; -- ok
-REINDEX TABLE pg_am TABLESPACE pg_default; -- ok
+CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE pg_default; -- ok
+VACUUM (FULL, ANALYSE, FREEZE) TABLESPACE pg_default regress_tblspace_test_tbl; -- ok
 
 -- check that all relations moved back to pg_default
 SELECT relname FROM pg_class
-WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+AND relname IN ('regress_tblspace_test_tbl_idx', 'regress_tblspace_test_tbl');
 
 -- create a schema we can use
 CREATE SCHEMA testschema;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index bd17feaa73..5634cf20ff 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -20,7 +20,7 @@ ERROR:  unrecognized parameter "some_nonexistent_parameter"
 ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
 ERROR:  RESET must not include values for parameters
 ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
--- create table to test REINDEX with TABLESPACE change
+-- create table to test REINDEX, CLUSTER and VACUUM FULL with TABLESPACE change
 CREATE TABLE regress_tblspace_test_tbl (num1 bigint, num2 double precision, num3 double precision);
 INSERT INTO regress_tblspace_test_tbl (num1, num2, num3)
   SELECT round(random()*100), random(), random()*42
@@ -33,6 +33,7 @@ REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace;
 ROLLBACK;
 BEGIN;
 REINDEX TABLE pg_am TABLESPACE regress_tblspace;
+ERROR:  cannot move system relation "pg_am_name_index"
 ROLLBACK;
 SELECT relname FROM pg_class
 WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
@@ -56,30 +57,50 @@ REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok
 REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; -- ok
 REINDEX TABLE pg_authid TABLESPACE regress_tblspace; -- fail
 ERROR:  cannot move system relation "pg_authid_rolname_index"
-REINDEX SYSTEM CONCURRENTLY postgres TABLESPACE regress_tblspace; -- fail
-ERROR:  cannot reindex system catalogs concurrently
-REINDEX TABLE CONCURRENTLY pg_am TABLESPACE regress_tblspace; -- fail
-ERROR:  cannot reindex system catalogs concurrently
+REINDEX SCHEMA pg_catalog TABLESPACE regress_tblspace; -- fail
+WARNING:  cannot change tablespace of indexes for mapped relations, skipping all
+REINDEX DATABASE postgres TABLESPACE regress_tblspace; -- fail
+ERROR:  can only reindex the currently open database
+REINDEX SYSTEM postgres TABLESPACE regress_tblspace; -- fail
+ERROR:  can only reindex the currently open database
 REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail
 ERROR:  cannot move non-shared relation to tablespace "pg_global"
-REINDEX TABLE pg_am TABLESPACE regress_tblspace; -- ok
+REINDEX SCHEMA pg_catalog TABLESPACE regress_tblspace; -- fail
+WARNING:  cannot change tablespace of indexes for mapped relations, skipping all
+REINDEX DATABASE postgres TABLESPACE regress_tblspace; -- fail
+ERROR:  can only reindex the currently open database
+REINDEX SYSTEM postgres TABLESPACE regress_tblspace; -- fail
+ERROR:  can only reindex the currently open database
+-- check CLUSTER with TABLESPACE change
+CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok
+CLUSTER pg_authid USING pg_authid_rolname_index TABLESPACE regress_tblspace; -- fail
+ERROR:  cannot cluster a shared catalog
+-- check VACUUM with TABLESPACE change
+VACUUM (FULL) TABLESPACE regress_tblspace pg_authid; -- skip with warning
+WARNING:  skipping tablespace change of "pg_authid"
+DETAIL:  Cannot move system relation, only VACUUM is performed.
+VACUUM (ANALYSE) TABLESPACE regress_tblspace; -- fail
+ERROR:  incompatible SET TABLESPACE option
+DETAIL:  You can only use SET TABLESPACE with VACUUM FULL.
 -- check that all relations moved to new tablespace
 SELECT relname FROM pg_class
 WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+AND relname IN ('regress_tblspace_test_tbl_idx', 'regress_tblspace_test_tbl')
 ORDER BY relname;
             relname            
 -------------------------------
- pg_am_name_index
- pg_am_oid_index
+ regress_tblspace_test_tbl
  regress_tblspace_test_tbl_idx
-(3 rows)
+(2 rows)
 
 -- move back to pg_default tablespace
 REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl TABLESPACE pg_default; -- ok
-REINDEX TABLE pg_am TABLESPACE pg_default; -- ok
+CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE pg_default; -- ok
+VACUUM (FULL, ANALYSE, FREEZE) TABLESPACE pg_default regress_tblspace_test_tbl; -- ok
 -- check that all relations moved back to pg_default
 SELECT relname FROM pg_class
-WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+AND relname IN ('regress_tblspace_test_tbl_idx', 'regress_tblspace_test_tbl');
  relname 
 ---------
 (0 rows)
-- 
2.17.0

--17pEHd4RhPHOinZp
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v10-0003-Capitalize-consistently.patch"