v8-0001-Allow-REINDEX-to-change-tablespace.patch

From d2b7a5fa2e11601759b47af0c142a7824ef907a2 Mon Sep 17 00:00:00 2001
From: Alexey Kondratov <kondratov.aleksey@gmail.com>
Date: Mon, 30 Dec 2019 20:00:37 +0300
Subject: [PATCH v8] Allow REINDEX to change tablespace

REINDEX already does full relation rewrite, this patch adds a
possibility to specify a new tablespace where new relfilenode
will be created.
---
 doc/src/sgml/ref/reindex.sgml             | 24 +++++-
 src/backend/catalog/index.c               | 75 ++++++++++++++++--
 src/backend/commands/cluster.c            |  2 +-
 src/backend/commands/indexcmds.c          | 96 ++++++++++++++++++++---
 src/backend/commands/tablecmds.c          |  2 +-
 src/backend/nodes/copyfuncs.c             |  1 +
 src/backend/nodes/equalfuncs.c            |  1 +
 src/backend/parser/gram.y                 | 14 ++--
 src/backend/tcop/utility.c                |  6 +-
 src/bin/psql/tab-complete.c               |  6 ++
 src/include/catalog/index.h               |  7 +-
 src/include/commands/defrem.h             |  6 +-
 src/include/nodes/parsenodes.h            |  1 +
 src/test/regress/input/tablespace.source  | 49 ++++++++++++
 src/test/regress/output/tablespace.source | 66 ++++++++++++++++
 15 files changed, 323 insertions(+), 33 deletions(-)

diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index c54a7c420d4..0628c94bb1e 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable>
+REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable> [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ]
 
 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
 
@@ -174,6 +174,28 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>TABLESPACE</literal></term>
+    <listitem>
+     <para>
+      This specifies a tablespace, where all rebuilt indexes will be created.
+      Cannot be used with "mapped" relations. If <literal>SCHEMA</literal>,
+      <literal>DATABASE</literal> or <literal>SYSTEM</literal> is specified, then
+      all unsuitable relations will be skipped and a single <literal>WARNING</literal>
+      will be generated.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">new_tablespace</replaceable></term>
+    <listitem>
+     <para>
+      The name of the specific tablespace to store rebuilt indexes.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>VERBOSE</literal></term>
     <listitem>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 8880586c372..fd07379118c 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1235,9 +1235,13 @@ index_create(Relation heapRelation,
  * Create concurrently an index based on the definition of the one provided by
  * caller.  The index is inserted into catalogs and needs to be built later
  * on.  This is called during concurrent reindex processing.
+ *
+ * "tablespaceOid" is the new tablespace to use for this index.  If
+ * InvalidOid, use the tablespace in-use instead.
  */
 Oid
-index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, const char *newName)
+index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
+							   Oid tablespaceOid, const char *newName)
 {
 	Relation	indexRelation;
 	IndexInfo  *oldInfo,
@@ -1367,7 +1371,8 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, const char
 							  newInfo,
 							  indexColNames,
 							  indexRelation->rd_rel->relam,
-							  indexRelation->rd_rel->reltablespace,
+							  OidIsValid(tablespaceOid) ?
+								tablespaceOid : indexRelation->rd_rel->reltablespace,
 							  indexRelation->rd_indcollation,
 							  indclass->values,
 							  indcoloptions->values,
@@ -3408,10 +3413,12 @@ IndexGetRelation(Oid indexId, bool missing_ok)
 
 /*
  * reindex_index - This routine is used to recreate a single index
+ *
+ * See comments of reindex_relation() for details about "tablespaceOid".
  */
 void
-reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
-			  int options)
+reindex_index(Oid indexId, Oid tablespaceOid, bool skip_constraint_checks,
+			  char persistence, int options)
 {
 	Relation	iRel,
 				heapRelation;
@@ -3458,6 +3465,16 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
 		elog(ERROR, "unsupported relation kind for index \"%s\"",
 			 RelationGetRelationName(iRel));
 
+	/*
+	 * We cannot support moving mapped relations into different tablespaces.
+	 * (In particular this eliminates all shared catalogs.)
+	 */
+	if (OidIsValid(tablespaceOid) && RelationIsMapped(iRel))
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("cannot move system relation \"%s\"",
+						RelationGetRelationName(iRel))));
+
 	/*
 	 * Don't allow reindex on temp tables of other backends ... their local
 	 * buffer manager is not going to cope.
@@ -3473,6 +3490,45 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
 	 */
 	CheckTableNotInUse(iRel, "REINDEX INDEX");
 
