Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit c5b2860

Browse filesBrowse 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
Copy full SHA for c5b2860

File tree

Expand file treeCollapse file tree

7 files changed

+505
-4
lines changed
Filter options
Expand file treeCollapse file tree

7 files changed

+505
-4
lines changed

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/reindex.sgml
+38Lines 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

Copy file name to clipboardExpand all lines: src/backend/catalog/index.c
+44-3Lines 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

Copy file name to clipboardExpand all lines: src/backend/commands/indexcmds.c
+111Lines 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

Copy file name to clipboardExpand all lines: src/bin/psql/tab-complete.c
+3-1Lines 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

Copy file name to clipboardExpand all lines: src/include/catalog/index.h
+3Lines 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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.