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

From f176a6e5a81ab133fee849f72e4edb8b287d6062 Mon Sep 17 00:00:00 2001
From: Alexey Kondratov <kondratov.aleksey@gmail.com>
Date: Wed, 27 Jan 2021 00:46:17 +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             |  31 +++-
 src/backend/catalog/index.c               |  50 +++++-
 src/backend/commands/indexcmds.c          | 112 ++++++++++++-
 src/bin/psql/tab-complete.c               |   4 +-
 src/include/catalog/index.h               |   9 +-
 src/test/regress/input/tablespace.source  | 106 +++++++++++++
 src/test/regress/output/tablespace.source | 181 ++++++++++++++++++++++
 7 files changed, 481 insertions(+), 12 deletions(-)

diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 627b36300c..e610a0f52c 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -27,6 +27,7 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
 
     CONCURRENTLY [ <replaceable class="parameter">boolean</replaceable> ]
     VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
+    TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
 </synopsis>
  </refsynopsisdiv>
 
@@ -187,6 +188,21 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>TABLESPACE</literal></term>
+    <listitem>
+     <para>
+      Specifies that indexes will be rebuilt on a new tablespace.
+      Cannot be used with "mapped" and system (unless <varname>allow_system_table_mods</varname>
+      is set to <literal>TRUE</literal>) relations. If <literal>SCHEMA</literal>,
+      <literal>DATABASE</literal> or <literal>SYSTEM</literal> are specified,
+      then all "mapped" and system relations will be skipped and a single
+      <literal>WARNING</literal> will be generated. Indexes on TOAST tables
+      are reindexed, but not moved the new tablespace.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>VERBOSE</literal></term>
     <listitem>
@@ -210,6 +226,14 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><replaceable class="parameter">new_tablespace</replaceable></term>
+    <listitem>
+     <para>
+      The tablespace where indexes will be rebuilt.
+     </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
  </refsect1>
 
@@ -292,7 +316,12 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
    with <command>REINDEX INDEX</command> or <command>REINDEX TABLE</command>,
    respectively. Each partition of the specified partitioned relation is
    reindexed in a separate transaction. Those commands cannot be used inside
-   a transaction block when working on a partitioned table or index.
+   a transaction block when working on a partitioned table or index. If
+   a <command>REINDEX</command> command fails when run on a partitioned
+   relation, and <literal>TABLESPACE</literal> was specified, then it may have
+   moved indexes on some partitions to the new tablespace.  Re-running the command
+   will reindex all partitions and move previously-unprocessed indexes to the new
+   tablespace.
   </para>
 
   <refsect2 id="sql-reindex-concurrently" xreflabel="Rebuilding Indexes Concurrently">
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index b8cd35e995..06cb3c8c55 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -57,6 +57,7 @@
 #include "commands/event_trigger.h"
 #include "commands/progress.h"
 #include "commands/tablecmds.h"
+#include "commands/tablespace.h"
 #include "commands/trigger.h"
 #include "executor/executor.h"
 #include "miscadmin.h"
@@ -1394,9 +1395,12 @@ index_update_collation_versions(Oid relid, Oid coll)
  * 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 tablespace to use for this index.
  */
 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,
@@ -1526,7 +1530,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, const char
 							  newInfo,
 							  indexColNames,
 							  indexRelation->rd_rel->relam,
-							  indexRelation->rd_rel->reltablespace,
+							  tablespaceOid,
 							  indexRelation->rd_indcollation,
 							  indclass->values,
 							  indcoloptions->values,
@@ -3603,6 +3607,7 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
 	volatile bool skipped_constraint = false;
 	PGRUsage	ru0;
 	bool		progress = ((params->options & REINDEXOPT_REPORT_PROGRESS) != 0);
+	bool		set_tablespace = OidIsValid(params->tablespaceOid);
 
 	pg_rusage_init(&ru0);
 
@@ -3654,14 +3659,20 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
 			 get_namespace_name(RelationGetNamespace(iRel)),
 			 RelationGetRelationName(iRel));
 