+	/*
+	 * Set the new tablespace for the relation.  Do that only in the
+	 * case where the reindex caller wishes to enforce a new tablespace.
+	 */
+	if (OidIsValid(tablespaceOid) &&
+		tablespaceOid != iRel->rd_rel->reltablespace)
+	{
+		Relation		pg_class;
+		Form_pg_class	rd_rel;
+		HeapTuple		tuple;
+
+		/* First get a modifiable copy of the relation's pg_class row */
+		pg_class = table_open(RelationRelationId, RowExclusiveLock);
+
+		tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(indexId));
+		if (!HeapTupleIsValid(tuple))
+			elog(ERROR, "cache lookup failed for relation %u", indexId);
+		rd_rel = (Form_pg_class) GETSTRUCT(tuple);
+
+		/*
+		 * Mark the relation as ready to be dropped at transaction commit,
+		 * before making visible the new tablespace change so as this won't
+		 * miss things.
+		 */
+		RelationDropStorage(iRel);
+
+		/* Update the pg_class row */
+		rd_rel->reltablespace = (tablespaceOid == MyDatabaseTableSpace) ?
+			InvalidOid : tablespaceOid;
+		CatalogTupleUpdate(pg_class, &tuple->t_self, tuple);
+
+		heap_freetuple(tuple);
+
+		table_close(pg_class, RowExclusiveLock);
+
+		/* Make sure the reltablespace change is visible */
+		CommandCounterIncrement();
+	}
+
 	/*
 	 * All predicate locks on the index are about to be made invalid. Promote
 	 * them to relation locks on the heap.
@@ -3611,6 +3667,10 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
  * reindex_relation - This routine is used to recreate all indexes
  * of a relation (and optionally its toast relation too, if any).
  *
+ * "tablespaceOid" defines the new tablespace where the indexes of
+ * the relation will be rebuilt.  If InvalidOid is used, the current
+ * tablespace of each index is used instead.
+ *
  * "flags" is a bitmask that can include any combination of these bits:
  *
  * REINDEX_REL_PROCESS_TOAST: if true, process the toast table too (if any).
@@ -3643,7 +3703,7 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
  * index rebuild.
  */
 bool
-reindex_relation(Oid relid, int flags, int options)
+reindex_relation(Oid relid, Oid tablespaceOid, int flags, int options)
 {
 	Relation	rel;
 	Oid			toast_relid;
@@ -3717,7 +3777,8 @@ reindex_relation(Oid relid, int flags, int options)
 		{
 			Oid			indexOid = lfirst_oid(indexId);
 
-			reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
+			reindex_index(indexOid, tablespaceOid,
+						  !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
 						  persistence, options);
 
 			CommandCounterIncrement();
@@ -3750,7 +3811,7 @@ reindex_relation(Oid relid, int flags, int options)
 	 * still hold the lock on the master table.
 	 */
 	if ((flags & REINDEX_REL_PROCESS_TOAST) && OidIsValid(toast_relid))
-		result |= reindex_relation(toast_relid, flags, options);
+		result |= reindex_relation(toast_relid, tablespaceOid, flags, options);
 
 	return result;
 }
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 11ce1bb4046..9e5b28903aa 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -1406,7 +1406,7 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
 	pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
 								 PROGRESS_CLUSTER_PHASE_REBUILD_INDEX);
 
-	reindex_relation(OIDOldHeap, reindex_flags, 0);
+	reindex_relation(OIDOldHeap, InvalidOid, reindex_flags, 0);
 
 	/* Report that we are now doing clean up */
 	pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index ec20ba38d13..d508f552075 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -87,7 +87,7 @@ static char *ChooseIndexNameAddition(List *colnames);
 static List *ChooseIndexColumnNames(List *indexElems);
 static void RangeVarCallbackForReindexIndex(const RangeVar *relation,
 											Oid relId, Oid oldRelId, void *arg);
