Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly
Alexey Kondratov <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.devel.general |
|---|---|
| Message-ID | <[email protected]> |
On 2019-12-02 11:21, Michael Paquier wrote: > On Wed, Nov 27, 2019 at 08:47:06PM +0300, Alexey Kondratov wrote: > >> Thus, I cannot get your point correctly here. Can you, please, >> elaborate a >> little bit more your concerns? > > The case of REINDEX CONCURRENTLY is pretty simple, because a new > relation which is a copy of the old relation is created before doing > the reindex, so you simply need to set the tablespace OID correctly > in index_concurrently_create_copy(). And actually, I think that the > computation is incorrect because we need to check after > MyDatabaseTableSpace as well, no? > > The case of REINDEX is more tricky, because you are working on a > relation that already exists, hence I think that what you need to do a > different thing before the actual REINDEX: > 1) Update the existing relation's pg_class tuple to point to the new > tablespace. > 2) Do a CommandCounterIncrement. > So I think that the order of the operations you are doing is incorrect, > and that you have a risk of breaking the existing tablespace assignment > logic done when first flushing a new relfilenode. > > This actually brings an extra thing: when doing a plain REINDEX you > need to make sure that the past relfilenode of the relation gets away > properly. The attached POC patch does that before doing the CCI which > is a bit ugly, but that's enough to show my point, and there is no > need to touch RelationSetNewRelfilenode() this way. > OK, I hope that now I understand your concerns better. Another thing I just realised is that RelationSetNewRelfilenode is also used for mapped relations, which are not movable at all, so adding a tablespace options there seems to be not semantically correct as well. However, I still have not find a way to reproduce how to actually brake anything with my previous version of the patch. As for doing RelationDropStorage before CCI, I do not think that there is something wrong with it, this is exactly what RelationSetNewRelfilenode does. I have only moved RelationDropStorage before CatalogTupleUpdate compared to your proposal to match order inside RelationSetNewRelfilenode. > > Your patch has forgotten to update copyfuncs.c and equalfuncs.c with > the new tablespace string field. > > It would be nice to add tab completion for this new clause in psql. > This is not ready for committer yet in my opinion, and more work is > done, so I am marking it as returned with feedback for now. > Finally, I have also merged and unified all your and Masahiko's proposals with my recent changes: ereport corrections, tab-completion, docs update, copy/equalfuncs update, etc. New version is attached. Have it come any closer to a committable state now? Regards -- Alexey Kondratov Postgres Professional https://www.postgrespro.com Russian Postgres Company
v7-0001-Allow-REINDEX-to-change-tablespace.patch
(text/x-diff, 32.9 KB)
From 81a9df46db91b6ba47e11f53e6e2379f835b576f Mon Sep 17 00:00:00 2001 From: Alexey Kondratov <[email protected]> Date: Mon, 30 Dec 2019 20:00:37 +0300 Subject: [PATCH v7] 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 | 24 +++++- src/backend/catalog/index.c | 75 ++++++++++++++++-- src/backend/commands/cluster.c | 2 +- src/backend/commands/indexcmds.c | 96 ++++++++++++++++++++--- src/backend/commands/tablecmds.c | 2 +- src/backend/nodes/copyfuncs.c | 1 + src/backend/nodes/equalfuncs.c | 1 + src/backend/parser/gram.y | 14 ++-- src/backend/tcop/utility.c | 6 +- src/bin/psql/tab-complete.c | 6 ++ src/include/catalog/index.h | 7 +- src/include/commands/defrem.h | 6 +- src/include/nodes/parsenodes.h | 1 + src/test/regress/input/tablespace.source | 49 ++++++++++++ src/test/regress/output/tablespace.source | 66 ++++++++++++++++ 15 files changed, 323 insertions(+), 33 deletions(-) diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index 5aa59d3b751..cf6bd88c128 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable> +REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable> [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase> @@ -169,6 +169,28 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN </listitem> </varlistentry> + <varlistentry> + <term><literal>TABLESPACE</literal></term> + <listitem> + <para> + This specifies a tablespace, where all rebuilt indexes will be created. + Cannot be used with "mapped" relations. If <literal>SCHEMA</literal>, + <literal>DATABASE</literal> or <literal>SYSTEM</literal> is specified, then + all unsuitable relations will be skipped and a single <literal>WARNING</literal> + will be generated. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">new_tablespace</replaceable></term> + <listitem> + <para> + The name of the specific tablespace to store rebuilt indexes. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>VERBOSE</literal></term> <listitem> diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 3e59e647e5c..49af0d4ee41 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1235,9 +1235,13 @@ index_create(Relation heapRelation, * 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 new tablespace to use for this index. If + * InvalidOid, use the tablespace in-use instead. */ 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, @@ -1367,7 +1371,8 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, const char newInfo, indexColNames, indexRelation->rd_rel->relam, - indexRelation->rd_rel->reltablespace, + OidIsValid(tablespaceOid) ? + tablespaceOid : indexRelation->rd_rel->reltablespace, indexRelation->rd_indcollation, indclass->values, indcoloptions->values, @@ -3399,10 +3404,12 @@ IndexGetRelation(Oid indexId, bool missing_ok) /* * reindex_index - This routine is used to recreate a single index + * + * See comments of reindex_relation() for details about "tablespaceOid". */ void -reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, - int options) +reindex_index(Oid indexId, Oid tablespaceOid, bool skip_constraint_checks, + char persistence, int options) { Relation iRel, heapRelation; @@ -3449,6 +3456,16 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, elog(ERROR, "unsupported relation kind for index \"%s\"", RelationGetRelationName(iRel)); + /* + * We cannot support moving mapped relations into different tablespaces. + * (In particular this eliminates all shared catalogs.) + */ + if (OidIsValid(tablespaceOid) && RelationIsMapped(iRel)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot move system relation \"%s\"", + RelationGetRelationName(iRel)))); + /* * Don't allow reindex on temp tables of other backends ... their local * buffer manager is not going to cope. @@ -3464,6 +3481,45 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, */ CheckTableNotInUse(iRel, "REINDEX INDEX"); + /* + * Set the new tablespace for the relation. Do that only in the + * case where the reindex caller wishes to enforce a new tablespace. + */ + if (OidIsValid(tablespaceOid) && + tablespaceOid != iRel->rd_rel->reltablespace) + { + Relation pg_class; + Form_pg_class rd_rel; + HeapTuple tuple; + + /* First get a modifiable copy of the relation's pg_class row */ + pg_class = table_open(RelationRelationId, RowExclusiveLock); + + tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(indexId)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for relation %u", indexId); + rd_rel = (Form_pg_class) GETSTRUCT(tuple); + + /* + * 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); + + /* Update the pg_class row */ + rd_rel->reltablespace = (tablespaceOid == MyDatabaseTableSpace) ? + InvalidOid : tablespaceOid; + CatalogTupleUpdate(pg_class, &tuple->t_self, tuple); + + heap_freetuple(tuple); + + table_close(pg_class, RowExclusiveLock); + + /* 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. @@ -3602,6 +3658,10 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, * reindex_relation - This routine is used to recreate all indexes * of a relation (and optionally its toast relation too, if any). * + * "tablespaceOid" defines the new tablespace where the indexes of + * the relation will be rebuilt. If InvalidOid is used, the default + * tablespace of each index is used instead. + * * "flags" is a bitmask that can include any combination of these bits: * * REINDEX_REL_PROCESS_TOAST: if true, process the toast table too (if any). @@ -3634,7 +3694,7 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, * index rebuild. */ bool -reindex_relation(Oid relid, int flags, int options) +reindex_relation(Oid relid, Oid tablespaceOid, int flags, int options) { Relation rel; Oid toast_relid; @@ -3708,7 +3768,8 @@ reindex_relation(Oid relid, int flags, int options) { Oid indexOid = lfirst_oid(indexId); - reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS), + reindex_index(indexOid, tablespaceOid, + !(flags & REINDEX_REL_CHECK_CONSTRAINTS), persistence, options); CommandCounterIncrement(); @@ -3741,7 +3802,7 @@ reindex_relation(Oid relid, int flags, int options) * still hold the lock on the master table. */ if ((flags & REINDEX_REL_PROCESS_TOAST) && OidIsValid(toast_relid)) - result |= reindex_relation(toast_relid, flags, options); + result |= reindex_relation(toast_relid, tablespaceOid, flags, options); return result; } diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index e9d7a7ff79b..5bd12eca382 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -1406,7 +1406,7 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, PROGRESS_CLUSTER_PHASE_REBUILD_INDEX); - reindex_relation(OIDOldHeap, reindex_flags, 0); + reindex_relation(OIDOldHeap, InvalidOid, reindex_flags, 0); /* Report that we are now doing clean up */ pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 52ce02f8981..c6d6b76de2d 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -87,7 +87,7 @@ static char *ChooseIndexNameAddition(List *colnames); static List *ChooseIndexColumnNames(List *indexElems); static void RangeVarCallbackForReindexIndex(const RangeVar *relation, Oid relId, Oid oldRelId, void *arg); -static bool ReindexRelationConcurrently(Oid relationOid, int options); +static bool ReindexRelationConcurrently(Oid relationOid, Oid tablespaceOid, int options); static void ReindexPartitionedIndex(Relation parentIdx); static void update_relispartition(Oid relationId, bool newval); @@ -2312,10 +2312,11 @@ ChooseIndexColumnNames(List *indexElems) * Recreate a specific index. */ void -ReindexIndex(RangeVar *indexRelation, int options, bool concurrent) +ReindexIndex(RangeVar *indexRelation, char *newTableSpaceName, int options, bool concurrent) { struct ReindexIndexCallbackState state; Oid indOid; + Oid tablespaceOid = InvalidOid; Relation irel; char persistence; @@ -2345,12 +2346,26 @@ ReindexIndex(RangeVar *indexRelation, int options, bool concurrent) } persistence = irel->rd_rel->relpersistence; + + /* Define new tablespaceOid if it is wanted by caller */ + if (newTableSpaceName) + { + tablespaceOid = get_tablespace_oid(newTableSpaceName, false); + + /* Can't move a non-shared relation into pg_global */ + if (tablespaceOid == GLOBALTABLESPACE_OID) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot move non-shared relation to tablespace \"%s\"", + newTableSpaceName))); + } + index_close(irel, NoLock); if (concurrent) - ReindexRelationConcurrently(indOid, options); + ReindexRelationConcurrently(indOid, tablespaceOid, options); else - reindex_index(indOid, false, persistence, + reindex_index(indOid, tablespaceOid, false, persistence, options | REINDEXOPT_REPORT_PROGRESS); } @@ -2429,10 +2444,11 @@ RangeVarCallbackForReindexIndex(const RangeVar *relation, * Recreate all indexes of a table (and of its toast table, if any) */ Oid -ReindexTable(RangeVar *relation, int options, bool concurrent) +ReindexTable(RangeVar *relation, char *newTableSpaceName, int options, bool concurrent) { Oid heapOid; bool result; + Oid tablespaceOid = InvalidOid; /* The lock level used here should match reindex_relation(). */ heapOid = RangeVarGetRelidExtended(relation, @@ -2440,9 +2456,22 @@ ReindexTable(RangeVar *relation, int options, bool concurrent) 0, RangeVarCallbackOwnsTable, NULL); + /* Define new tablespaceOid if it is wanted by caller */ + if (newTableSpaceName) + { + tablespaceOid = get_tablespace_oid(newTableSpaceName, false); + + /* Can't move a non-shared relation into pg_global */ + if (tablespaceOid == GLOBALTABLESPACE_OID) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot move non-shared relation to tablespace \"%s\"", + newTableSpaceName))); + } + if (concurrent) { - result = ReindexRelationConcurrently(heapOid, options); + result = ReindexRelationConcurrently(heapOid, tablespaceOid, options); if (!result) ereport(NOTICE, @@ -2452,6 +2481,7 @@ ReindexTable(RangeVar *relation, int options, bool concurrent) else { result = reindex_relation(heapOid, + tablespaceOid, REINDEX_REL_PROCESS_TOAST | REINDEX_REL_CHECK_CONSTRAINTS, options | REINDEXOPT_REPORT_PROGRESS); @@ -2473,10 +2503,11 @@ ReindexTable(RangeVar *relation, int options, bool concurrent) * That means this must not be called within a user transaction block! */ void -ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, +ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, char *newTableSpaceName, int options, bool concurrent) { Oid objectOid; + Oid tablespaceOid = InvalidOid; Relation relationRelation; TableScanDesc scan; ScanKeyData scan_keys[1]; @@ -2487,6 +2518,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, ListCell *l; int num_keys; bool concurrent_warning = false; + bool mapped_warning = false; AssertArg(objectName); Assert(objectKind == REINDEX_OBJECT_SCHEMA || @@ -2525,6 +2557,19 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, objectName); } + /* Define new tablespaceOid if it is wanted by caller */ + if (newTableSpaceName) + { + tablespaceOid = get_tablespace_oid(newTableSpaceName, false); + + /* Can't move a non-shared relation into pg_global */ + if (tablespaceOid == GLOBALTABLESPACE_OID) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot move non-shared relation to tablespace \"%s\"", + newTableSpaceName))); + } + /* * Create a memory context that will survive forced transaction commits we * do below. Since it is a child of PortalContext, it will go away @@ -2615,6 +2660,22 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, continue; } + /* + * Skip all mapped relations if TABLESPACE is specified. + * relfilenode == 0 checks after that, similarly to + * RelationIsMapped(). + */ + if (OidIsValid(tablespaceOid) && + !OidIsValid(classtuple->relfilenode)) + { + if (!mapped_warning) + ereport(WARNING, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot change tablespace of indexes for mapped relations, skipping all"))); + mapped_warning = true; + continue; + } + /* Save the list of relation OIDs in private context */ old = MemoryContextSwitchTo(private_context); @@ -2648,7 +2709,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, if (concurrent) { - (void) ReindexRelationConcurrently(relid, options); + (void) ReindexRelationConcurrently(relid, tablespaceOid, options); /* ReindexRelationConcurrently() does the verbose output */ } else @@ -2656,6 +2717,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, bool result; result = reindex_relation(relid, + tablespaceOid, REINDEX_REL_PROCESS_TOAST | REINDEX_REL_CHECK_CONSTRAINTS, options | REINDEXOPT_REPORT_PROGRESS); @@ -2688,6 +2750,9 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, * itself will be rebuilt. If 'relationOid' belongs to a partitioned table * then we issue a warning to mention these are not yet supported. * + * 'tablespaceOid' defines the new tablespace where the indexes of + * the relation will be rebuilt. + * * The locks taken on parent tables and involved indexes are kept until the * transaction is committed, at which point a session lock is taken on each * relation. Both of these protect against concurrent schema changes. @@ -2696,7 +2761,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, * indexes, when relevant), otherwise returns false. */ static bool -ReindexRelationConcurrently(Oid relationOid, int options) +ReindexRelationConcurrently(Oid relationOid, Oid tablespaceOid, int options) { List *heapRelationIds = NIL; List *indexIds = NIL; @@ -2769,6 +2834,12 @@ ReindexRelationConcurrently(Oid relationOid, int options) /* Open relation to get its indexes */ heapRelation = table_open(relationOid, ShareUpdateExclusiveLock); + if (OidIsValid(tablespaceOid) && RelationIsMapped(heapRelation)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot change tablespace of indexes for mapped relation \"%s\"", + RelationGetRelationName(heapRelation)))); + /* Add all the valid indexes of relation to list */ foreach(lc, RelationGetIndexList(heapRelation)) { @@ -2937,6 +3008,12 @@ ReindexRelationConcurrently(Oid relationOid, int options) heapRel = table_open(indexRel->rd_index->indrelid, ShareUpdateExclusiveLock); + if (OidIsValid(tablespaceOid) && RelationIsMapped(heapRel)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot change tablespace of mapped relation \"%s\"", + RelationGetRelationName(heapRel)))); + pgstat_progress_start_command(PROGRESS_COMMAND_CREATE_INDEX, RelationGetRelid(heapRel)); pgstat_progress_update_param(PROGRESS_CREATEIDX_COMMAND, @@ -2956,6 +3033,7 @@ ReindexRelationConcurrently(Oid relationOid, int options) /* Create new index definition based on given index */ newIndexId = index_concurrently_create_copy(heapRel, indexId, + tablespaceOid, concurrentName); /* diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 1c4394abea1..59cef46f2ff 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -1821,7 +1821,7 @@ ExecuteTruncateGuts(List *explicit_rels, List *relids, List *relids_logged, /* * Reconstruct the indexes to match, and we're done. */ - reindex_relation(heap_relid, REINDEX_REL_PROCESS_TOAST, 0); + reindex_relation(heap_relid, InvalidOid, REINDEX_REL_PROCESS_TOAST, 0); } pgstat_count_truncate(rel); diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 8034d5a51c5..1f32ad6582e 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -4391,6 +4391,7 @@ _copyReindexStmt(const ReindexStmt *from) COPY_STRING_FIELD(name); COPY_SCALAR_FIELD(options); COPY_SCALAR_FIELD(concurrent); + COPY_STRING_FIELD(tablespacename); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 9c8070c6406..cd45f1e6121 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -2118,6 +2118,7 @@ _equalReindexStmt(const ReindexStmt *a, const ReindexStmt *b) COMPARE_STRING_FIELD(name); COMPARE_SCALAR_FIELD(options); COMPARE_SCALAR_FIELD(concurrent); + COMPARE_STRING_FIELD(tablespacename); return true; } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index ad5be902b07..f3727f7bc7b 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -8358,31 +8358,33 @@ DropTransformStmt: DROP TRANSFORM opt_if_exists FOR Typename LANGUAGE name opt_d * * QUERY: * - * REINDEX [ (options) ] type [CONCURRENTLY] <name> + * REINDEX [ (options) ] type [CONCURRENTLY] <name> [ TABLESPACE <tablespace_name> ] *****************************************************************************/ ReindexStmt: - REINDEX reindex_target_type opt_concurrently qualified_name + REINDEX reindex_target_type opt_concurrently qualified_name OptTableSpace { ReindexStmt *n = makeNode(ReindexStmt); n->kind = $2; n->concurrent = $3; n->relation = $4; + n->tablespacename = $5; n->name = NULL; n->options = 0; $$ = (Node *)n; } - | REINDEX reindex_target_multitable opt_concurrently name + | REINDEX reindex_target_multitable opt_concurrently name OptTableSpace { ReindexStmt *n = makeNode(ReindexStmt); n->kind = $2; n->concurrent = $3; n->name = $4; + n->tablespacename = $5; n->relation = NULL; n->options = 0; $$ = (Node *)n; } - | REINDEX '(' reindex_option_list ')' reindex_target_type opt_concurrently qualified_name + | REINDEX '(' reindex_option_list ')' reindex_target_type opt_concurrently qualified_name OptTableSpace { ReindexStmt *n = makeNode(ReindexStmt); n->kind = $5; @@ -8390,9 +8392,10 @@ ReindexStmt: n->relation = $7; n->name = NULL; n->options = $3; + n->tablespacename = $8; $$ = (Node *)n; } - | REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name + | REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name OptTableSpace { ReindexStmt *n = makeNode(ReindexStmt); n->kind = $5; @@ -8400,6 +8403,7 @@ ReindexStmt: n->name = $7; n->relation = NULL; n->options = $3; + n->tablespacename = $8; $$ = (Node *)n; } ; diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 4474658ddf4..8bae41d579b 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -779,10 +779,10 @@ standard_ProcessUtility(PlannedStmt *pstmt, switch (stmt->kind) { case REINDEX_OBJECT_INDEX: - ReindexIndex(stmt->relation, stmt->options, stmt->concurrent); + ReindexIndex(stmt->relation, stmt->tablespacename, stmt->options, stmt->concurrent); break; case REINDEX_OBJECT_TABLE: - ReindexTable(stmt->relation, stmt->options, stmt->concurrent); + ReindexTable(stmt->relation, stmt->tablespacename, stmt->options, stmt->concurrent); break; case REINDEX_OBJECT_SCHEMA: case REINDEX_OBJECT_SYSTEM: @@ -798,7 +798,7 @@ standard_ProcessUtility(PlannedStmt *pstmt, (stmt->kind == REINDEX_OBJECT_SCHEMA) ? "REINDEX SCHEMA" : (stmt->kind == REINDEX_OBJECT_SYSTEM) ? "REINDEX SYSTEM" : "REINDEX DATABASE"); - ReindexMultipleTables(stmt->name, stmt->kind, stmt->options, stmt->concurrent); + ReindexMultipleTables(stmt->name, stmt->kind, stmt->tablespacename, stmt->options, stmt->concurrent); break; default: elog(ERROR, "unrecognized object type: %d", diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 22717914c73..4c960b05c4e 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -3366,6 +3366,12 @@ psql_completion(const char *text, int start, int end) COMPLETE_WITH_QUERY(Query_for_list_of_schemas); else if (Matches("REINDEX", "SYSTEM|DATABASE", "CONCURRENTLY")) COMPLETE_WITH_QUERY(Query_for_list_of_databases); + else if (Matches("REINDEX", MatchAny, "CONCURRENTLY", MatchAny)) + COMPLETE_WITH("TABLESPACE"); + else if (Matches("REINDEX", MatchAny, MatchAny)) + COMPLETE_WITH("TABLESPACE"); + else if (TailMatches("TABLESPACE")) + COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces); /* SECURITY LABEL */ else if (Matches("SECURITY")) diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index a2890c1314d..f38e978b45d 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -80,6 +80,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, @@ -131,8 +132,8 @@ extern void validate_index(Oid heapId, Oid indexId, Snapshot snapshot); extern void index_set_state_flags(Oid indexId, IndexStateFlagsAction action); -extern void reindex_index(Oid indexId, bool skip_constraint_checks, - char relpersistence, int options); +extern void reindex_index(Oid indexId, Oid tablespaceOid, bool skip_constraint_checks, + char relpersistence, int options); /* Flag bits for reindex_relation(): */ #define REINDEX_REL_PROCESS_TOAST 0x01 @@ -141,7 +142,7 @@ extern void reindex_index(Oid indexId, bool skip_constraint_checks, #define REINDEX_REL_FORCE_INDEXES_UNLOGGED 0x08 #define REINDEX_REL_FORCE_INDEXES_PERMANENT 0x10 -extern bool reindex_relation(Oid relid, int flags, int options); +extern bool reindex_relation(Oid relid, Oid tablespaceOid, int flags, int options); extern bool ReindexIsProcessingHeap(Oid heapOid); extern bool ReindexIsProcessingIndex(Oid indexOid); diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index dede9d788ec..4999e590e67 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -34,10 +34,10 @@ extern ObjectAddress DefineIndex(Oid relationId, bool check_not_in_use, bool skip_build, bool quiet); -extern void ReindexIndex(RangeVar *indexRelation, int options, bool concurrent); -extern Oid ReindexTable(RangeVar *relation, int options, bool concurrent); +extern void ReindexIndex(RangeVar *indexRelation, char *newTableSpaceName, int options, bool concurrent); +extern Oid ReindexTable(RangeVar *relation, char *newTableSpaceName, int options, bool concurrent); extern void ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, - int options, bool concurrent); + char *newTableSpaceName, int options, bool concurrent); extern char *makeObjectName(const char *name1, const char *name2, const char *label); extern char *ChooseRelationName(const char *name1, const char *name2, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index f67bd9fad59..2725860c90f 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3334,6 +3334,7 @@ typedef struct ReindexStmt const char *name; /* name of database to reindex */ int options; /* Reindex options flags */ bool concurrent; /* reindex concurrently? */ + char *tablespacename; /* name of tablespace to store index */ } ReindexStmt; /* ---------------------- diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source index a5f61a35dc5..7d698d4294d 100644 --- a/src/test/regress/input/tablespace.source +++ b/src/test/regress/input/tablespace.source @@ -17,6 +17,52 @@ 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 to test REINDEX with TABLESPACE change +CREATE TABLE regress_tblspace_test_tbl (num1 bigint, num2 double precision, num3 double precision); +INSERT INTO regress_tblspace_test_tbl (num1, num2, num3) + SELECT round(random()*100), random(), random()*42 + FROM generate_series(1, 20000) s(i); +CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1); + +-- check that REINDEX with TABLESPACE change is transactional +BEGIN; +REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; +REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; +ROLLBACK; +BEGIN; +REINDEX TABLE pg_am TABLESPACE regress_tblspace; +ROLLBACK; +SELECT relname FROM pg_class +WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace'); + +-- first, let us reindex and move the entire database, after that return everything back +REINDEX DATABASE regression TABLESPACE regress_tblspace; -- ok with warning +REINDEX DATABASE regression TABLESPACE pg_default; -- ok with warning +SELECT relname FROM pg_class +WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace'); + +-- check REINDEX with TABLESPACE change +REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok +REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; -- ok +REINDEX TABLE pg_authid TABLESPACE regress_tblspace; -- fail +REINDEX SYSTEM CONCURRENTLY postgres TABLESPACE regress_tblspace; -- fail +REINDEX TABLE CONCURRENTLY pg_am TABLESPACE regress_tblspace; -- fail +REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail +REINDEX TABLE pg_am TABLESPACE regress_tblspace; -- ok + +-- 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; + +-- move back to pg_default tablespace +REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl TABLESPACE pg_default; -- ok +REINDEX TABLE pg_am TABLESPACE pg_default; -- 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 a schema we can use CREATE SCHEMA testschema; @@ -279,6 +325,9 @@ ALTER TABLE ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default -- Should succeed DROP TABLESPACE regress_tblspace_renamed; +DROP INDEX regress_tblspace_test_tbl_idx; +DROP TABLE regress_tblspace_test_tbl; + 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 162b591b315..bd17feaa73f 100644 --- a/src/test/regress/output/tablespace.source +++ b/src/test/regress/output/tablespace.source @@ -20,6 +20,70 @@ 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 to test REINDEX with TABLESPACE change +CREATE TABLE regress_tblspace_test_tbl (num1 bigint, num2 double precision, num3 double precision); +INSERT INTO regress_tblspace_test_tbl (num1, num2, num3) + SELECT round(random()*100), random(), random()*42 + FROM generate_series(1, 20000) s(i); +CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1); +-- check that REINDEX with TABLESPACE change is transactional +BEGIN; +REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; +REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; +ROLLBACK; +BEGIN; +REINDEX TABLE pg_am TABLESPACE regress_tblspace; +ROLLBACK; +SELECT relname FROM pg_class +WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace'); + relname +--------- +(0 rows) + +-- first, let us reindex and move the entire database, after that return everything back +REINDEX DATABASE regression TABLESPACE regress_tblspace; -- ok with warning +WARNING: cannot change tablespace of indexes for mapped relations, skipping all +REINDEX DATABASE regression TABLESPACE pg_default; -- ok with warning +WARNING: cannot change tablespace of indexes for mapped relations, skipping all +SELECT relname FROM pg_class +WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace'); + relname +--------- +(0 rows) + +-- check REINDEX with TABLESPACE change +REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok +REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; -- ok +REINDEX TABLE pg_authid TABLESPACE regress_tblspace; -- fail +ERROR: cannot move system relation "pg_authid_rolname_index" +REINDEX SYSTEM CONCURRENTLY postgres TABLESPACE regress_tblspace; -- fail +ERROR: cannot reindex system catalogs concurrently +REINDEX TABLE CONCURRENTLY pg_am TABLESPACE regress_tblspace; -- fail +ERROR: cannot reindex system catalogs concurrently +REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail +ERROR: cannot move non-shared relation to tablespace "pg_global" +REINDEX TABLE pg_am TABLESPACE regress_tblspace; -- ok +-- 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 +------------------------------- + pg_am_name_index + pg_am_oid_index + regress_tblspace_test_tbl_idx +(3 rows) + +-- move back to pg_default tablespace +REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl TABLESPACE pg_default; -- ok +REINDEX TABLE pg_am TABLESPACE pg_default; -- 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 a schema we can use CREATE SCHEMA testschema; -- try a table @@ -736,6 +800,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 INDEX regress_tblspace_test_tbl_idx; +DROP TABLE regress_tblspace_test_tbl; DROP SCHEMA testschema CASCADE; NOTICE: drop cascades to 6 other objects DETAIL: drop cascades to table testschema.foo base-commit: 04334fde69132f335d9d70cfefe419bd1276b232 -- 2.19.1