+	if (set_tablespace)
+		set_tablespace = CheckRelationTableSpaceMove(iRel,
+													 params->tablespaceOid);
+
 	/*
-	 * Don't allow reindex on temp tables of other backends ... their local
-	 * buffer manager is not going to cope.
+	 * We don't support moving system relations into different tablespaces
+	 * unless allow_system_table_mods=1.
 	 */
-	if (RELATION_IS_OTHER_TEMP(iRel))
+	if (set_tablespace &&
+		!allowSystemTableMods && IsSystemRelation(iRel))
 		ereport(ERROR,
-				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-				 errmsg("cannot reindex temporary tables of other sessions")));
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied: \"%s\" is a system catalog",
+						RelationGetRelationName(iRel))));
 
 	/*
 	 * Don't allow reindex of an invalid index on TOAST table.  This is a
@@ -3680,6 +3691,27 @@ 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 (set_tablespace)
+	{
+		SetRelationTableSpace(iRel, params->tablespaceOid, InvalidOid);
+
+		/*
+		 * 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);
+
+		RelationAssumeNewRelfilenode(iRel);
+
+		/* 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.
@@ -3964,10 +3996,14 @@ reindex_relation(Oid relid, int flags, ReindexParams *params)
 		/*
 		 * Note that this should fail if the toast relation is missing, so
 		 * reset REINDEXOPT_MISSING_OK.
+		 *
+		 * Even if table's indexes were moved to a new tablespace, the index
+		 * on its toast table is not moved implicitly.
 		 */
 		ReindexParams newparams = *params;
 
 		newparams.options &= ~(REINDEXOPT_MISSING_OK);
+		newparams.tablespaceOid = InvalidOid;
 		result |= reindex_relation(toast_relid, flags, &newparams);
 	}
 
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index f9f3ff3b62..b3eaf7cab4 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -2474,6 +2474,7 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 	ListCell   *lc;
 	bool		concurrently = false;
 	bool		verbose = false;
+	char		*tablespace = NULL;
 
 	/* Parse option list */
 	foreach(lc, stmt->params)
@@ -2484,6 +2485,8 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 			verbose = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "concurrently") == 0)
 			concurrently = defGetBoolean(opt);
+		else if (strcmp(opt->defname, "tablespace") == 0)
+			tablespace = defGetString(opt);
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_SYNTAX_ERROR),
@@ -2500,6 +2503,22 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 		(verbose ? REINDEXOPT_VERBOSE : 0) |
 		(concurrently ? REINDEXOPT_CONCURRENTLY : 0);
 
+	params.tablespaceOid = tablespace != NULL ?
+		get_tablespace_oid(tablespace, false) : InvalidOid;
+
+	/* Check permissions except when using database's default */
+	if (OidIsValid(params.tablespaceOid) &&
+		params.tablespaceOid != MyDatabaseTableSpace)
+	{
+		AclResult	aclresult;
+
+		aclresult = pg_tablespace_aclcheck(params.tablespaceOid,
+										   GetUserId(), ACL_CREATE);
+		if (aclresult != ACLCHECK_OK)
+			aclcheck_error(aclresult, OBJECT_TABLESPACE,
+						   get_tablespace_name(params.tablespaceOid));
+	}
+
 	switch (stmt->kind)
 	{
 		case REINDEX_OBJECT_INDEX:
@@ -2730,6 +2749,8 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
 	List	   *relids = NIL;
 	int			num_keys;
 	bool		concurrent_warning = false;
+	bool		tablespace_warning = false;
+	bool		mapped_warning = false;
 
 	AssertArg(objectName);
 	Assert(objectKind == REINDEX_OBJECT_SCHEMA ||
@@ -2856,6 +2877,35 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
 			continue;
 		}
 
+		if (OidIsValid(params->tablespaceOid) &&
+			IsSystemClass(relid, classtuple))
+		{
+			if (!allowSystemTableMods)
+			{
+				/* Skip all system relations, if not allowSystemTableMods */
+				if (!tablespace_warning)
+					ereport(WARNING,
+							(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+							 errmsg("cannot change tablespace of indexes on system relations, skipping all")));
+				tablespace_warning = true;
+				continue;
+			}
+			else if (!OidIsValid(classtuple->relfilenode))
+			{
+				/*
+				 * Skip all mapped relations if TABLESPACE is specified.
+				 * OidIsValid(relfilenode) checks that, similar to
+				 * RelationIsMapped().
+				 */
+				if (!mapped_warning)
+					ereport(WARNING,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							 errmsg("cannot change tablespace of indexes on mapped relations, skipping all")));
+				mapped_warning = true;
+				continue;
+			}
+		}
+
 		/* Save the list of relation OIDs in private context */
 		old = MemoryContextSwitchTo(private_context);
 