-static bool ReindexRelationConcurrently(Oid relationOid, int options);
+static bool ReindexRelationConcurrently(Oid relationOid, Oid tablespaceOid, int options);
 static void ReindexPartitionedIndex(Relation parentIdx);
 static void update_relispartition(Oid relationId, bool newval);
 
@@ -2331,10 +2331,11 @@ ChooseIndexColumnNames(List *indexElems)
  *		Recreate a specific index.
  */
 void
-ReindexIndex(RangeVar *indexRelation, int options, bool concurrent)
+ReindexIndex(RangeVar *indexRelation, char *newTableSpaceName, int options, bool concurrent)
 {
 	struct ReindexIndexCallbackState state;
 	Oid			indOid;
+	Oid			tablespaceOid = InvalidOid;
 	Relation	irel;
 	char		persistence;
 
@@ -2369,12 +2370,26 @@ ReindexIndex(RangeVar *indexRelation, int options, bool concurrent)
 	}
 
 	persistence = irel->rd_rel->relpersistence;
+
+	/* Define new tablespaceOid if it is wanted by caller */
+	if (newTableSpaceName)
+	{
+		tablespaceOid = get_tablespace_oid(newTableSpaceName, false);
+
+		/* Can't move a non-shared relation into pg_global */
+		if (tablespaceOid == GLOBALTABLESPACE_OID)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("cannot move non-shared relation to tablespace \"%s\"",
+							newTableSpaceName)));
+	}
+
 	index_close(irel, NoLock);
 
 	if (concurrent && persistence != RELPERSISTENCE_TEMP)
-		ReindexRelationConcurrently(indOid, options);
+		ReindexRelationConcurrently(indOid, tablespaceOid, options);
 	else
-		reindex_index(indOid, false, persistence,
+		reindex_index(indOid, tablespaceOid, false, persistence,
 					  options | REINDEXOPT_REPORT_PROGRESS);
 }
 
@@ -2453,10 +2468,11 @@ RangeVarCallbackForReindexIndex(const RangeVar *relation,
  *		Recreate all indexes of a table (and of its toast table, if any)
  */
 Oid
-ReindexTable(RangeVar *relation, int options, bool concurrent)
+ReindexTable(RangeVar *relation, char *newTableSpaceName, int options, bool concurrent)
 {
 	Oid			heapOid;
 	bool		result;
+	Oid 		tablespaceOid = InvalidOid;
 
 	/*
 	 * The lock level used here should match reindex_relation().
@@ -2471,9 +2487,22 @@ ReindexTable(RangeVar *relation, int options, bool concurrent)
 									   0,
 									   RangeVarCallbackOwnsTable, NULL);
 
+	/* Define new tablespaceOid if it is wanted by caller */
+	if (newTableSpaceName)
+	{
+		tablespaceOid = get_tablespace_oid(newTableSpaceName, false);
+
+		/* Can't move a non-shared relation into pg_global */
+		if (tablespaceOid == GLOBALTABLESPACE_OID)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("cannot move non-shared relation to tablespace \"%s\"",
+							newTableSpaceName)));
+	}
+
 	if (concurrent && get_rel_persistence(heapOid) != RELPERSISTENCE_TEMP)
 	{
-		result = ReindexRelationConcurrently(heapOid, options);
+		result = ReindexRelationConcurrently(heapOid, tablespaceOid, options);
 
 		if (!result)
 			ereport(NOTICE,
@@ -2483,6 +2512,7 @@ ReindexTable(RangeVar *relation, int options, bool concurrent)
 	else
 	{
 		result = reindex_relation(heapOid,
+								  tablespaceOid,
 								  REINDEX_REL_PROCESS_TOAST |
 								  REINDEX_REL_CHECK_CONSTRAINTS,
 								  options | REINDEXOPT_REPORT_PROGRESS);
@@ -2504,10 +2534,11 @@ ReindexTable(RangeVar *relation, int options, bool concurrent)
  * That means this must not be called within a user transaction block!
  */
 void
-ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
+ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, char *newTableSpaceName,
 					  int options, bool concurrent)
 {
 	Oid			objectOid;
+	Oid			tablespaceOid = InvalidOid;
 	Relation	relationRelation;
 	TableScanDesc scan;
 	ScanKeyData scan_keys[1];
@@ -2518,6 +2549,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
 	ListCell   *l;
 	int			num_keys;
 	bool		concurrent_warning = false;
+	bool		mapped_warning = false;
 
 	AssertArg(objectName);
 	Assert(objectKind == REINDEX_OBJECT_SCHEMA ||
@@ -2556,6 +2588,19 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
 						   objectName);
 	}
 
+	/* Define new tablespaceOid if it is wanted by caller */
+	if (newTableSpaceName)
+	{
+		tablespaceOid = get_tablespace_oid(newTableSpaceName, false);
+
+		/* Can't move a non-shared relation into pg_global */
+		if (tablespaceOid == GLOBALTABLESPACE_OID)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("cannot move non-shared relation to tablespace \"%s\"",
+							newTableSpaceName)));
+	}
+
 	/*
 	 * Create a memory context that will survive forced transaction commits we
 	 * do below.  Since it is a child of PortalContext, it will go away
@@ -2646,6 +2691,22 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
 			continue;
 		}
 
+		/*
+		 * Skip all mapped relations if TABLESPACE is specified.
+		 * relfilenode == 0 checks after that, similarly to
+		 * RelationIsMapped().
+		 */
+		if (OidIsValid(tablespaceOid) &&
+			!OidIsValid(classtuple->relfilenode))
+		{
+			if (!mapped_warning)
+				ereport(WARNING,
+						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+						 errmsg("cannot change tablespace of indexes for mapped relations, skipping all")));
+			mapped_warning = true;
+			continue;
+		}
+
 		/* Save the list of relation OIDs in private context */
 		old = MemoryContextSwitchTo(private_context);
 
