Skip to content

Commit c5b2860

Browse files
michaelpqmichaelpq
committed
Add TABLESPACE option to REINDEX
This patch adds the possibility to move indexes to a new tablespace while rebuilding them. Both the concurrent and the non-concurrent cases are supported, and the following set of restrictions apply: - When using TABLESPACE with a REINDEX command that targets a partitioned table or index, all the indexes of the leaf partitions are moved to the new tablespace. The tablespace references of the non-leaf, partitioned tables in pg_class.reltablespace are not changed. This requires an extra ALTER TABLE SET TABLESPACE. - Any index on a toast table rebuilt as part of a parent table is kept in its original tablespace. - The operation is forbidden on system catalogs, including trying to directly move a toast relation with REINDEX. This results in an error if doing REINDEX on a single object. REINDEX SCHEMA, DATABASE and SYSTEM skip system relations when TABLESPACE is used. Author: Alexey Kondratov, Michael Paquier, Justin Pryzby Reviewed-by: Álvaro Herrera, Michael Paquier Discussion: https://postgr.es/m/8a8f5f73-00d3-55f8-7583-1375ca8f6a91@postgrespro.ru
1 parent 9624321 commit c5b2860

File tree

7 files changed

+505
-4
lines changed

7 files changed

+505
-4
lines changed

‎doc/src/sgml/ref/reindex.sgml

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,7 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
2626
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
2727

2828
CONCURRENTLY [ <replaceable class="parameter">boolean</replaceable> ]
29+
TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
2930
VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
3031
</synopsis>
3132
</refsynopsisdiv>
@@ -187,6 +188,15 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
187188
</listitem>
188189
</varlistentry>
189190

191+
<varlistentry>
192+
<term><literal>TABLESPACE</literal></term>
193+
<listitem>
194+
<para>
195+
Specifies that indexes will be rebuilt on a new tablespace.
196+
</para>
197+
</listitem>
198+
</varlistentry>
199+
190200
<varlistentry>
191201
<term><literal>VERBOSE</literal></term>
192202
<listitem>
@@ -210,6 +220,14 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
210220
</listitem>
211221
</varlistentry>
212222

223+
<varlistentry>
224+
<term><replaceable class="parameter">new_tablespace</replaceable></term>
225+
<listitem>
226+
<para>
227+
The tablespace where indexes will be rebuilt.
228+
</para>
229+
</listitem>
230+
</varlistentry>
213231
</variablelist>
214232
</refsect1>
215233

@@ -294,7 +312,27 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
294312
reindexed in a separate transaction. Those commands cannot be used inside
295313
a transaction block when working on a partitioned table or index.
296314
</para>
315+
316+
<para>
317+
When using the <literal>TABLESPACE</literal> clause with
318+
<command>REINDEX</command> on a partitioned index or table, only the
319+
tablespace references of the leaf partitions are updated. As partitioned
320+
indexes are not updated, it is recommended to separately use
321+
<command>ALTER TABLE ONLY</command> on them so as any new partitions
322+
attached inherit the new tablespace. On failure, it may not have moved
323+
all the indexes to the new tablespace. Re-running the command will rebuild
324+
all the leaf partitions and move previously-unprocessed indexes to the new
325+
tablespace.
326+
</para>
297327

328+
<para>
329+
If <literal>SCHEMA</literal>, <literal>DATABASE</literal> or
330+
<literal>SYSTEM</literal> is used with <literal>TABLESPACE</literal>,
331+
system relations are skipped and a single <literal>WARNING</literal>
332+
will be generated. Indexes on TOAST tables are rebuilt, but not moved
333+
to the new tablespace.
334+
</para>
335+
298336
<refsect2 id="sql-reindex-concurrently" xreflabel="Rebuilding Indexes Concurrently">
299337
<title>Rebuilding Indexes Concurrently</title>
300338

‎src/backend/catalog/index.c