@@ -2969,9 +3019,41 @@ ReindexPartitions(Oid relid, ReindexParams *params, bool isTopLevel)
 		MemoryContext old_context;
 
 		/*
-		 * This discards partitioned tables, partitioned indexes and foreign
-		 * tables.
+		 * Foreign tables and partitioned relations are not themselves
+		 * reindexed - leaf partitions are processed directly.  But any
+		 * tablespace change is recorded in the catalog for partitioned
+		 * relations.
 		 */
+		if (partkind == RELKIND_PARTITIONED_INDEX)
+		{
+			Relation iRel = index_open(partoid, ShareLock);
+
+			if (CheckRelationTableSpaceMove(iRel, params->tablespaceOid))
+				SetRelationTableSpace(iRel,
+									  params->tablespaceOid,
+									  InvalidOid);
+			index_close(iRel, NoLock);
+		}
+		else if (partkind == RELKIND_PARTITIONED_TABLE)
+		{
+			Relation rel = table_open(partoid, ShareLock);
+			List	*indexIds = RelationGetIndexList(rel);
+			ListCell *lc;
+
+			table_close(rel, NoLock);
+			foreach (lc, indexIds)
+			{
+				Oid indexid = lfirst_oid(lc);
+				Relation iRel = index_open(indexid, ShareLock);
+
+				if (CheckRelationTableSpaceMove(iRel, params->tablespaceOid))
+					SetRelationTableSpace(iRel,
+										  params->tablespaceOid,
+										  InvalidOid);
+				index_close(iRel, NoLock);
+			}
+		}
+
 		if (!RELKIND_HAS_STORAGE(partkind))
 			continue;
 
@@ -3032,6 +3114,22 @@ ReindexMultipleInternal(List *relids, ReindexParams *params)
 			continue;
 		}
 
+		/*
+		 * ExecReindex() does this check, but here we have to recheck
+		 * it again to be sure that nothing changed between transactions.
+		 */
+		if (OidIsValid(params->tablespaceOid) &&
+			params->tablespaceOid != MyDatabaseTableSpace)
+		{
+			AclResult	aclresult;
+
+			aclresult = pg_tablespace_aclcheck(params->tablespaceOid,
+											   GetUserId(), ACL_CREATE);
+			if (aclresult != ACLCHECK_OK)
+				aclcheck_error(aclresult, OBJECT_TABLESPACE,
+							get_tablespace_name(params->tablespaceOid));
+		}
+
 		relkind = get_rel_relkind(relid);
 		relpersistence = get_rel_persistence(relid);
 
@@ -3390,6 +3488,13 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 		return false;
 	}
 
