Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commitc5b2860

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

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(RelationheapRelation,OidoldIndexId,constchar*newName)
1402+
index_concurrently_create_copy(RelationheapRelation,OidoldIndexId,
1403+
OidtablespaceOid,constchar*newName)
14001404
{
14011405
RelationindexRelation;
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
volatileboolskipped_constraint= false;
36043608
PGRUsageru0;
36053609
boolprogress= ((params->options&REINDEXOPT_REPORT_PROGRESS)!=0);
3610+
boolset_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
ReindexParamsnewparams=*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
boolconcurrently= false;
24762476
boolverbose= 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
elseif (strcmp(opt->defname,"concurrently")==0)
24862487
concurrently=defGetBoolean(opt);
2488+
elseif (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+
AclResultaclresult;
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
caseREINDEX_OBJECT_INDEX:
@@ -2730,6 +2757,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
27302757
List*relids=NIL;
27312758
intnum_keys;
27322759
boolconcurrent_warning= false;
2760+
booltablespace_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+
boolskip_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+
AclResultaclresult;
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
RelationheapRel;
34283530
RelationnewIndexRel;
34293531
LockRelId*lockrelid;
3532+
Oidtablespaceid;
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+
elseif (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
typedefstructReindexParams
3434
{
3535
bits32options;/* bitmask of REINDEXOPT_* */
36+
OidtablespaceOid;/* 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 Oidindex_create(Relation heapRelation,
9294

9395
externOidindex_concurrently_create_copy(RelationheapRelation,
9496
OidoldIndexId,
97+
OidtablespaceOid,
9598
constchar*newName);
9699

97100
externvoidindex_concurrently_build(OidheapRelationId,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp