On 2021-01-28 14:42, Alexey Kondratov wrote:
On 2021-01-28 00:36, Alvaro Herrera wrote:
I didn't look at the patch closely enough to understand why you're
trying to do something like CLUSTER, VACUUM FULL or REINDEX without
holding full AccessExclusiveLock on the relation. But do keep in mind
that once you hold a lock on a relation, trying to grab a weaker lock
afterwards is pretty pointless.
No, you are right, we are doing REINDEX with AccessExclusiveLock as it
was before. This part is a more specific one. It only applies to
partitioned indexes, which do not hold any data, so we do not reindex
them directly, only their leafs. However, if we are doing a TABLESPACE
change, we have to record it in their pg_class entry, so all future
leaf partitions were created in the proper tablespace.
That way, we open partitioned index relation only for a reference,
i.e. read-only, but modify pg_class entry under a proper lock
(RowExclusiveLock). That's why I thought that ShareLock will be
enough.
IIUC, 'ALTER TABLE ... SET TABLESPACE' uses AccessExclusiveLock even
for relations with no storage, since AlterTableGetLockLevel() chooses
it if AT_SetTableSpace is met. This is very similar to our case, so
probably we should do the same?
Actually it is not completely clear for me why
ShareUpdateExclusiveLock is sufficient for newly added
SetRelationTableSpace() as Michael wrote in the comment.
Changed patch to use AccessExclusiveLock in this part for now. This is
what 'ALTER TABLE/INDEX ... SET TABLESPACE' and 'REINDEX' usually do.
Anyway, all real leaf partitions are processed in the independent
transactions later.
Also changed some doc/comment parts Justin pointed me to.
+ 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.
moved *to* the new tablespace.
Fixed.
I don't know if that needs to be said at all. We talked about it a lot
to
arrive at the current behavior, but I think that's only due to the
difficulty
of correcting the initial mistake.
I do not think that it will be a big deal to move indexes on TOAST
tables as well. I just thought that since 'ALTER TABLE/INDEX ... SET
TABLESPACE' only moves them together with host table, we also should not
do that. Yet, I am ready to change this logic if requested.
Regards
--
Alexey Kondratov
Postgres Professional https://www.postgrespro.com
Russian Postgres Company
From 6e9db8d362e794edf421733bc7cade38c917bff4 Mon Sep 17 00:00:00 2001
From: Alexey Kondratov <kondratov.alek...@gmail.com>
Date: Wed, 27 Jan 2021 00:46:17 +0300
Subject: [PATCH v9] 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 | 47 +++++-
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, 478 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 627b36300c..2b39699d42 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" or (unless <varname>allow_system_table_mods</varname>)
+ system 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 to 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..df4220646f 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,24 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
*/
CheckTableNotInUse(iRel, "REINDEX INDEX");
+ /* Set new tablespace, if requested. */
+ 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 +3993,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..27b6e1692f 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, AccessExclusiveLock);
+
+ 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, AccessExclusiveLock);
+
+ 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: f743a2bbd4e511ad58b6ce01f81841e5e1611474
--
2.20.1