+	/* It's not a shared catalog, so refuse to move it to shared tablespace */
+	if (params->tablespaceOid == GLOBALTABLESPACE_OID)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("cannot move non-shared relation to tablespace \"%s\"",
+					 get_tablespace_name(params->tablespaceOid))));
+
 	Assert(heapRelationIds != NIL);
 
 	/*-----
@@ -3461,6 +3566,9 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 		/* Create new index definition based on given index */
 		newIndexId = index_concurrently_create_copy(heapRel,
 													idx->indexId,
+													OidIsValid(params->tablespaceOid) ?
+														params->tablespaceOid :
+														indexRel->rd_rel->reltablespace,
 													concurrentName);
 
 		/*
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 17f7265038..a75647b1cc 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3641,7 +3641,9 @@ psql_completion(const char *text, int start, int end)
 		 * one word, so the above test is correct.
 		 */
 		if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
-			COMPLETE_WITH("CONCURRENTLY", "VERBOSE");
+			COMPLETE_WITH("CONCURRENTLY", "TABLESPACE", "VERBOSE");
+		else if (TailMatches("TABLESPACE"))
+			COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
 	}
 
 /* SECURITY LABEL */
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 266f8950dc..8172bff617 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -29,10 +29,16 @@ typedef enum
 	INDEX_DROP_SET_DEAD
 } IndexStateFlagsAction;
 
-/* options for REINDEX */
+/*
+ * Options for REINDEX
+ *
+ * "tablespaceOid" is the tablespace where indexes will be rebuilt,
+ * or InvalidOid to keep each index on its current tablespace.
+ */
 typedef struct ReindexParams
 {
 	bits32		options;		/* bitmask of REINDEXOPT_* */
+	Oid  tablespaceOid;			/* tablespace to rebuild index */
 } ReindexParams;
 
 /* flag bits for ReindexParams->flags */
@@ -92,6 +98,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,
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 1a181016d7..c8a3098bf6 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -17,6 +17,97 @@ 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 (with TOAST) to test REINDEX with TABLESPACE change
+CREATE TABLE regress_tblspace_test_tbl (num1 bigint, num2 double precision, t text);
+INSERT INTO regress_tblspace_test_tbl (num1, num2, t)
+  SELECT round(random()*100), random(), 'text'
+  FROM generate_series(1, 10) s(i);
+CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1);
+CREATE TABLE regress_tblspace_test_pt(col1 int, col2 int) PARTITION BY HASH (col1);
+CREATE TABLE regress_tblspace_test_pt_1
+  PARTITION OF regress_tblspace_test_pt
+  FOR VALUES WITH (modulus 2, remainder 0)
+  PARTITION BY HASH (col2);
+CREATE INDEX regress_tblspace_test_pt_idx_1 ON regress_tblspace_test_pt (col1);
+CREATE INDEX regress_tblspace_test_pt_idx_2 ON regress_tblspace_test_pt (col2);
+
+-- first, check a no-op case
+REINDEX (TABLESPACE pg_default) INDEX regress_tblspace_test_tbl_idx;
+REINDEX (TABLESPACE pg_default) TABLE regress_tblspace_test_tbl;
+REINDEX (TABLESPACE pg_default) INDEX regress_tblspace_test_pt_idx_2;
+REINDEX (TABLESPACE pg_default) TABLE regress_tblspace_test_pt;
+
+-- check that REINDEX with TABLESPACE change is transactional
+BEGIN;
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_tbl_idx;
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl;
+ROLLBACK;
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+
+-- reindex and move the entire database, after that return everything back
+REINDEX (TABLESPACE regress_tblspace) DATABASE regression; -- ok with warning
+REINDEX (TABLESPACE pg_default) DATABASE regression; -- ok with warning
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+
+-- partitioned indexes are not directly reindexed, but we still have to set proper
+-- tablespace in pg_class for them
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_pt; -- ok
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_pt_idx_1; -- ok
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_pt_idx_2; -- ok
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+
+-- indexes for new partitions should be created in the new tablespace
+CREATE TABLE regress_tblspace_test_pt_2
+  PARTITION OF regress_tblspace_test_pt
+  FOR VALUES WITH (modulus 2, remainder 1);
+CREATE TABLE regress_tblspace_test_pt_1_1
+  PARTITION OF regress_tblspace_test_pt_1
+  FOR VALUES WITH (modulus 2, remainder 0);
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+
+-- check REINDEX with TABLESPACE change
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_tbl_idx; -- ok
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl; -- ok
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_authid; -- fail
+REINDEX (TABLESPACE regress_tblspace) SYSTEM CONCURRENTLY postgres; -- fail
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_am; -- fail
+REINDEX (TABLESPACE pg_global) INDEX regress_tblspace_test_tbl_idx; -- fail
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_am; -- 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')
+ORDER BY relname;
+
+CREATE TEMP TABLE old_reindex_info AS SELECT relname, relfilenode FROM pg_class
+WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_1'::regclass) WHERE isleaf = true);
+INSERT INTO old_reindex_info SELECT relname, relfilenode FROM pg_class
+WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_2'::regclass) WHERE isleaf = true);
+INSERT INTO old_reindex_info SELECT relname, relfilenode FROM pg_class
+WHERE relname = 'regress_tblspace_test_tbl_idx';
+
+-- move indexes back to pg_default tablespace
+REINDEX (TABLESPACE pg_default) TABLE CONCURRENTLY regress_tblspace_test_tbl; -- ok
+REINDEX (TABLESPACE pg_default) TABLE regress_tblspace_test_pt; -- 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 TEMP TABLE new_reindex_info AS SELECT relname, relfilenode FROM pg_class
+WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_1'::regclass) WHERE isleaf = true);
+INSERT INTO new_reindex_info SELECT relname, relfilenode FROM pg_class
+WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_2'::regclass) WHERE isleaf = true);
+INSERT INTO new_reindex_info SELECT relname, relfilenode FROM pg_class
+WHERE relname = 'regress_tblspace_test_tbl_idx';
+
+SELECT * FROM new_reindex_info JOIN old_reindex_info ON new_reindex_info.relname = old_reindex_info.relname
+WHERE new_reindex_info.relfilenode = old_reindex_info.relfilenode;
+
 -- create a schema we can use
 CREATE SCHEMA testschema;
 