@@ -2679,7 +2740,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
 
 		if (concurrent && get_rel_persistence(relid) != RELPERSISTENCE_TEMP)
 		{
-			(void) ReindexRelationConcurrently(relid, options);
+			(void) ReindexRelationConcurrently(relid, tablespaceOid, options);
 			/* ReindexRelationConcurrently() does the verbose output */
 		}
 		else
@@ -2687,6 +2748,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
 			bool		result;
 
 			result = reindex_relation(relid,
+									  tablespaceOid,
 									  REINDEX_REL_PROCESS_TOAST |
 									  REINDEX_REL_CHECK_CONSTRAINTS,
 									  options | REINDEXOPT_REPORT_PROGRESS);
@@ -2719,6 +2781,9 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
  * itself will be rebuilt.  If 'relationOid' belongs to a partitioned table
  * then we issue a warning to mention these are not yet supported.
  *
+ * 'tablespaceOid' defines the new tablespace where the indexes of
+ * the relation will be rebuilt.
+ *
  * The locks taken on parent tables and involved indexes are kept until the
  * transaction is committed, at which point a session lock is taken on each
  * relation.  Both of these protect against concurrent schema changes.
@@ -2733,7 +2798,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
  * anyway, and a non-concurrent reindex is more efficient.
  */
 static bool
