On Tue, Feb 02, 2021 at 10:32:19AM +0900, Michael Paquier wrote: > On Mon, Feb 01, 2021 at 06:28:57PM +0300, Alexey Kondratov wrote: > > Hm, IIUC, REINDEX CONCURRENTLY doesn't use either of them. It directly uses > > index_create() with a proper tablespaceOid instead of > > SetRelationTableSpace(). And its checks structure is more restrictive even > > without tablespace change, so it doesn't use CheckRelationTableSpaceMove(). > > Sure. I have not checked the patch in details, but even with that it > would be much safer to me if we apply the same sanity checks > everywhere. That's less potential holes to worry about.
Thanks Alexey for the new patch. I have been looking at the main
patch in details.
/*
- * 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 you remove the check on RELATION_IS_OTHER_TEMP() in
reindex_index(), you would allow the reindex of a temp relation owned
by a different session if its tablespace is not changed, so this
cannot be removed.
+ !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))));
Indeed, a system relation with a relfilenode should be allowed to move
under allow_system_table_mods. I think that we had better move this
check into CheckRelationTableSpaceMove() instead of reindex_index() to
centralize the logic. ALTER TABLE does this business in
RangeVarCallbackForAlterRelation(), but our code path opening the
relation is different for the non-concurrent case.
+ if (OidIsValid(params->tablespaceOid) &&
+ IsSystemClass(relid, classtuple))
+ {
+ if (!allowSystemTableMods)
+ {
+ /* Skip all system relations, if not allowSystemTableMods *
I don't see the need for having two warnings here to say the same
thing if a relation is mapped or not mapped, so let's keep it simple.
+REINDEX (TABLESPACE regress_tblspace) SYSTEM CONCURRENTLY postgres; -- fail
+ERROR: cannot reindex system catalogs concurrently
[...]
+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
Those tests are costly by design, so let's drop them. They have been
useful to check the patch, but if tests are changed with objects
remaining around this would cost a lot of resources.
+ /* 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 totablespace \"%s\"",
+ get_tablespace_name(params->tablespaceOid))));
There is no test coverage for this case with REINDEX CONCURRENTLY, and
that's easy enough to stress. So I have added one.
I have found that the test suite was rather messy in its
organization. Table creations were done first with a set of tests not
really ordered, so that was really hard to follow. This has also led
to a set of tests that were duplicated, while other tests have been
missed, mainly some cross checks for the concurrent and non-concurrent
behaviors. I have reordered the whole so as tests on catalogs, normal
tables and partitions are done separately with relations created and
dropped for each set. Partitions use a global check for tablespaces
and relfilenodes after one concurrent reindex (didn't see the point in
doubling with the non-concurrent case as the same code path to select
the relations from the partition tree is taken). An ACL test has been
added at the end.
The case of partitioned indexes was kind of interesting and I thought
about that a couple of days, and I took the decision to ignore
relations that have no storage as you did, documenting that ALTER
TABLE can be used to update the references of the partitioned
relations. The command is still useful with this behavior, and the
tests I have added track that.
Finally, I have reworked the docs, separating the limitations related
to system catalogs and partitioned relations, to be more consistent
with the notes at the end of the page.
--
Michael
From c021aeca905a738d38acb257cbc4724804273499 Mon Sep 17 00:00:00 2001
From: Michael Paquier <mich...@paquier.xyz>
Date: Wed, 3 Feb 2021 15:26:21 +0900
Subject: [PATCH v11] 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.
---
src/include/catalog/index.h | 3 +
src/backend/catalog/index.c | 35 ++++-
src/backend/commands/indexcmds.c | 90 ++++++++++++
src/backend/commands/tablecmds.c | 7 +-
src/bin/psql/tab-complete.c | 4 +-
src/test/regress/input/tablespace.source | 106 +++++++++++++++
src/test/regress/output/tablespace.source | 159 ++++++++++++++++++++++
doc/src/sgml/ref/reindex.sgml | 40 ++++++
8 files changed, 438 insertions(+), 6 deletions(-)
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 266f8950dc..e22d506436 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -33,6 +33,8 @@ typedef enum
typedef struct ReindexParams
{
bits32 options; /* bitmask of REINDEXOPT_* */
+ Oid tablespaceOid; /* New tablespace to move indexes to.
+ * InvalidOid to do nothing. */
} ReindexParams;
/* flag bits for ReindexParams->flags */
@@ -92,6 +94,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/backend/catalog/index.c b/src/backend/catalog/index.c
index 8350c65beb..3c695553dc 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 = false;
pg_rusage_init(&ru0);
@@ -3674,12 +3679,32 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot reindex invalid index on TOAST table")));
+ /* Check if the tablespace of this index needs to be changed */
+ if (OidIsValid(params->tablespaceOid) &&
+ CheckRelationTableSpaceMove(iRel, params->tablespaceOid))
+ set_tablespace = true;
+
/*
* Also check for active uses of the index in the current transaction; we
* don't want to reindex underneath an open indexscan.
*/
CheckTableNotInUse(iRel, "REINDEX INDEX");
+ /* Set new tablespace, if requested */
+ if (set_tablespace)
+ {
+ SetRelationTableSpace(iRel, params->tablespaceOid, InvalidOid);
+
+ /*
+ * Schedule unlinking of the old index storage at transaction commit.
+ */
+ 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.
@@ -3963,11 +3988,15 @@ reindex_relation(Oid relid, int flags, ReindexParams *params)
{
/*
* Note that this should fail if the toast relation is missing, so
- * reset REINDEXOPT_MISSING_OK.
+ * reset REINDEXOPT_MISSING_OK. Even if a new tablespace is set
+ * for the parent relation, the indexes on its toast table are not
+ * moved. This rule is enforced by setting tablespaceOid to
+ * InvalidOid.
*/
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..674f5a3bc7 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 *tablespacename = 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)
+ tablespacename = defGetString(opt);
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -2500,6 +2503,30 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
(verbose ? REINDEXOPT_VERBOSE : 0) |
(concurrently ? REINDEXOPT_CONCURRENTLY : 0);
+ /*
+ * Assign the tablespace OID to move indexes to, with InvalidOid
+ * to do nothing.
+ */
+ if (tablespacename != NULL)
+ {
+ params.tablespaceOid = get_tablespace_oid(tablespacename, false);
+
+ /* Check permissions except when moving to 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));
+ }
+ }
+ else
+ params.tablespaceOid = InvalidOid;
+
switch (stmt->kind)
{
case REINDEX_OBJECT_INDEX:
@@ -2730,6 +2757,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
List *relids = NIL;
int num_keys;
bool concurrent_warning = false;
+ bool tablespace_warning = false;
AssertArg(objectName);
Assert(objectKind == REINDEX_OBJECT_SCHEMA ||
@@ -2856,6 +2884,40 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
continue;
}
+ /*
+ * If a new tablespace is set, check if this relation has to be
+ * skipped.
+ */
+ if (OidIsValid(params->tablespaceOid))
+ {
+ bool skip_rel = false;
+
+ /*
+ * Mapped relations cannot be moved to different tablespaces (in
+ * particular this eliminates all shared catalogs.).
+ */
+ if (RELKIND_HAS_STORAGE(classtuple->relkind) &&
+ !OidIsValid(classtuple->relfilenode))
+ skip_rel = true;
+
+ /*
+ * A system relation is skipped unless allow_system_table_mods
+ * is enabled.
+ */
+ if (!allowSystemTableMods && IsSystemClass(relid, classtuple))
+ skip_rel = true;
+
+ if (skip_rel)
+ {
+ if (!tablespace_warning)
+ ereport(WARNING,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("cannot move system relations, skipping all")));
+ tablespace_warning = true;
+ continue;
+ }
+ }
+
/* Save the list of relation OIDs in private context */
old = MemoryContextSwitchTo(private_context);
@@ -3032,6 +3094,24 @@ ReindexMultipleInternal(List *relids, ReindexParams *params)
continue;
}
+ /*
+ * Check permissions except when moving to database's default
+ * if a new tablespace is chosen. Note that this check also
+ * happens in ExecReindex(), but we do an extra check here as
+ * this runs across multiple 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 +3470,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 +3548,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/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 420991e315..13a39e5a6e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -3063,9 +3063,12 @@ CheckRelationTableSpaceMove(Relation rel, Oid newTableSpaceId)
/*
* We cannot support moving mapped relations into different tablespaces.
- * (In particular this eliminates all shared catalogs.)
+ * (In particular this eliminates all shared catalogs.). If this is
+ * a system relation this is forbidden unless allow_system_table_mods is
+ * enabled.
*/
- if (RelationIsMapped(rel))
+ if (RelationIsMapped(rel) ||
+ (!allowSystemTableMods && IsSystemRelation(rel)))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot move system relation \"%s\"",
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/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 1a181016d7..4fb1ae9ac4 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -17,6 +17,111 @@ 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
+-- REINDEX (TABLESPACE)
+-- catalogs and system tablespaces
+-- system catalog, fail
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_am;
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_am;
+-- shared catalog, fail
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_authid;
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_authid;
+-- authorized, nothing happens
+REINDEX (TABLESPACE pg_global) TABLE pg_authid;
+SELECT reltablespace <> 0 AS nonzero_tbspace
+ FROM pg_class where relname = 'pg_authid';
+
+-- table with toast relation
+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);
+-- move to global tablespace move fails
+REINDEX (TABLESPACE pg_global) INDEX regress_tblspace_test_tbl_idx;
+REINDEX (TABLESPACE pg_global) INDEX CONCURRENTLY regress_tblspace_test_tbl_idx;
+
+-- check transactional behavior of REINDEX (TABLESPACE)
+BEGIN;
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_tbl_idx;
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl;
+ROLLBACK;
+-- no relation moved to the new tablespace
+SELECT c.relname FROM pg_class c, pg_tablespace s
+ WHERE c.reltablespace = s.oid AND s.spcname = 'regress_tblspace';
+
+-- check that all indexes are moved to a new tablespace with different
+-- relfilenode.
+-- Save first the existing relfilenode for the toast and main relations.
+SELECT relfilenode as main_filenode FROM pg_class
+ WHERE relname = 'regress_tblspace_test_tbl_idx' \gset
+SELECT relfilenode as toast_filenode FROM pg_class
+ WHERE oid =
+ (SELECT i.indexrelid
+ FROM pg_class c,
+ pg_index i
+ WHERE i.indrelid = c.reltoastrelid AND
+ c.relname = 'regress_tblspace_test_tbl') \gset
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl;
+SELECT c.relname FROM pg_class c, pg_tablespace s
+ WHERE c.reltablespace = s.oid AND s.spcname = 'regress_tblspace'
+ ORDER BY c.relname;
+SELECT relfilenode = :main_filenode AS main_same FROM pg_class
+ WHERE relname = 'regress_tblspace_test_tbl_idx';
+SELECT relfilenode = :toast_filenode as toast_same FROM pg_class
+ WHERE oid =
+ (SELECT i.indexrelid
+ FROM pg_class c,
+ pg_index i
+ WHERE i.indrelid = c.reltoastrelid AND
+ c.relname = 'regress_tblspace_test_tbl');
+DROP TABLE regress_tblspace_test_tbl;
+
+-- REINDEX (TABLESPACE) with partitions
+-- Create a partition tree and check the set of relations reindexed
+-- with their new tablespace.
+CREATE TABLE tbspace_reindex_part (c1 int, c2 int) PARTITION BY RANGE (c1);
+CREATE TABLE tbspace_reindex_part_0 PARTITION OF tbspace_reindex_part
+ FOR VALUES FROM (0) TO (10) PARTITION BY list (c2);
+CREATE TABLE tbspace_reindex_part_0_1 PARTITION OF tbspace_reindex_part_0
+ FOR VALUES IN (1);
+CREATE TABLE tbspace_reindex_part_0_2 PARTITION OF tbspace_reindex_part_0
+ FOR VALUES IN (2);
+-- This partitioned table will have no partitions.
+CREATE TABLE tbspace_reindex_part_10 PARTITION OF tbspace_reindex_part
+ FOR VALUES FROM (10) TO (20) PARTITION BY list (c2);
+-- Create some partitioned indexes
+CREATE INDEX tbspace_reindex_part_index ON ONLY tbspace_reindex_part (c1);
+CREATE INDEX tbspace_reindex_part_index_0 ON ONLY tbspace_reindex_part_0 (c1);
+ALTER INDEX tbspace_reindex_part_index ATTACH PARTITION tbspace_reindex_part_index_0;
+-- This partitioned index will have no partitions.
+CREATE INDEX tbspace_reindex_part_index_10 ON ONLY tbspace_reindex_part_10 (c1);
+ALTER INDEX tbspace_reindex_part_index ATTACH PARTITION tbspace_reindex_part_index_10;
+CREATE INDEX tbspace_reindex_part_index_0_1 ON ONLY tbspace_reindex_part_0_1 (c1);
+ALTER INDEX tbspace_reindex_part_index_0 ATTACH PARTITION tbspace_reindex_part_index_0_1;
+CREATE INDEX tbspace_reindex_part_index_0_2 ON ONLY tbspace_reindex_part_0_2 (c1);
+ALTER INDEX tbspace_reindex_part_index_0 ATTACH PARTITION tbspace_reindex_part_index_0_2;
+SELECT relid, parentrelid, level FROM pg_partition_tree('tbspace_reindex_part_index')
+ ORDER BY relid, level;
+SELECT relid, parentrelid, level FROM pg_partition_tree('tbspace_reindex_part_index')
+ ORDER BY relid, level;
+-- Track the original tablespace, relfilenode and OID of each index
+-- in the tree.
+CREATE TEMP TABLE reindex_temp_before AS
+ SELECT oid, relname, relfilenode, reltablespace
+ FROM pg_class
+ WHERE relname ~ 'tbspace_reindex_part_index';
+REINDEX (TABLESPACE regress_tblspace, CONCURRENTLY) TABLE tbspace_reindex_part;
+-- REINDEX CONCURRENTLY changes the OID of the old relation, hence a check
+-- based on the relation name below.
+SELECT b.relname,
+ CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'
+ ELSE 'relfilenode has changed' END AS filenode,
+ CASE WHEN a.reltablespace = b.reltablespace THEN 'reltablespace is unchanged'
+ ELSE 'reltablespace has changed' END AS tbspace
+ FROM reindex_temp_before b JOIN pg_class a ON b.relname = a.relname
+ ORDER BY 1;
+DROP TABLE tbspace_reindex_part;
+
-- create a schema we can use
CREATE SCHEMA testschema;
@@ -269,6 +374,7 @@ 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
ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint;
+REINDEX (TABLESPACE regress_tblspace) TABLE tablespace_table; -- fail
RESET ROLE;
ALTER TABLESPACE regress_tblspace RENAME TO regress_tblspace_renamed;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 94c5f023c6..137110deba 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -20,6 +20,163 @@ 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
+-- REINDEX (TABLESPACE)
+-- catalogs and system tablespaces
+-- system catalog, fail
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_am;
+ERROR: cannot move system relation "pg_am_name_index"
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_am;
+ERROR: cannot reindex system catalogs concurrently
+-- shared catalog, fail
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_authid;
+ERROR: cannot move system relation "pg_authid_rolname_index"
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_authid;
+ERROR: cannot reindex system catalogs concurrently
+-- authorized, nothing happens
+REINDEX (TABLESPACE pg_global) TABLE pg_authid;
+SELECT reltablespace <> 0 AS nonzero_tbspace
+ FROM pg_class where relname = 'pg_authid';
+ nonzero_tbspace
+-----------------
+ t
+(1 row)
+
+-- table with toast relation
+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);
+-- move to global tablespace move fails
+REINDEX (TABLESPACE pg_global) INDEX regress_tblspace_test_tbl_idx;
+ERROR: only shared relations can be placed in pg_global tablespace
+REINDEX (TABLESPACE pg_global) INDEX CONCURRENTLY regress_tblspace_test_tbl_idx;
+ERROR: cannot move non-shared relation to tablespace "pg_global"
+-- check transactional behavior of REINDEX (TABLESPACE)
+BEGIN;
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_tbl_idx;
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl;
+ROLLBACK;
+-- no relation moved to the new tablespace
+SELECT c.relname FROM pg_class c, pg_tablespace s
+ WHERE c.reltablespace = s.oid AND s.spcname = 'regress_tblspace';
+ relname
+---------
+(0 rows)
+
+-- check that all indexes are moved to a new tablespace with different
+-- relfilenode.
+-- Save first the existing relfilenode for the toast and main relations.
+SELECT relfilenode as main_filenode FROM pg_class
+ WHERE relname = 'regress_tblspace_test_tbl_idx' \gset
+SELECT relfilenode as toast_filenode FROM pg_class
+ WHERE oid =
+ (SELECT i.indexrelid
+ FROM pg_class c,
+ pg_index i
+ WHERE i.indrelid = c.reltoastrelid AND
+ c.relname = 'regress_tblspace_test_tbl') \gset
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl;
+SELECT c.relname FROM pg_class c, pg_tablespace s
+ WHERE c.reltablespace = s.oid AND s.spcname = 'regress_tblspace'
+ ORDER BY c.relname;
+ relname
+-------------------------------
+ regress_tblspace_test_tbl_idx
+(1 row)
+
+SELECT relfilenode = :main_filenode AS main_same FROM pg_class
+ WHERE relname = 'regress_tblspace_test_tbl_idx';
+ main_same
+-----------
+ f
+(1 row)
+
+SELECT relfilenode = :toast_filenode as toast_same FROM pg_class
+ WHERE oid =
+ (SELECT i.indexrelid
+ FROM pg_class c,
+ pg_index i
+ WHERE i.indrelid = c.reltoastrelid AND
+ c.relname = 'regress_tblspace_test_tbl');
+ toast_same
+------------
+ f
+(1 row)
+
+DROP TABLE regress_tblspace_test_tbl;
+-- REINDEX (TABLESPACE) with partitions
+-- Create a partition tree and check the set of relations reindexed
+-- with their new tablespace.
+CREATE TABLE tbspace_reindex_part (c1 int, c2 int) PARTITION BY RANGE (c1);
+CREATE TABLE tbspace_reindex_part_0 PARTITION OF tbspace_reindex_part
+ FOR VALUES FROM (0) TO (10) PARTITION BY list (c2);
+CREATE TABLE tbspace_reindex_part_0_1 PARTITION OF tbspace_reindex_part_0
+ FOR VALUES IN (1);
+CREATE TABLE tbspace_reindex_part_0_2 PARTITION OF tbspace_reindex_part_0
+ FOR VALUES IN (2);
+-- This partitioned table will have no partitions.
+CREATE TABLE tbspace_reindex_part_10 PARTITION OF tbspace_reindex_part
+ FOR VALUES FROM (10) TO (20) PARTITION BY list (c2);
+-- Create some partitioned indexes
+CREATE INDEX tbspace_reindex_part_index ON ONLY tbspace_reindex_part (c1);
+CREATE INDEX tbspace_reindex_part_index_0 ON ONLY tbspace_reindex_part_0 (c1);
+ALTER INDEX tbspace_reindex_part_index ATTACH PARTITION tbspace_reindex_part_index_0;
+-- This partitioned index will have no partitions.
+CREATE INDEX tbspace_reindex_part_index_10 ON ONLY tbspace_reindex_part_10 (c1);
+ALTER INDEX tbspace_reindex_part_index ATTACH PARTITION tbspace_reindex_part_index_10;
+CREATE INDEX tbspace_reindex_part_index_0_1 ON ONLY tbspace_reindex_part_0_1 (c1);
+ALTER INDEX tbspace_reindex_part_index_0 ATTACH PARTITION tbspace_reindex_part_index_0_1;
+CREATE INDEX tbspace_reindex_part_index_0_2 ON ONLY tbspace_reindex_part_0_2 (c1);
+ALTER INDEX tbspace_reindex_part_index_0 ATTACH PARTITION tbspace_reindex_part_index_0_2;
+SELECT relid, parentrelid, level FROM pg_partition_tree('tbspace_reindex_part_index')
+ ORDER BY relid, level;
+ relid | parentrelid | level
+--------------------------------+------------------------------+-------
+ tbspace_reindex_part_index | | 0
+ tbspace_reindex_part_index_0 | tbspace_reindex_part_index | 1
+ tbspace_reindex_part_index_10 | tbspace_reindex_part_index | 1
+ tbspace_reindex_part_index_0_1 | tbspace_reindex_part_index_0 | 2
+ tbspace_reindex_part_index_0_2 | tbspace_reindex_part_index_0 | 2
+(5 rows)
+
+SELECT relid, parentrelid, level FROM pg_partition_tree('tbspace_reindex_part_index')
+ ORDER BY relid, level;
+ relid | parentrelid | level
+--------------------------------+------------------------------+-------
+ tbspace_reindex_part_index | | 0
+ tbspace_reindex_part_index_0 | tbspace_reindex_part_index | 1
+ tbspace_reindex_part_index_10 | tbspace_reindex_part_index | 1
+ tbspace_reindex_part_index_0_1 | tbspace_reindex_part_index_0 | 2
+ tbspace_reindex_part_index_0_2 | tbspace_reindex_part_index_0 | 2
+(5 rows)
+
+-- Track the original tablespace, relfilenode and OID of each index
+-- in the tree.
+CREATE TEMP TABLE reindex_temp_before AS
+ SELECT oid, relname, relfilenode, reltablespace
+ FROM pg_class
+ WHERE relname ~ 'tbspace_reindex_part_index';
+REINDEX (TABLESPACE regress_tblspace, CONCURRENTLY) TABLE tbspace_reindex_part;
+-- REINDEX CONCURRENTLY changes the OID of the old relation, hence a check
+-- based on the relation name below.
+SELECT b.relname,
+ CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'
+ ELSE 'relfilenode has changed' END AS filenode,
+ CASE WHEN a.reltablespace = b.reltablespace THEN 'reltablespace is unchanged'
+ ELSE 'reltablespace has changed' END AS tbspace
+ FROM reindex_temp_before b JOIN pg_class a ON b.relname = a.relname
+ ORDER BY 1;
+ relname | filenode | tbspace
+--------------------------------+--------------------------+----------------------------
+ tbspace_reindex_part_index | relfilenode is unchanged | reltablespace is unchanged
+ tbspace_reindex_part_index_0 | relfilenode is unchanged | reltablespace is unchanged
+ tbspace_reindex_part_index_0_1 | relfilenode has changed | reltablespace has changed
+ tbspace_reindex_part_index_0_2 | relfilenode has changed | reltablespace has changed
+ tbspace_reindex_part_index_10 | relfilenode is unchanged | reltablespace is unchanged
+(5 rows)
+
+DROP TABLE tbspace_reindex_part;
-- create a schema we can use
CREATE SCHEMA testschema;
-- try a table
@@ -732,6 +889,8 @@ SET SESSION ROLE regress_tablespace_user2;
CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail
ERROR: permission denied for tablespace regress_tblspace
ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint;
+REINDEX (TABLESPACE regress_tblspace) TABLE tablespace_table; -- fail
+ERROR: permission denied for tablespace regress_tblspace
RESET ROLE;
ALTER TABLESPACE regress_tblspace RENAME TO regress_tblspace_renamed;
ALTER TABLE ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default;
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 627b36300c..4ee3951ca0 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -26,6 +26,7 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
CONCURRENTLY [ <replaceable class="parameter">boolean</replaceable> ]
+ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
</synopsis>
</refsynopsisdiv>
@@ -187,6 +188,15 @@ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>VERBOSE</literal></term>
<listitem>
@@ -210,6 +220,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>
@@ -293,8 +311,30 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
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.
+ If a <command>REINDEX</command> command fails when run on a partitioned
+ relation, and <literal>TABLESPACE</literal> was specified, then it may not
+ have moved all indexes to the new tablespace. Re-running the command
+ will rebuild again all the partitions and move previously-unprocessed
+ indexes to the new tablespace.
+ </para>
+
+ <para>
+ When using the <literal>TABLESPACE</literal> clause with
+ <command>REINDEX</command> on a partitioned index or table, only the
+ tablespace references of the partitions are updated. As partitioned indexes
+ are not updated, it is recommended to separately use
+ <command>ALTER TABLE ONLY</command> on them to achieve that.
</para>
+ <para>
+ If <literal>SCHEMA</literal>, <literal>DATABASE</literal> or
+ <literal>SYSTEM</literal> is used with
+ <literal>TABLESPACE</literal>, system relations are skipped
+ and a single <literal>WARNING</literal> will be generated.
+ Indexes on TOAST tables are rebuilt, but not moved to the new
+ tablespace.
+ </para>
+
<refsect2 id="sql-reindex-concurrently" xreflabel="Rebuilding Indexes Concurrently">
<title>Rebuilding Indexes Concurrently</title>
--
2.30.0
signature.asc
Description: PGP signature