Lines changed: 44 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,7 @@
5757
#include "commands/event_trigger.h"
5858
#include "commands/progress.h"
5959
#include "commands/tablecmds.h"
60+
#include "commands/tablespace.h"
6061
#include "commands/trigger.h"
6162
#include "executor/executor.h"
6263
#include "miscadmin.h"
@@ -1394,9 +1395,12 @@ index_update_collation_versions(Oid relid, Oid coll)
13941395
* Create concurrently an index based on the definition of the one provided by
13951396
* caller. The index is inserted into catalogs and needs to be built later
13961397
* on. This is called during concurrent reindex processing.
1398+
*
1399+
* "tablespaceOid" is the tablespace to use for this index.
13971400
*/
13981401
Oid
1399-
index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, const char *newName)
1402+
index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
1403+
Oid tablespaceOid, const char *newName)
14001404
{
14011405
Relation indexRelation;
14021406
IndexInfo *oldInfo,
@@ -1526,7 +1530,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, const char
15261530
newInfo,
15271531
indexColNames,
15281532
indexRelation->rd_rel->relam,
1529-
indexRelation->rd_rel->reltablespace,
1533+
tablespaceOid,
15301534
indexRelation->rd_indcollation,
15311535
indclass->values,
15321536
indcoloptions->values,
@@ -3603,6 +3607,7 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
36033607
volatile bool skipped_constraint = false;
36043608
PGRUsage ru0;
36053609
bool progress = ((params->options & REINDEXOPT_REPORT_PROGRESS) != 0);
3610+
bool set_tablespace = false;
36063611

36073612
pg_rusage_init(&ru0);
36083613

@@ -3674,12 +3679,45 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
36743679
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
36753680
errmsg("cannot reindex invalid index on TOAST table")));
36763681

3682+
/*
3683+
* System relations cannot be moved even if allow_system_table_mods is
3684+
* enabled to keep things consistent with the concurrent case where all
3685+
* the indexes of a relation are processed in series, including indexes of
3686+
* toast relations.
3687+
*
3688+
* Note that this check is not part of CheckRelationTableSpaceMove() as it
3689+
* gets used for ALTER TABLE SET TABLESPACE that could cascade across
3690+
* toast relations.
3691+
*/
3692+
if (OidIsValid(params->tablespaceOid) &&
3693+
IsSystemRelation(iRel))
3694+
ereport(ERROR,
3695+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
3696+
errmsg("cannot move system relation \"%s\"",
3697+
RelationGetRelationName(iRel))));
3698+
3699+
/* Check if the tablespace of this index needs to be changed */
3700+
if (OidIsValid(params->tablespaceOid) &&
3701+
CheckRelationTableSpaceMove(iRel, params->tablespaceOid))
3702+
set_tablespace = true;
3703+
36773704
/*
36783705
* Also check for active uses of the index in the current transaction; we
36793706
* don't want to reindex underneath an open indexscan.
36803707
*/
36813708
CheckTableNotInUse(iRel, "REINDEX INDEX");
36823709

3710+
/* Set new tablespace, if requested */
3711+
if (set_tablespace)
3712+
{
3713+
/* Update its pg_class row */
3714+
SetRelationTableSpace(iRel, params->tablespaceOid, InvalidOid);
3715+
RelationAssumeNewRelfilenode(iRel);
3716+
3717+
/* Make sure the reltablespace change is visible */
3718+
CommandCounterIncrement();
3719+
}
3720+
36833721
/*
36843722
* All predicate locks on the index are about to be made invalid. Promote
36853723
* them to relation locks on the heap.
@@ -3963,11 +4001,14 @@ reindex_relation(Oid relid, int flags, ReindexParams *params)
39634001
{
39644002
/*
39654003
* Note that this should fail if the toast relation is missing, so
3966-
* reset REINDEXOPT_MISSING_OK.
4004+
* reset REINDEXOPT_MISSING_OK. Even if a new tablespace is set for
4005+
* the parent relation, the indexes on its toast table are not moved.
4006+
* This rule is enforced by setting tablespaceOid to InvalidOid.
39674007
*/
39684008
ReindexParams newparams = *params;
39694009

39704010
newparams.options &= ~(REINDEXOPT_MISSING_OK);
4011+
newparams.tablespaceOid = InvalidOid;
39714012
result |= reindex_relation(toast_relid, flags, &newparams);
39724013
}
39734014

‎src/backend/commands/indexcmds.c

Lines changed: 111 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2474,6 +2474,7 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
24742474
ListCell *lc;
24752475
bool concurrently = false;
24762476
bool verbose = false;
2477+
char *tablespacename = NULL;
24772478