-ReindexRelationConcurrently(Oid relationOid, int options)
+ReindexRelationConcurrently(Oid relationOid, Oid tablespaceOid, int options)
 {
 	List	   *heapRelationIds = NIL;
 	List	   *indexIds = NIL;
@@ -2806,6 +2871,12 @@ ReindexRelationConcurrently(Oid relationOid, int options)
 				/* Open relation to get its indexes */
 				heapRelation = table_open(relationOid, ShareUpdateExclusiveLock);
 
+				if (OidIsValid(tablespaceOid) && RelationIsMapped(heapRelation))
+					ereport(ERROR,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							 errmsg("cannot change tablespace of indexes for mapped relation \"%s\"",
+									RelationGetRelationName(heapRelation))));
+
 				/* Add all the valid indexes of relation to list */
 				foreach(lc, RelationGetIndexList(heapRelation))
 				{
@@ -2978,6 +3049,12 @@ ReindexRelationConcurrently(Oid relationOid, int options)
 		if (indexRel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
 			elog(ERROR, "cannot reindex a temporary table concurrently");
 
+		if (OidIsValid(tablespaceOid) && RelationIsMapped(heapRel))
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("cannot change tablespace of mapped relation \"%s\"",
+							RelationGetRelationName(heapRel))));
+
 		pgstat_progress_start_command(PROGRESS_COMMAND_CREATE_INDEX,
 									  RelationGetRelid(heapRel));
 		pgstat_progress_update_param(PROGRESS_CREATEIDX_COMMAND,
@@ -2997,6 +3074,7 @@ ReindexRelationConcurrently(Oid relationOid, int options)
 		/* Create new index definition based on given index */
 		newIndexId = index_concurrently_create_copy(heapRel,
 													indexId,
+													tablespaceOid,
 													concurrentName);
 
 		/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 02a7c04fdb7..9d2185605fb 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1870,7 +1870,7 @@ ExecuteTruncateGuts(List *explicit_rels, List *relids, List *relids_logged,
 			/*
 			 * Reconstruct the indexes to match, and we're done.
 			 */
-			reindex_relation(heap_relid, REINDEX_REL_PROCESS_TOAST, 0);
+			reindex_relation(heap_relid, InvalidOid, REINDEX_REL_PROCESS_TOAST, 0);
 		}
 
 		pgstat_count_truncate(rel);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index e04c33e4ad7..83ab90c6123 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4395,6 +4395,7 @@ _copyReindexStmt(const ReindexStmt *from)
 	COPY_STRING_FIELD(name);
 	COPY_SCALAR_FIELD(options);
 	COPY_SCALAR_FIELD(concurrent);
+	COPY_STRING_FIELD(tablespacename);
 
 	return newnode;
 }
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 5b1ba143b1c..e63ed0729b9 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2122,6 +2122,7 @@ _equalReindexStmt(const ReindexStmt *a, const ReindexStmt *b)
 	COMPARE_STRING_FIELD(name);
 	COMPARE_SCALAR_FIELD(options);
 	COMPARE_SCALAR_FIELD(concurrent);
+	COMPARE_STRING_FIELD(tablespacename);
 
 	return true;
 }
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 96e7fdbcfe2..5b47fb664a7 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -8384,31 +8384,33 @@ DropTransformStmt: DROP TRANSFORM opt_if_exists FOR Typename LANGUAGE name opt_d
  *
  *		QUERY:
  *
- *		REINDEX [ (options) ] type [CONCURRENTLY] <name>
+ *		REINDEX [ (options) ] type [CONCURRENTLY] <name> [ TABLESPACE <tablespace_name> ]
  *****************************************************************************/
 
 ReindexStmt:
-			REINDEX reindex_target_type opt_concurrently qualified_name
+			REINDEX reindex_target_type opt_concurrently qualified_name OptTableSpace
 				{
 					ReindexStmt *n = makeNode(ReindexStmt);
 					n->kind = $2;
 					n->concurrent = $3;
 					n->relation = $4;
+					n->tablespacename = $5;
 					n->name = NULL;
 					n->options = 0;
 					$$ = (Node *)n;
 				}
-			| REINDEX reindex_target_multitable opt_concurrently name
+			| REINDEX reindex_target_multitable opt_concurrently name OptTableSpace
 				{
 					ReindexStmt *n = makeNode(ReindexStmt);
 					n->kind = $2;
 					n->concurrent = $3;
 					n->name = $4;
+					n->tablespacename = $5;
 					n->relation = NULL;
 					n->options = 0;
 					$$ = (Node *)n;
 				}
-			| REINDEX '(' reindex_option_list ')' reindex_target_type opt_concurrently qualified_name
+			| REINDEX '(' reindex_option_list ')' reindex_target_type opt_concurrently qualified_name OptTableSpace
 				{
 					ReindexStmt *n = makeNode(ReindexStmt);
 					n->kind = $5;
@@ -8416,9 +8418,10 @@ ReindexStmt:
 					n->relation = $7;
 					n->name = NULL;
 					n->options = $3;
+					n->tablespacename = $8;
 					$$ = (Node *)n;
 				}