@@ -96,6 +187,14 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
 \d testschema.part_a_idx
 \d+ testschema.part_a_idx
 
+-- REINDEX partitioned indexes to new tablespace
+REINDEX (TABLESPACE pg_default) TABLE testschema.part;
+\d testschema.part
+\d testschema.part1
+REINDEX (CONCURRENTLY, TABLESPACE regress_tblspace) INDEX testschema.part_a_idx;
+\d testschema.part
+\d testschema.part1
+
 -- partitioned rels cannot specify the default tablespace.  These fail:
 CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
 CREATE TABLE testschema.dflt (a int PRIMARY KEY USING INDEX TABLESPACE pg_default) PARTITION BY LIST (a);
@@ -268,6 +367,10 @@ ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2;
 
 SET SESSION ROLE regress_tablespace_user2;
 CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
+
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY regress_tblspace_test_tbl; -- fail
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_pt_idx; -- fail
+
 ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint;
 RESET ROLE;
 
@@ -282,6 +385,9 @@ ALTER TABLE ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default
 -- Should succeed
 DROP TABLESPACE regress_tblspace_renamed;
 
+DROP TABLE regress_tblspace_test_tbl;
+DROP TABLE regress_tblspace_test_pt;
+
 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 94c5f023c6..b7a9016054 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -20,6 +20,140 @@ 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 (with TOAST) to test REINDEX with TABLESPACE change