24782479
/* Parse option list */
24792480
foreach(lc, stmt->params)
@@ -2484,6 +2485,8 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
24842485
verbose = defGetBoolean(opt);
24852486
else if (strcmp(opt->defname, "concurrently") == 0)
24862487
concurrently = defGetBoolean(opt);
2488+
else if (strcmp(opt->defname, "tablespace") == 0)
2489+
tablespacename = defGetString(opt);
24872490
else
24882491
ereport(ERROR,
24892492
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -2500,6 +2503,30 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
25002503
(verbose ? REINDEXOPT_VERBOSE : 0) |
25012504
(concurrently ? REINDEXOPT_CONCURRENTLY : 0);
25022505

2506+
/*
2507+
* Assign the tablespace OID to move indexes to, with InvalidOid to do
2508+
* nothing.
2509+
*/
2510+
if (tablespacename != NULL)
2511+
{
2512+
params.tablespaceOid = get_tablespace_oid(tablespacename, false);
2513+
2514+
/* Check permissions except when moving to database's default */
2515+
if (OidIsValid(params.tablespaceOid) &&
2516+
params.tablespaceOid != MyDatabaseTableSpace)
2517+
{
2518+
AclResult aclresult;
2519+
2520+
aclresult = pg_tablespace_aclcheck(params.tablespaceOid,
2521+
GetUserId(), ACL_CREATE);
2522+
if (aclresult != ACLCHECK_OK)
2523+
aclcheck_error(aclresult, OBJECT_TABLESPACE,
2524+
get_tablespace_name(params.tablespaceOid));
2525+
}
2526+
}
2527+
else
2528+
params.tablespaceOid = InvalidOid;
2529+
25032530
switch (stmt->kind)
25042531
{
25052532
case REINDEX_OBJECT_INDEX:
@@ -2730,6 +2757,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
27302757
List *relids = NIL;
27312758
int num_keys;
27322759
bool concurrent_warning = false;
2760+
bool tablespace_warning = false;
27332761

27342762
AssertArg(objectName);
27352763
Assert(objectKind == REINDEX_OBJECT_SCHEMA ||
@@ -2856,6 +2884,40 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
28562884
continue;
28572885
}
28582886

2887+
/*
2888+
* If a new tablespace is set, check if this relation has to be
2889+
* skipped.
2890+
*/
2891+
if (OidIsValid(params->tablespaceOid))
2892+
{
2893+
bool skip_rel = false;
2894+
2895+
/*
2896+
* Mapped relations cannot be moved to different tablespaces (in
2897+
* particular this eliminates all shared catalogs.).
2898+
*/
2899+
if (RELKIND_HAS_STORAGE(classtuple->relkind) &&
2900+
!OidIsValid(classtuple->relfilenode))
2901+
skip_rel = true;
2902+
2903+
/*
2904+
* A system relation is always skipped, even with
2905+
* allow_system_table_mods enabled.
2906+
*/
2907+
if (IsSystemClass(relid, classtuple))
2908+
skip_rel = true;
2909+
2910+
if (skip_rel)
2911+
{
2912+
if (!tablespace_warning)
2913+
ereport(WARNING,
2914+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
2915+
errmsg("cannot move system relations, skipping all")));
2916+
tablespace_warning = true;
2917+
continue;
2918+
}
2919+
}
2920+
28592921
/* Save the list of relation OIDs in private context */
28602922
old = MemoryContextSwitchTo(private_context);
28612923

@@ -3032,6 +3094,24 @@ ReindexMultipleInternal(List *relids, ReindexParams *params)
30323094
continue;
30333095
}
30343096

3097+
/*
3098+
* Check permissions except when moving to database's default if a new
3099+
* tablespace is chosen. Note that this check also happens in
3100+
* ExecReindex(), but we do an extra check here as this runs across
3101+
* multiple transactions.
3102+
*/
3103+
if (OidIsValid(params->tablespaceOid) &&
3104+
params->tablespaceOid != MyDatabaseTableSpace)
3105+
{
3106+
AclResult aclresult;
3107+
3108+
aclresult = pg_tablespace_aclcheck(params->tablespaceOid,
3109+
GetUserId(), ACL_CREATE);
3110+
if (aclresult != ACLCHECK_OK)
3111+
aclcheck_error(aclresult, OBJECT_TABLESPACE,
3112+
get_tablespace_name(params->tablespaceOid));
3113+
}
3114+
30353115
relkind = get_rel_relkind(relid);
30363116
relpersistence = get_rel_persistence(relid);
30373117