-			| REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name
+			| REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name OptTableSpace
 				{
 					ReindexStmt *n = makeNode(ReindexStmt);
 					n->kind = $5;
@@ -8426,6 +8429,7 @@ ReindexStmt:
 					n->name = $7;
 					n->relation = NULL;
 					n->options = $3;
+					n->tablespacename = $8;
 					$$ = (Node *)n;
 				}
 		;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index bb85b5e52aa..3c5c2150027 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -933,10 +933,10 @@ standard_ProcessUtility(PlannedStmt *pstmt,
 				switch (stmt->kind)
 				{
 					case REINDEX_OBJECT_INDEX:
-						ReindexIndex(stmt->relation, stmt->options, stmt->concurrent);
+						ReindexIndex(stmt->relation, stmt->tablespacename, stmt->options, stmt->concurrent);
 						break;
 					case REINDEX_OBJECT_TABLE:
-						ReindexTable(stmt->relation, stmt->options, stmt->concurrent);
+						ReindexTable(stmt->relation, stmt->tablespacename, stmt->options, stmt->concurrent);
 						break;
 					case REINDEX_OBJECT_SCHEMA:
 					case REINDEX_OBJECT_SYSTEM:
@@ -952,7 +952,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
 												  (stmt->kind == REINDEX_OBJECT_SCHEMA) ? "REINDEX SCHEMA" :
 												  (stmt->kind == REINDEX_OBJECT_SYSTEM) ? "REINDEX SYSTEM" :
 												  "REINDEX DATABASE");
-						ReindexMultipleTables(stmt->name, stmt->kind, stmt->options, stmt->concurrent);
+						ReindexMultipleTables(stmt->name, stmt->kind, stmt->tablespacename, stmt->options, stmt->concurrent);
 						break;
 					default:
 						elog(ERROR, "unrecognized object type: %d",
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b6b08d0ccb6..5df5c80854c 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3424,6 +3424,12 @@ psql_completion(const char *text, int start, int end)
 		COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
 	else if (Matches("REINDEX", "SYSTEM|DATABASE", "CONCURRENTLY"))
 		COMPLETE_WITH_QUERY(Query_for_list_of_databases);
+	else if (Matches("REINDEX", MatchAny, "CONCURRENTLY", MatchAny))
+		COMPLETE_WITH("TABLESPACE");
+	else if (Matches("REINDEX", MatchAny, MatchAny))
+		COMPLETE_WITH("TABLESPACE");
+	else if (TailMatches("TABLESPACE"))
+		COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
 
 /* SECURITY LABEL */
 	else if (Matches("SECURITY"))
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index a2890c1314d..f38e978b45d 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -80,6 +80,7 @@ extern Oid	index_create(Relation heapRelation,
 
 extern Oid	index_concurrently_create_copy(Relation heapRelation,
 										   Oid oldIndexId,
+										   Oid tablespaceOid,
 										   const char *newName);
 
 extern void index_concurrently_build(Oid heapRelationId,
@@ -131,8 +132,8 @@ extern void validate_index(Oid heapId, Oid indexId, Snapshot snapshot);
 
 extern void index_set_state_flags(Oid indexId, IndexStateFlagsAction action);
 
-extern void reindex_index(Oid indexId, bool skip_constraint_checks,
-						  char relpersistence, int options);
+extern void reindex_index(Oid indexId, Oid tablespaceOid, bool skip_constraint_checks,
+			  char relpersistence, int options);
 
 /* Flag bits for reindex_relation(): */
 #define REINDEX_REL_PROCESS_TOAST			0x01
@@ -141,7 +142,7 @@ extern void reindex_index(Oid indexId, bool skip_constraint_checks,
 #define REINDEX_REL_FORCE_INDEXES_UNLOGGED	0x08
 #define REINDEX_REL_FORCE_INDEXES_PERMANENT 0x10
 
-extern bool reindex_relation(Oid relid, int flags, int options);
+extern bool reindex_relation(Oid relid, Oid tablespaceOid, int flags, int options);
 
 extern bool ReindexIsProcessingHeap(Oid heapOid);
 extern bool ReindexIsProcessingIndex(Oid indexOid);
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index dede9d788ec..4999e590e67 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -34,10 +34,10 @@ extern ObjectAddress DefineIndex(Oid relationId,
 								 bool check_not_in_use,
 								 bool skip_build,
 								 bool quiet);
-extern void ReindexIndex(RangeVar *indexRelation, int options, bool concurrent);
-extern Oid	ReindexTable(RangeVar *relation, int options, bool concurrent);
+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,
-								  int options, bool concurrent);
+								  char *newTableSpaceName, int options, bool concurrent);
 extern char *makeObjectName(const char *name1, const char *name2,
 							const char *label);
 extern char *ChooseRelationName(const char *name1, const char *name2,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index da0706add59..7900bb07265 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3354,6 +3354,7 @@ typedef struct ReindexStmt
 	const char *name;			/* name of database to reindex */
 	int			options;		/* Reindex options flags */
 	bool		concurrent;		/* reindex concurrently? */
+	char	   *tablespacename; /* name of tablespace to store index */
 } ReindexStmt;
 
 /* ----------------------
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index a5f61a35dc5..7d698d4294d 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -17,6 +17,52 @@ 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 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
+  FROM generate_series(1, 20000) s(i);
+CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1);
+
+-- check that REINDEX with TABLESPACE change is transactional
+BEGIN;
+REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace;
+REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace;
+ROLLBACK;
+BEGIN;
+REINDEX TABLE pg_am TABLESPACE regress_tblspace;
+ROLLBACK;
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+
+-- first, let us reindex and move the entire database, after that return everything back
+REINDEX DATABASE regression TABLESPACE regress_tblspace; -- ok with warning
+REINDEX DATABASE regression TABLESPACE pg_default; -- ok with warning
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+
+-- check REINDEX with TABLESPACE change
+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 INDEX regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail
+REINDEX TABLE pg_am TABLESPACE regress_tblspace; -- ok
+
+-- check that all relations moved to new tablespace
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+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
+
+-- 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');
+
 -- create a schema we can use
 CREATE SCHEMA testschema;
 
@@ -279,6 +325,9 @@ ALTER TABLE ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default
 -- Should succeed
 DROP TABLESPACE regress_tblspace_renamed;
 
+DROP INDEX regress_tblspace_test_tbl_idx;
+DROP TABLE regress_tblspace_test_tbl;
+
 DROP SCHEMA testschema CASCADE;
 
 DROP ROLE regress_tablespace_user1;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 162b591b315..bd17feaa73f 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -20,6 +20,70 @@ 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 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
+  FROM generate_series(1, 20000) s(i);
+CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1);
+-- check that REINDEX with TABLESPACE change is transactional
+BEGIN;
+REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace;
+REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace;
+ROLLBACK;
+BEGIN;
+REINDEX TABLE pg_am TABLESPACE regress_tblspace;
+ROLLBACK;
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+ relname 
+---------
+(0 rows)
+
+-- first, let us reindex and move the entire database, after that return everything back
+REINDEX DATABASE regression TABLESPACE regress_tblspace; -- ok with warning
+WARNING:  cannot change tablespace of indexes for mapped relations, skipping all
+REINDEX DATABASE regression TABLESPACE pg_default; -- ok with warning
+WARNING:  cannot change tablespace of indexes for mapped relations, skipping all
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+ relname 
+---------
+(0 rows)
+
+-- check REINDEX with TABLESPACE change
+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 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
+-- check that all relations moved to new tablespace
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+ORDER BY relname;
+            relname            
+-------------------------------
+ pg_am_name_index
+ pg_am_oid_index
+ regress_tblspace_test_tbl_idx
+(3 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
+-- 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');
+ relname 
+---------
+(0 rows)
+
 -- create a schema we can use
 CREATE SCHEMA testschema;
 -- try a table
@@ -736,6 +800,8 @@ ALTER TABLE ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default
 NOTICE:  no matching relations in tablespace "regress_tblspace_renamed" found
 -- Should succeed
 DROP TABLESPACE regress_tblspace_renamed;
+DROP INDEX regress_tblspace_test_tbl_idx;
+DROP TABLE regress_tblspace_test_tbl;
 DROP SCHEMA testschema CASCADE;
 NOTICE:  drop cascades to 6 other objects
 DETAIL:  drop cascades to table testschema.foo

base-commit: 80d76be51cfb7f8f54e779ec5a287d80ac28b6e4
-- 
2.19.1