+CREATE TABLE regress_tblspace_test_tbl (num1 bigint, num2 double precision, t text);
+INSERT INTO regress_tblspace_test_tbl (num1, num2, t)
+  SELECT round(random()*100), random(), 'text'
+  FROM generate_series(1, 10) s(i);
+CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1);
+CREATE TABLE regress_tblspace_test_pt(col1 int, col2 int) PARTITION BY HASH (col1);
+CREATE TABLE regress_tblspace_test_pt_1
+  PARTITION OF regress_tblspace_test_pt
+  FOR VALUES WITH (modulus 2, remainder 0)
+  PARTITION BY HASH (col2);
+CREATE INDEX regress_tblspace_test_pt_idx_1 ON regress_tblspace_test_pt (col1);
+CREATE INDEX regress_tblspace_test_pt_idx_2 ON regress_tblspace_test_pt (col2);
+-- first, check a no-op case
+REINDEX (TABLESPACE pg_default) INDEX regress_tblspace_test_tbl_idx;
+REINDEX (TABLESPACE pg_default) TABLE regress_tblspace_test_tbl;
+REINDEX (TABLESPACE pg_default) INDEX regress_tblspace_test_pt_idx_2;
+REINDEX (TABLESPACE pg_default) TABLE regress_tblspace_test_pt;
+-- check that REINDEX with TABLESPACE change is transactional
+BEGIN;
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_tbl_idx;
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl;
+ROLLBACK;
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+ relname 
+---------
+(0 rows)
+
+-- reindex and move the entire database, after that return everything back
+REINDEX (TABLESPACE regress_tblspace) DATABASE regression; -- ok with warning
+WARNING:  cannot change tablespace of indexes on system relations, skipping all
+REINDEX (TABLESPACE pg_default) DATABASE regression; -- ok with warning
+WARNING:  cannot change tablespace of indexes on system relations, skipping all
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+ relname 
+---------
+(0 rows)
+
+-- partitioned indexes are not directly reindexed, but we still have to set proper
+-- tablespace in pg_class for them
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_pt; -- ok
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_pt_idx_1; -- ok
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_pt_idx_2; -- ok
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+               relname               
+-------------------------------------
+ regress_tblspace_test_pt_idx_1
+ regress_tblspace_test_pt_idx_2
+ regress_tblspace_test_pt_1_col1_idx
+ regress_tblspace_test_pt_1_col2_idx
+(4 rows)
+
+-- indexes for new partitions should be created in the new tablespace
+CREATE TABLE regress_tblspace_test_pt_2
+  PARTITION OF regress_tblspace_test_pt
+  FOR VALUES WITH (modulus 2, remainder 1);
+CREATE TABLE regress_tblspace_test_pt_1_1
+  PARTITION OF regress_tblspace_test_pt_1
+  FOR VALUES WITH (modulus 2, remainder 0);
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+                relname                
+---------------------------------------
+ regress_tblspace_test_pt_idx_1
+ regress_tblspace_test_pt_idx_2
+ regress_tblspace_test_pt_2_col1_idx
+ regress_tblspace_test_pt_2_col2_idx
+ regress_tblspace_test_pt_1_1_col1_idx
+ regress_tblspace_test_pt_1_col1_idx
+ regress_tblspace_test_pt_1_1_col2_idx
+ regress_tblspace_test_pt_1_col2_idx
+(8 rows)
+
+-- check REINDEX with TABLESPACE change
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_tbl_idx; -- ok
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl; -- ok
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_authid; -- fail
+ERROR:  cannot move system relation "pg_authid_rolname_index"
+REINDEX (TABLESPACE regress_tblspace) SYSTEM CONCURRENTLY postgres; -- fail
+ERROR:  cannot reindex system catalogs concurrently
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_am; -- fail
+ERROR:  cannot reindex system catalogs concurrently
+REINDEX (TABLESPACE pg_global) INDEX regress_tblspace_test_tbl_idx; -- fail
+ERROR:  only shared relations can be placed in pg_global tablespace
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_am; -- fail
+ERROR:  permission denied: "pg_am_name_index" is a system catalog
+-- 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                
+---------------------------------------
+ regress_tblspace_test_pt_1_1_col1_idx
+ regress_tblspace_test_pt_1_1_col2_idx
+ regress_tblspace_test_pt_1_col1_idx
+ regress_tblspace_test_pt_1_col2_idx
+ regress_tblspace_test_pt_2_col1_idx
+ regress_tblspace_test_pt_2_col2_idx
+ regress_tblspace_test_pt_idx_1
+ regress_tblspace_test_pt_idx_2
+ regress_tblspace_test_tbl_idx
+(9 rows)
+
+CREATE TEMP TABLE old_reindex_info AS SELECT relname, relfilenode FROM pg_class
+WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_1'::regclass) WHERE isleaf = true);
+INSERT INTO old_reindex_info SELECT relname, relfilenode FROM pg_class
+WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_2'::regclass) WHERE isleaf = true);
+INSERT INTO old_reindex_info SELECT relname, relfilenode FROM pg_class
+WHERE relname = 'regress_tblspace_test_tbl_idx';
+-- move indexes back to pg_default tablespace
+REINDEX (TABLESPACE pg_default) TABLE CONCURRENTLY regress_tblspace_test_tbl; -- ok
+REINDEX (TABLESPACE pg_default) TABLE regress_tblspace_test_pt; -- 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 TEMP TABLE new_reindex_info AS SELECT relname, relfilenode FROM pg_class
+WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_1'::regclass) WHERE isleaf = true);
+INSERT INTO new_reindex_info SELECT relname, relfilenode FROM pg_class
+WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_2'::regclass) WHERE isleaf = true);
+INSERT INTO new_reindex_info SELECT relname, relfilenode FROM pg_class
+WHERE relname = 'regress_tblspace_test_tbl_idx';
+SELECT * FROM new_reindex_info JOIN old_reindex_info ON new_reindex_info.relname = old_reindex_info.relname
+WHERE new_reindex_info.relfilenode = old_reindex_info.relfilenode;
+ relname | relfilenode | relname | relfilenode 
+---------+-------------+---------+-------------
+(0 rows)
+
 -- create a schema we can use
 CREATE SCHEMA testschema;
 -- try a table
@@ -199,6 +333,47 @@ Partitions: testschema.part1_a_idx,
             testschema.part2_a_idx
 Tablespace: "regress_tblspace"
 
+-- REINDEX partitioned indexes to new tablespace
+REINDEX (TABLESPACE pg_default) TABLE testschema.part;
+\d testschema.part
+        Partitioned table "testschema.part"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+Partition key: LIST (a)
+Indexes:
+    "part_a_idx" btree (a)
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d testschema.part1
+             Table "testschema.part1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+Partition of: testschema.part FOR VALUES IN (1)
+Indexes:
+    "part1_a_idx" btree (a)
+
+REINDEX (CONCURRENTLY, TABLESPACE regress_tblspace) INDEX testschema.part_a_idx;
+\d testschema.part
+        Partitioned table "testschema.part"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+Partition key: LIST (a)
+Indexes:
+    "part_a_idx" btree (a), tablespace "regress_tblspace"
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d testschema.part1
+             Table "testschema.part1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+Partition of: testschema.part FOR VALUES IN (1)
+Indexes:
+    "part1_a_idx" btree (a), tablespace "regress_tblspace"
+
 -- partitioned rels cannot specify the default tablespace.  These fail:
 CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
 ERROR:  cannot specify default tablespace for partitioned relations
@@ -731,6 +906,10 @@ ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2;
 SET SESSION ROLE regress_tablespace_user2;
 CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
 ERROR:  permission denied for tablespace regress_tblspace
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY regress_tblspace_test_tbl; -- fail
+ERROR:  permission denied for tablespace regress_tblspace
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_pt_idx; -- fail
+ERROR:  permission denied for tablespace regress_tblspace
 ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint;
 RESET ROLE;
 ALTER TABLESPACE regress_tblspace RENAME TO regress_tblspace_renamed;
@@ -741,6 +920,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 TABLE regress_tblspace_test_tbl;
+DROP TABLE regress_tblspace_test_pt;
 DROP SCHEMA testschema CASCADE;
 NOTICE:  drop cascades to 6 other objects
 DETAIL:  drop cascades to table testschema.foo

base-commit: e42b3c3bd6a9c6233ac4c8a2e9b040367ba2f97c
-- 
2.20.1