@@ -3210,6 +3290,13 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
32103290
heapRelation = table_open(relationOid,
32113291
ShareUpdateExclusiveLock);
32123292

3293+
if (OidIsValid(params->tablespaceOid) &&
3294+
IsSystemRelation(heapRelation))
3295+
ereport(ERROR,
3296+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
3297+
errmsg("cannot move system relation \"%s\"",
3298+
RelationGetRelationName(heapRelation))));
3299+
32133300
/* Add all the valid indexes of relation to list */
32143301
foreach(lc, RelationGetIndexList(heapRelation))
32153302
{
@@ -3346,6 +3433,14 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
33463433
else
33473434
heapRelation = table_open(heapId,
33483435
ShareUpdateExclusiveLock);
3436+
3437+
if (OidIsValid(params->tablespaceOid) &&
3438+
IsSystemRelation(heapRelation))
3439+
ereport(ERROR,
3440+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
3441+
errmsg("cannot move system relation \"%s\"",
3442+
get_rel_name(relationOid))));
3443+
33493444
table_close(heapRelation, NoLock);
33503445

33513446
/* Save the list of relation OIDs in private context */
@@ -3390,6 +3485,13 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
33903485
return false;
33913486
}
33923487

3488+
/* It's not a shared catalog, so refuse to move it to shared tablespace */
3489+
if (params->tablespaceOid == GLOBALTABLESPACE_OID)
3490+
ereport(ERROR,
3491+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
3492+
errmsg("cannot move non-shared relation to tablespace \"%s\"",
3493+
get_tablespace_name(params->tablespaceOid))));
3494+
33933495
Assert(heapRelationIds != NIL);
33943496

33953497
/*-----
@@ -3427,6 +3529,7 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
34273529
Relation heapRel;
34283530
Relation newIndexRel;
34293531
LockRelId *lockrelid;
3532+
Oid tablespaceid;
34303533

34313534
indexRel = index_open(idx->indexId, ShareUpdateExclusiveLock);
34323535
heapRel = table_open(indexRel->rd_index->indrelid,
@@ -3458,9 +3561,17 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
34583561
get_rel_namespace(indexRel->rd_index->indrelid),
34593562
false);
34603563

3564+
/* Choose the new tablespace, indexes of toast tables are not moved */
3565+
if (OidIsValid(params->tablespaceOid) &&
3566+
heapRel->rd_rel->relkind != RELKIND_TOASTVALUE)
3567+
tablespaceid = params->tablespaceOid;
3568+
else
3569+
tablespaceid = indexRel->rd_rel->reltablespace;
3570+
34613571
/* Create new index definition based on given index */
34623572
newIndexId = index_concurrently_create_copy(heapRel,
34633573
idx->indexId,
3574+
tablespaceid,
34643575
concurrentName);
34653576

34663577
/*

‎src/bin/psql/tab-complete.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3641,7 +3641,9 @@ psql_completion(const char *text, int start, int end)
36413641
* one word, so the above test is correct.
36423642
*/
36433643
if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
3644-
COMPLETE_WITH("CONCURRENTLY", "VERBOSE");
3644+
COMPLETE_WITH("CONCURRENTLY", "TABLESPACE", "VERBOSE");
3645+
else if (TailMatches("TABLESPACE"))
3646+
COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
36453647
}
36463648

36473649
/* SECURITY LABEL */

‎src/include/catalog/index.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,8 @@ typedef enum
3333
typedef struct ReindexParams
3434
{
3535
bits32 options; /* bitmask of REINDEXOPT_* */
36+
Oid tablespaceOid; /* New tablespace to move indexes to.
37+
* InvalidOid to do nothing. */
3638
} ReindexParams;
3739

3840
/* flag bits for ReindexParams->flags */
@@ -92,6 +94,7 @@ extern Oid index_create(Relation heapRelation,
9294

9395
extern Oid index_concurrently_create_copy(Relation heapRelation,
9496
Oid oldIndexId,
97+
Oid tablespaceOid,
9598
const char *newName);
9699

97100
extern void index_concurrently_build(Oid heapRelationId,

0 commit comments

Comments
 (0)