On Wed, Apr 01, 2020 at 03:03:34PM +0900, Michael Paquier wrote: > On Tue, Mar 31, 2020 at 01:56:07PM +0300, Alexey Kondratov wrote: > > I am fine with allowing REINDEX (CONCURRENTLY), but then we will have to > > support both syntaxes as we already do for VACUUM. Anyway, if we agree to > > add parenthesized options to REINDEX/CLUSTER, then it should be done as a > > separated patch before the current patch set. > > would honestly prefer that for now on we only add the parenthesized > version of an option if something new is added to such utility > commands (vacuum, analyze, reindex, etc.) as that's much more > extensible from the point of view of the parser. And this, even if > you need to rework things a bit more things around > reindex_option_elem for the tablespace option proposed here.
Thanks for your input. I'd already converted VACUUM and REINDEX to use a parenthesized TABLESPACE option, and just converted CLUSTER to take an option list and do the same. Alexey suggested that those changes should be done as a separate patch, with the tablespace options built on top. Which makes sense. I had quite some fun rebasing these with patches in that order. However, I've kept my changes separate from Alexey's patch, to make it easier for him to integrate. So there's "fix!" commits which are not logically separate and should be read as if they're merged with their parent commits. That makes the patchset look kind of dirty. So I'm first going to send the "before rebase" patchset. There's a few fixme items, but I think this is in pretty good shape, and I'd appreciate review. I'll follow up later with the "after rebase" patchset. Maybe Alexey will want to integrate that. I claimed it would be easy, so I also implemented (INDEX_TABESPACE ..) option: template1=# VACUUM (TABLESPACE pg_default, INDEX_TABLESPACE ts, FULL) t; template1=# CLUSTER (TABLESPACE pg_default, INDEX_TABLESPACE ts) t; -- Justin
>From f108192a89a663214d46b84d86c8ce4fdce44663 Mon Sep 17 00:00:00 2001 From: Alexey Kondratov <kondratov.alek...@gmail.com> Date: Mon, 23 Mar 2020 21:10:29 +0300 Subject: [PATCH v15 1/6] 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 | 89 ++++++++++++-- src/backend/commands/cluster.c | 2 +- src/backend/commands/indexcmds.c | 139 ++++++++++++++++++++-- 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 | 39 ++++++ src/test/regress/output/tablespace.source | 50 ++++++++ 15 files changed, 354 insertions(+), 33 deletions(-) diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index c54a7c420d..b97bd673a5 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> @@ -174,6 +174,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 a 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 bd7ec923e9..5267ecfffb 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1247,9 +1247,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, @@ -1379,7 +1383,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, @@ -3429,10 +3434,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; @@ -3441,6 +3448,7 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, volatile bool skipped_constraint = false; PGRUsage ru0; bool progress = (options & REINDEXOPT_REPORT_PROGRESS) != 0; + bool set_tablespace = OidIsValid(tablespaceOid); pg_rusage_init(&ru0); @@ -3479,6 +3487,27 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, elog(ERROR, "unsupported relation kind for index \"%s\"", RelationGetRelationName(iRel)); + /* + * We don't support moving system relations into different tablespaces, + * unless allow_system_table_mods=1. + */ + if (set_tablespace && + !allowSystemTableMods && IsSystemRelation(iRel)) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied: \"%s\" is a system catalog", + RelationGetRelationName(iRel)))); + + /* + * We cannot support moving mapped relations into different tablespaces. + * (In particular this eliminates all shared catalogs.) + */ + if (set_tablespace && RelationIsMapped(iRel)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot change tablespace of mapped relation \"%s\"", + RelationGetRelationName(iRel)))); + /* * Don't allow reindex on temp tables of other backends ... their local * buffer manager is not going to cope. @@ -3505,6 +3534,47 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, */ CheckTableNotInUse(iRel, "REINDEX INDEX"); + if (tablespaceOid == MyDatabaseTableSpace) + tablespaceOid = InvalidOid; + + /* + * Set the new tablespace for the relation. Do that only in the + * case where the reindex caller wishes to enforce a new tablespace. + */ + if (set_tablespace && + 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; + 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. @@ -3643,6 +3713,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 current + * 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). @@ -3675,7 +3749,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; @@ -3766,7 +3840,8 @@ reindex_relation(Oid relid, int flags, int options) continue; } - reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS), + reindex_index(indexOid, tablespaceOid, + !(flags & REINDEX_REL_CHECK_CONSTRAINTS), persistence, options); CommandCounterIncrement(); @@ -3799,7 +3874,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 fc1cea0236..afc752e9d6 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 2e5997b5c3..056e390698 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); static bool CompareOpclassOptions(Datum *opts1, Datum *opts2, int natts); @@ -2366,10 +2366,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; @@ -2404,12 +2405,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 && persistence != RELPERSISTENCE_TEMP) - ReindexRelationConcurrently(indOid, options); + ReindexRelationConcurrently(indOid, tablespaceOid, options); else - reindex_index(indOid, false, persistence, + reindex_index(indOid, tablespaceOid, false, persistence, options | REINDEXOPT_REPORT_PROGRESS); } @@ -2488,10 +2503,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(). @@ -2506,9 +2522,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 && get_rel_persistence(heapOid) != RELPERSISTENCE_TEMP) { - result = ReindexRelationConcurrently(heapOid, options); + result = ReindexRelationConcurrently(heapOid, tablespaceOid, options); if (!result) ereport(NOTICE, @@ -2518,6 +2547,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); @@ -2539,10 +2569,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]; @@ -2553,6 +2584,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, ListCell *l; int num_keys; bool concurrent_warning = false; + bool tablespace_warning = false; AssertArg(objectName); Assert(objectKind == REINDEX_OBJECT_SCHEMA || @@ -2591,6 +2623,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 @@ -2681,6 +2726,35 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, continue; } + if (OidIsValid(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. + * relfilenode == 0 checks after that, similarly to + * RelationIsMapped(). + */ + if (!tablespace_warning) + ereport(WARNING, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot change tablespace of indexes on mapped relations, skipping all"))); + tablespace_warning = true; + continue; + } + } + /* Save the list of relation OIDs in private context */ old = MemoryContextSwitchTo(private_context); @@ -2714,7 +2788,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, if (concurrent && get_rel_persistence(relid) != RELPERSISTENCE_TEMP) { - (void) ReindexRelationConcurrently(relid, options); + (void) ReindexRelationConcurrently(relid, tablespaceOid, options); /* ReindexRelationConcurrently() does the verbose output */ } else @@ -2722,6 +2796,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); @@ -2754,6 +2829,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. @@ -2768,7 +2846,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, * anyway, and a non-concurrent reindex is more efficient. */ static bool -ReindexRelationConcurrently(Oid relationOid, int options) +ReindexRelationConcurrently(Oid relationOid, Oid tablespaceOid, int options) { List *heapRelationIds = NIL; List *indexIds = NIL; @@ -2841,6 +2919,27 @@ ReindexRelationConcurrently(Oid relationOid, int options) /* Open relation to get its indexes */ heapRelation = table_open(relationOid, ShareUpdateExclusiveLock); + /* + * We don't support moving system relations into different tablespaces, + * unless allow_system_table_mods=1. + */ + if (OidIsValid(tablespaceOid) && + !allowSystemTableMods && IsSystemRelation(heapRelation)) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied: \"%s\" is a system catalog", + RelationGetRelationName(heapRelation)))); + + /* + * We cannot support moving mapped relations into different tablespaces. + * (In particular this eliminates all shared catalogs.) + */ + if (OidIsValid(tablespaceOid) && RelationIsMapped(heapRelation)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot change tablespace of indexes on mapped relation \"%s\"", + RelationGetRelationName(heapRelation)))); + /* Add all the valid indexes of relation to list */ foreach(lc, RelationGetIndexList(heapRelation)) { @@ -3023,6 +3122,27 @@ ReindexRelationConcurrently(Oid relationOid, int options) if (indexRel->rd_rel->relpersistence == RELPERSISTENCE_TEMP) elog(ERROR, "cannot reindex a temporary table concurrently"); + /* + * We don't support moving system relations into different tablespaces, + * unless allow_system_table_mods=1. + */ + if (OidIsValid(tablespaceOid) && + !allowSystemTableMods && IsSystemRelation(indexRel)) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied: \"%s\" is a system catalog", + RelationGetRelationName(indexRel)))); + + /* + * We cannot support moving mapped relations into different tablespaces. + * (In particular this eliminates all shared catalogs.) + */ + if (OidIsValid(tablespaceOid) && RelationIsMapped(indexRel)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot change tablespace of mapped relation \"%s\"", + RelationGetRelationName(indexRel)))); + pgstat_progress_start_command(PROGRESS_COMMAND_CREATE_INDEX, RelationGetRelid(heapRel)); pgstat_progress_update_param(PROGRESS_CREATEIDX_COMMAND, @@ -3042,6 +3162,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 c8c88be2c9..5f6a9fe3e2 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -1871,7 +1871,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 c9a90d1191..9fe820f691 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -4407,6 +4407,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 d05ca26fcf..e87a696471 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -2131,6 +2131,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 eb0bf12cd8..beeacb9520 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -8395,31 +8395,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; @@ -8427,9 +8429,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; @@ -8437,6 +8440,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 b1f7f6e2d0..1d9e448a1c 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -924,10 +924,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: @@ -943,7 +943,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 ca8f0d75a6..f2d0e81bfe 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -3436,6 +3436,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 a2890c1314..f38e978b45 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 c77c9a6ed5..6c4f0996fa 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 77943f0637..b64d538671 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3364,6 +3364,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 a5f61a35dc..77ea4cbcee 100644 --- a/src/test/regress/input/tablespace.source +++ b/src/test/regress/input/tablespace.source @@ -17,6 +17,42 @@ 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; +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; -- 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; + +-- move indexes back to pg_default tablespace +REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl 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 +315,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 162b591b31..0db9929f44 100644 --- a/src/test/regress/output/tablespace.source +++ b/src/test/regress/output/tablespace.source @@ -20,6 +20,54 @@ 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; +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: permission denied: "pg_authid_rolname_index" is a system catalog +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; -- 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_tbl_idx +(1 row) + +-- move indexes back to pg_default tablespace +REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl 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 +784,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 -- 2.17.0
>From 578d6304fc7d5d684c05a7f20f4a9d25f8fd8b04 Mon Sep 17 00:00:00 2001 From: Alexey Kondratov <kondratov.alek...@gmail.com> Date: Tue, 24 Mar 2020 18:16:06 +0300 Subject: [PATCH v15 2/6] Allow CLUSTER and VACUUM FULL to change tablespace --- doc/src/sgml/ref/cluster.sgml | 11 ++++- doc/src/sgml/ref/vacuum.sgml | 11 +++++ src/backend/commands/cluster.c | 58 ++++++++++++++++++++--- src/backend/commands/vacuum.c | 51 ++++++++++++++++++-- src/backend/nodes/copyfuncs.c | 2 + src/backend/nodes/equalfuncs.c | 2 + src/backend/parser/gram.y | 45 ++++++++++++++++-- src/backend/postmaster/autovacuum.c | 1 + src/include/commands/cluster.h | 2 +- src/include/commands/vacuum.h | 2 + src/include/nodes/parsenodes.h | 2 + src/test/regress/input/tablespace.source | 23 ++++++++- src/test/regress/output/tablespace.source | 37 ++++++++++++++- 13 files changed, 231 insertions(+), 16 deletions(-) diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml index 4da60d8d56..1adf12c3b9 100644 --- a/doc/src/sgml/ref/cluster.sgml +++ b/doc/src/sgml/ref/cluster.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -CLUSTER [VERBOSE] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">index_name</replaceable> ] +CLUSTER [VERBOSE] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">index_name</replaceable> ] [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] CLUSTER [VERBOSE] </synopsis> </refsynopsisdiv> @@ -99,6 +99,15 @@ CLUSTER [VERBOSE] </listitem> </varlistentry> + <varlistentry> + <term><replaceable class="parameter">new_tablespace</replaceable></term> + <listitem> + <para> + The name of a specific tablespace to store clustered relations. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>VERBOSE</literal></term> <listitem> diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 846056a353..0a00125a36 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -23,6 +23,7 @@ PostgreSQL documentation <synopsis> VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ] +VACUUM ( FULL [, ...] ) [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ] <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase> @@ -299,6 +300,16 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet </para> </listitem> </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">new_tablespace</replaceable></term> + <listitem> + <para> + The name of a specific tablespace to write a new copy of the table. + </para> + </listitem> + </varlistentry> + </variablelist> </refsect1> diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index afc752e9d6..894b1fb98f 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -33,10 +33,12 @@ #include "catalog/namespace.h" #include "catalog/objectaccess.h" #include "catalog/pg_am.h" +#include "catalog/pg_tablespace.h" #include "catalog/toasting.h" #include "commands/cluster.h" #include "commands/progress.h" #include "commands/tablecmds.h" +#include "commands/tablespace.h" #include "commands/vacuum.h" #include "miscadmin.h" #include "optimizer/optimizer.h" @@ -67,7 +69,7 @@ typedef struct } RelToCluster; -static void rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose); +static void rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTableSpaceOid, bool verbose); static void copy_table_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose, bool *pSwapToastByContent, TransactionId *pFreezeXid, MultiXactId *pCutoffMulti); @@ -101,6 +103,22 @@ static List *get_tables_to_cluster(MemoryContext cluster_context); void cluster(ClusterStmt *stmt, bool isTopLevel) { + /* Oid of tablespace to use for clustered relation. */ + Oid tablespaceOid = InvalidOid; + + /* Select tablespace Oid to use. */ + if (stmt->tablespacename) + { + tablespaceOid = get_tablespace_oid(stmt->tablespacename, 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\"", + stmt->tablespacename))); + } + if (stmt->relation != NULL) { /* This is the single-relation case. */ @@ -182,7 +200,7 @@ cluster(ClusterStmt *stmt, bool isTopLevel) table_close(rel, NoLock); /* Do the job. */ - cluster_rel(tableOid, indexOid, stmt->options); + cluster_rel(tableOid, indexOid, tablespaceOid, stmt->options); } else { @@ -230,7 +248,7 @@ cluster(ClusterStmt *stmt, bool isTopLevel) /* functions in indexes may want a snapshot set */ PushActiveSnapshot(GetTransactionSnapshot()); /* Do the job. */ - cluster_rel(rvtc->tableOid, rvtc->indexOid, + cluster_rel(rvtc->tableOid, rvtc->indexOid, tablespaceOid, stmt->options | CLUOPT_RECHECK); PopActiveSnapshot(); CommitTransactionCommand(); @@ -262,7 +280,7 @@ cluster(ClusterStmt *stmt, bool isTopLevel) * and error messages should refer to the operation as VACUUM not CLUSTER. */ void -cluster_rel(Oid tableOid, Oid indexOid, int options) +cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, int options) { Relation OldHeap; bool verbose = ((options & CLUOPT_VERBOSE) != 0); @@ -375,6 +393,23 @@ cluster_rel(Oid tableOid, Oid indexOid, int options) (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot cluster a shared catalog"))); + if (OidIsValid(tablespaceOid) && + !allowSystemTableMods && IsSystemRelation(OldHeap)) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied: \"%s\" is a system catalog", + RelationGetRelationName(OldHeap)))); + + /* + * We cannot support moving mapped relations into different tablespaces. + * (In particular this eliminates all shared catalogs.) + */ + if (OidIsValid(tablespaceOid) && RelationIsMapped(OldHeap)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot change tablespace of mapped relation \"%s\"", + RelationGetRelationName(OldHeap)))); + /* * Don't process temp tables of other backends ... their local buffer * manager is not going to cope. @@ -425,7 +460,7 @@ cluster_rel(Oid tableOid, Oid indexOid, int options) TransferPredicateLocksToHeapRelation(OldHeap); /* rebuild_relation does all the dirty work */ - rebuild_relation(OldHeap, indexOid, verbose); + rebuild_relation(OldHeap, indexOid, tablespaceOid, verbose); /* NB: rebuild_relation does table_close() on OldHeap */ @@ -584,7 +619,7 @@ mark_index_clustered(Relation rel, Oid indexOid, bool is_internal) * NB: this routine closes OldHeap at the right time; caller should not. */ static void -rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose) +rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTablespaceOid, bool verbose) { Oid tableOid = RelationGetRelid(OldHeap); Oid tableSpace = OldHeap->rd_rel->reltablespace; @@ -595,6 +630,10 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose) TransactionId frozenXid; MultiXactId cutoffMulti; + /* Use new tablespace if passed. */ + if (OidIsValid(NewTablespaceOid)) + tableSpace = NewTablespaceOid; + /* Mark the correct index as clustered */ if (OidIsValid(indexOid)) mark_index_clustered(OldHeap, indexOid, true); @@ -1039,6 +1078,13 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class, */ Assert(!target_is_pg_class); + if (!allowSystemTableMods && IsSystemClass(r1, relform1) && + relform1->reltablespace != relform2->reltablespace) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied: \"%s\" is a system catalog", + get_rel_name(r1)))); + swaptemp = relform1->relfilenode; relform1->relfilenode = relform2->relfilenode; relform2->relfilenode = swaptemp; diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 59731d687f..95bec8d6ba 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -31,13 +31,16 @@ #include "access/tableam.h" #include "access/transam.h" #include "access/xact.h" +#include "catalog/catalog.h" #include "catalog/namespace.h" #include "catalog/pg_database.h" #include "catalog/pg_inherits.h" #include "catalog/pg_namespace.h" +#include "catalog/pg_tablespace.h" #include "commands/cluster.h" #include "commands/defrem.h" #include "commands/vacuum.h" +#include "commands/tablespace.h" #include "miscadmin.h" #include "nodes/makefuncs.h" #include "pgstat.h" @@ -106,6 +109,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) bool disable_page_skipping = false; bool parallel_option = false; ListCell *lc; + Oid tablespaceOid = InvalidOid; /* Oid of tablespace to use for relations + * after VACUUM FULL. */ /* Set default value */ params.index_cleanup = VACOPT_TERNARY_DEFAULT; @@ -241,6 +246,28 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) params.multixact_freeze_table_age = -1; } + /* Get tablespace Oid to use. */ + if (vacstmt->tablespacename) + { + if (params.options & VACOPT_FULL) + { + tablespaceOid = get_tablespace_oid(vacstmt->tablespacename, 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\"", + vacstmt->tablespacename))); + } + else + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("incompatible TABLESPACE option"), + errdetail("You can only use TABLESPACE with VACUUM FULL."))); + } + params.tablespace_oid = tablespaceOid; + /* user-invoked vacuum is never "for wraparound" */ params.is_wraparound = false; @@ -1672,8 +1699,9 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) LOCKMODE lmode; Relation onerel; LockRelId onerelid; - Oid toast_relid; - Oid save_userid; + Oid toast_relid, + save_userid, + tablespaceOid = InvalidOid; int save_sec_context; int save_nestlevel; @@ -1807,6 +1835,23 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) return true; } + /* + * We cannot support moving system relations into different tablespaces, + * unless allow_system_table_mods=1. + */ + if (params->options & VACOPT_FULL && + OidIsValid(params->tablespace_oid) && + IsSystemRelation(onerel) && !allowSystemTableMods) + { + ereport(WARNING, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("skipping tablespace change of \"%s\"", + RelationGetRelationName(onerel)), + errdetail("Cannot move system relation, only VACUUM is performed."))); + } + else + tablespaceOid = params->tablespace_oid; + /* * Get a session-level lock too. This will protect our access to the * relation across multiple transactions, so that we can vacuum the @@ -1876,7 +1921,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) cluster_options |= CLUOPT_VERBOSE; /* VACUUM FULL is now a variant of CLUSTER; see cluster.c */ - cluster_rel(relid, InvalidOid, cluster_options); + cluster_rel(relid, InvalidOid, tablespaceOid, cluster_options); } else table_relation_vacuum(onerel, params, vac_strategy); diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 9fe820f691..55ff3e8419 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3317,6 +3317,7 @@ _copyClusterStmt(const ClusterStmt *from) COPY_NODE_FIELD(relation); COPY_STRING_FIELD(indexname); COPY_SCALAR_FIELD(options); + COPY_STRING_FIELD(tablespacename); return newnode; } @@ -3902,6 +3903,7 @@ _copyVacuumStmt(const VacuumStmt *from) COPY_NODE_FIELD(options); COPY_NODE_FIELD(rels); COPY_SCALAR_FIELD(is_vacuumcmd); + COPY_STRING_FIELD(tablespacename); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index e87a696471..baee0b944a 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1215,6 +1215,7 @@ _equalClusterStmt(const ClusterStmt *a, const ClusterStmt *b) COMPARE_NODE_FIELD(relation); COMPARE_STRING_FIELD(indexname); COMPARE_SCALAR_FIELD(options); + COMPARE_SCALAR_FIELD(tablespacename); return true; } @@ -1702,6 +1703,7 @@ _equalVacuumStmt(const VacuumStmt *a, const VacuumStmt *b) COMPARE_NODE_FIELD(options); COMPARE_NODE_FIELD(rels); COMPARE_SCALAR_FIELD(is_vacuumcmd); + COMPARE_SCALAR_FIELD(tablespacename); return true; } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index beeacb9520..8ff12fe086 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -10599,14 +10599,14 @@ CreateConversionStmt: /***************************************************************************** * * QUERY: - * CLUSTER [VERBOSE] <qualified_name> [ USING <index_name> ] - * CLUSTER [VERBOSE] + * CLUSTER [VERBOSE] <qualified_name> [ USING <index_name> ] [ TABLESPACE <tablespace_name> ] + * CLUSTER [VERBOSE] [ TABLESPACE <tablespace_name> ] * CLUSTER [VERBOSE] <index_name> ON <qualified_name> (for pre-8.3) * *****************************************************************************/ ClusterStmt: - CLUSTER opt_verbose qualified_name cluster_index_specification + CLUSTER opt_verbose qualified_name cluster_index_specification OptTableSpace { ClusterStmt *n = makeNode(ClusterStmt); n->relation = $3; @@ -10614,6 +10614,7 @@ ClusterStmt: n->options = 0; if ($2) n->options |= CLUOPT_VERBOSE; + n->tablespacename = $5; $$ = (Node*)n; } | CLUSTER opt_verbose @@ -10624,6 +10625,7 @@ ClusterStmt: n->options = 0; if ($2) n->options |= CLUOPT_VERBOSE; + n->tablespacename = NULL; $$ = (Node*)n; } /* kept for pre-8.3 compatibility */ @@ -10635,6 +10637,7 @@ ClusterStmt: n->options = 0; if ($2) n->options |= CLUOPT_VERBOSE; + n->tablespacename = NULL; $$ = (Node*)n; } ; @@ -10649,6 +10652,8 @@ cluster_index_specification: * * QUERY: * VACUUM + * VACUUM FULL [ TABLESPACE <tablespace_name> ] [ <table_and_columns> [, ...] ] + * VACUUM (FULL) [ TABLESPACE <tablespace_name> ] [ <table_and_columns> [, ...] ] * ANALYZE * *****************************************************************************/ @@ -10671,6 +10676,28 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose opt_analyze opt_vacuum_relati makeDefElem("analyze", NULL, @5)); n->rels = $6; n->is_vacuumcmd = true; + n->tablespacename = NULL; + $$ = (Node *)n; + } + | VACUUM opt_full opt_freeze opt_verbose opt_analyze TABLESPACE name opt_vacuum_relation_list + { + VacuumStmt *n = makeNode(VacuumStmt); + n->options = NIL; + if ($2) + n->options = lappend(n->options, + makeDefElem("full", NULL, @2)); + if ($3) + n->options = lappend(n->options, + makeDefElem("freeze", NULL, @3)); + if ($4) + n->options = lappend(n->options, + makeDefElem("verbose", NULL, @4)); + if ($5) + n->options = lappend(n->options, + makeDefElem("analyze", NULL, @5)); + n->tablespacename = $7; + n->rels = $8; + n->is_vacuumcmd = true; $$ = (Node *)n; } | VACUUM '(' vac_analyze_option_list ')' opt_vacuum_relation_list @@ -10679,6 +10706,16 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose opt_analyze opt_vacuum_relati n->options = $3; n->rels = $5; n->is_vacuumcmd = true; + n->tablespacename = NULL; + $$ = (Node *) n; + } + | VACUUM '(' vac_analyze_option_list ')' TABLESPACE name opt_vacuum_relation_list + { + VacuumStmt *n = makeNode(VacuumStmt); + n->options = $3; + n->tablespacename = $6; + n->rels = $7; + n->is_vacuumcmd = true; $$ = (Node *) n; } ; @@ -10692,6 +10729,7 @@ AnalyzeStmt: analyze_keyword opt_verbose opt_vacuum_relation_list makeDefElem("verbose", NULL, @2)); n->rels = $3; n->is_vacuumcmd = false; + n->tablespacename = NULL; $$ = (Node *)n; } | analyze_keyword '(' vac_analyze_option_list ')' opt_vacuum_relation_list @@ -10700,6 +10738,7 @@ AnalyzeStmt: analyze_keyword opt_verbose opt_vacuum_relation_list n->options = $3; n->rels = $5; n->is_vacuumcmd = false; + n->tablespacename = NULL; $$ = (Node *) n; } ; diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 7e97ffab27..8acc321faa 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -2897,6 +2897,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, tab->at_params.multixact_freeze_table_age = multixact_freeze_table_age; tab->at_params.is_wraparound = wraparound; tab->at_params.log_min_duration = log_min_duration; + tab->at_params.tablespace_oid = InvalidOid; tab->at_vacuum_cost_limit = vac_cost_limit; tab->at_vacuum_cost_delay = vac_cost_delay; tab->at_relname = NULL; diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h index e05884781b..aecc0c312a 100644 --- a/src/include/commands/cluster.h +++ b/src/include/commands/cluster.h @@ -19,7 +19,7 @@ extern void cluster(ClusterStmt *stmt, bool isTopLevel); -extern void cluster_rel(Oid tableOid, Oid indexOid, int options); +extern void cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, int options); extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid, bool recheck, LOCKMODE lockmode); extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal); diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 2779bea5c9..6758e9812f 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -229,6 +229,8 @@ typedef struct VacuumParams * disabled. */ int nworkers; + Oid tablespace_oid; /* tablespace Oid to use for relations + * after VACUUM FULL */ } VacuumParams; /* GUC parameters */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index b64d538671..9575a7985c 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3203,6 +3203,7 @@ typedef struct ClusterStmt NodeTag type; RangeVar *relation; /* relation being indexed, or NULL if all */ char *indexname; /* original index defined */ + char *tablespacename; /* tablespace name to use for clustered relation */ int options; /* OR of ClusterOption flags */ } ClusterStmt; @@ -3219,6 +3220,7 @@ typedef struct VacuumStmt List *options; /* list of DefElem nodes */ List *rels; /* list of VacuumRelation, or NIL for all */ bool is_vacuumcmd; /* true for VACUUM, false for ANALYZE */ + char *tablespacename; /* tablespace name to use for vacuumed relation */ } VacuumStmt; /* diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source index 77ea4cbcee..352db323c8 100644 --- a/src/test/regress/input/tablespace.source +++ b/src/test/regress/input/tablespace.source @@ -17,7 +17,7 @@ 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 to test REINDEX, CLUSTER and VACUUM FULL 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 @@ -41,11 +41,32 @@ 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; -- fail +-- check that all indexes moved to new tablespace +SELECT relname FROM pg_class +WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace') +ORDER BY relname; + +-- check CLUSTER with TABLESPACE change +CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok +CLUSTER pg_authid USING pg_authid_rolname_index TABLESPACE regress_tblspace; -- fail +CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- 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; +-- check VACUUM with TABLESPACE change +VACUUM (FULL, ANALYSE, FREEZE) TABLESPACE pg_default regress_tblspace_test_tbl; -- ok +VACUUM (FULL) TABLESPACE pg_default pg_authid; -- skip with warning +VACUUM (ANALYSE) TABLESPACE pg_default; -- fail +VACUUM (FULL) TABLESPACE pg_global regress_tblspace_test_tbl; -- fail + +-- check that all tables moved back to pg_default +SELECT relname FROM pg_class +WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace') +ORDER BY relname; + -- move indexes back to pg_default tablespace REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl TABLESPACE pg_default; -- ok diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source index 0db9929f44..a05176069b 100644 --- a/src/test/regress/output/tablespace.source +++ b/src/test/regress/output/tablespace.source @@ -20,7 +20,7 @@ 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 to test REINDEX, CLUSTER and VACUUM FULL 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 @@ -50,9 +50,44 @@ 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; -- fail ERROR: permission denied: "pg_am_name_index" is a system catalog +-- check that all indexes 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_tbl_idx +(1 row) + +-- check CLUSTER with TABLESPACE change +CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok +CLUSTER pg_authid USING pg_authid_rolname_index TABLESPACE regress_tblspace; -- fail +ERROR: cannot cluster a shared catalog +CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail +ERROR: cannot move non-shared relation to tablespace "pg_global" -- 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_tbl + regress_tblspace_test_tbl_idx +(2 rows) + +-- check VACUUM with TABLESPACE change +VACUUM (FULL, ANALYSE, FREEZE) TABLESPACE pg_default regress_tblspace_test_tbl; -- ok +VACUUM (FULL) TABLESPACE pg_default pg_authid; -- skip with warning +WARNING: skipping tablespace change of "pg_authid" +DETAIL: Cannot move system relation, only VACUUM is performed. +VACUUM (ANALYSE) TABLESPACE pg_default; -- fail +ERROR: incompatible TABLESPACE option +DETAIL: You can only use TABLESPACE with VACUUM FULL. +VACUUM (FULL) TABLESPACE pg_global regress_tblspace_test_tbl; -- fail +ERROR: cannot move non-shared relation to tablespace "pg_global" +-- check that all tables moved back to pg_default +SELECT relname FROM pg_class +WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace') ORDER BY relname; relname ------------------------------- -- 2.17.0
>From e5628c99835b93846519240e5577330b0752f95b Mon Sep 17 00:00:00 2001 From: Justin Pryzby <pryz...@telsasoft.com> Date: Thu, 26 Mar 2020 22:33:56 -0500 Subject: [PATCH v15 3/6] fixes2 --- doc/src/sgml/ref/cluster.sgml | 2 +- doc/src/sgml/ref/reindex.sgml | 2 +- doc/src/sgml/ref/vacuum.sgml | 4 ++-- src/backend/commands/cluster.c | 3 +++ src/backend/commands/indexcmds.c | 4 ++-- 5 files changed, 9 insertions(+), 6 deletions(-) diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml index 1adf12c3b9..897fea9692 100644 --- a/doc/src/sgml/ref/cluster.sgml +++ b/doc/src/sgml/ref/cluster.sgml @@ -103,7 +103,7 @@ CLUSTER [VERBOSE] <term><replaceable class="parameter">new_tablespace</replaceable></term> <listitem> <para> - The name of a specific tablespace to store clustered relations. + The tablespace where the clustered relation will be rebuilt. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index b97bd673a5..e989729327 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -191,7 +191,7 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN <term><replaceable class="parameter">new_tablespace</replaceable></term> <listitem> <para> - The name of a specific tablespace to store rebuilt indexes. + The tablespace where indexes will be rebuilt. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 0a00125a36..681486bb38 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -22,7 +22,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ] -VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ] +VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ] VACUUM ( FULL [, ...] ) [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ] <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase> @@ -305,7 +305,7 @@ VACUUM ( FULL [, ...] ) [ TABLESPACE <replaceable class="parameter">new_tablespa <term><replaceable class="parameter">new_tablespace</replaceable></term> <listitem> <para> - The name of a specific tablespace to write a new copy of the table. + The tablespace where the table will be rebuilt. </para> </listitem> </varlistentry> diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index 894b1fb98f..abc84f8ff1 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -278,6 +278,9 @@ cluster(ClusterStmt *stmt, bool isTopLevel) * If indexOid is InvalidOid, the table will be rewritten in physical order * instead of index order. This is the new implementation of VACUUM FULL, * and error messages should refer to the operation as VACUUM not CLUSTER. + * + * "tablespaceOid" is the tablespace to use for the rebuilt relation. If + * InvalidOid, use the tablespace in-use instead. */ void cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, int options) diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 056e390698..d19d53f551 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -2742,8 +2742,8 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, char else if (!OidIsValid(classtuple->relfilenode)) { /* - * Skip all mapped relations. - * relfilenode == 0 checks after that, similarly to + * Skip all mapped relations if TABLESPACE is specified. + * OidIsValid(relfilenode) checks that, similar to * RelationIsMapped(). */ if (!tablespace_warning) -- 2.17.0
>From 22f6c5e65aaeeab8fb00f231fe55a5fb2476c616 Mon Sep 17 00:00:00 2001 From: Justin Pryzby <pryz...@telsasoft.com> Date: Thu, 26 Mar 2020 22:08:40 -0500 Subject: [PATCH v15 4/6] Parenthesized syntax: VACUUM (FULL, TABLESPACE ..).. ..it seems to be impossible to use "VACUUM FULL t TABLESPACE ts" since "tablespace" isn't a fully reserved word? Parenthesized list is the intended way to add new options anyway. --- doc/src/sgml/ref/vacuum.sgml | 5 ++- src/backend/commands/vacuum.c | 51 ++++++++++++----------- src/backend/nodes/copyfuncs.c | 1 - src/backend/nodes/equalfuncs.c | 1 - src/backend/parser/gram.y | 34 --------------- src/include/nodes/parsenodes.h | 1 - src/test/regress/input/tablespace.source | 8 ++-- src/test/regress/output/tablespace.source | 8 ++-- 8 files changed, 38 insertions(+), 71 deletions(-) diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 681486bb38..d3ea13b94d 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -22,8 +22,8 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ] -VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ] -VACUUM ( FULL [, ...] ) [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ] +VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ] +VACUUM ( FULL [, ...] ) [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ] <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase> @@ -36,6 +36,7 @@ VACUUM ( FULL [, ...] ) [ TABLESPACE <replaceable class="parameter">new_tablespa INDEX_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ] TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ] PARALLEL <replaceable class="parameter">integer</replaceable> + TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase> diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 95bec8d6ba..005fb97a7b 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -109,8 +109,10 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) bool disable_page_skipping = false; bool parallel_option = false; ListCell *lc; - Oid tablespaceOid = InvalidOid; /* Oid of tablespace to use for relations - * after VACUUM FULL. */ + + /* Name and Oid of tablespace to use for relations after VACUUM FULL. */ + char *tablespacename = NULL; + Oid tablespaceOid = InvalidOid; /* Set default value */ params.index_cleanup = VACOPT_TERNARY_DEFAULT; @@ -148,6 +150,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) params.index_cleanup = get_vacopt_ternary_value(opt); else if (strcmp(opt->defname, "truncate") == 0) params.truncate = get_vacopt_ternary_value(opt); + else if (strcmp(opt->defname, "tablespace") == 0) + tablespacename = defGetString(opt); else if (strcmp(opt->defname, "parallel") == 0) { parallel_option = true; @@ -209,6 +213,27 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot specify both FULL and PARALLEL options"))); + /* Get tablespace Oid to use. */ + if (tablespacename) + { + if ((params.options & VACOPT_FULL) == 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("incompatible TABLESPACE option"), + errdetail("You can only use TABLESPACE with VACUUM FULL."))); + + tablespaceOid = get_tablespace_oid(tablespacename, 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\"", + tablespacename))); + + } + params.tablespace_oid = tablespaceOid; + /* * Make sure VACOPT_ANALYZE is specified if any column lists are present. */ @@ -246,28 +271,6 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) params.multixact_freeze_table_age = -1; } - /* Get tablespace Oid to use. */ - if (vacstmt->tablespacename) - { - if (params.options & VACOPT_FULL) - { - tablespaceOid = get_tablespace_oid(vacstmt->tablespacename, 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\"", - vacstmt->tablespacename))); - } - else - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("incompatible TABLESPACE option"), - errdetail("You can only use TABLESPACE with VACUUM FULL."))); - } - params.tablespace_oid = tablespaceOid; - /* user-invoked vacuum is never "for wraparound" */ params.is_wraparound = false; diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 55ff3e8419..d2d82ea35e 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3903,7 +3903,6 @@ _copyVacuumStmt(const VacuumStmt *from) COPY_NODE_FIELD(options); COPY_NODE_FIELD(rels); COPY_SCALAR_FIELD(is_vacuumcmd); - COPY_STRING_FIELD(tablespacename); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index baee0b944a..66f4a403a5 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1703,7 +1703,6 @@ _equalVacuumStmt(const VacuumStmt *a, const VacuumStmt *b) COMPARE_NODE_FIELD(options); COMPARE_NODE_FIELD(rels); COMPARE_SCALAR_FIELD(is_vacuumcmd); - COMPARE_SCALAR_FIELD(tablespacename); return true; } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 8ff12fe086..23d8c2194a 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -10676,28 +10676,6 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose opt_analyze opt_vacuum_relati makeDefElem("analyze", NULL, @5)); n->rels = $6; n->is_vacuumcmd = true; - n->tablespacename = NULL; - $$ = (Node *)n; - } - | VACUUM opt_full opt_freeze opt_verbose opt_analyze TABLESPACE name opt_vacuum_relation_list - { - VacuumStmt *n = makeNode(VacuumStmt); - n->options = NIL; - if ($2) - n->options = lappend(n->options, - makeDefElem("full", NULL, @2)); - if ($3) - n->options = lappend(n->options, - makeDefElem("freeze", NULL, @3)); - if ($4) - n->options = lappend(n->options, - makeDefElem("verbose", NULL, @4)); - if ($5) - n->options = lappend(n->options, - makeDefElem("analyze", NULL, @5)); - n->tablespacename = $7; - n->rels = $8; - n->is_vacuumcmd = true; $$ = (Node *)n; } | VACUUM '(' vac_analyze_option_list ')' opt_vacuum_relation_list @@ -10706,16 +10684,6 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose opt_analyze opt_vacuum_relati n->options = $3; n->rels = $5; n->is_vacuumcmd = true; - n->tablespacename = NULL; - $$ = (Node *) n; - } - | VACUUM '(' vac_analyze_option_list ')' TABLESPACE name opt_vacuum_relation_list - { - VacuumStmt *n = makeNode(VacuumStmt); - n->options = $3; - n->tablespacename = $6; - n->rels = $7; - n->is_vacuumcmd = true; $$ = (Node *) n; } ; @@ -10729,7 +10697,6 @@ AnalyzeStmt: analyze_keyword opt_verbose opt_vacuum_relation_list makeDefElem("verbose", NULL, @2)); n->rels = $3; n->is_vacuumcmd = false; - n->tablespacename = NULL; $$ = (Node *)n; } | analyze_keyword '(' vac_analyze_option_list ')' opt_vacuum_relation_list @@ -10738,7 +10705,6 @@ AnalyzeStmt: analyze_keyword opt_verbose opt_vacuum_relation_list n->options = $3; n->rels = $5; n->is_vacuumcmd = false; - n->tablespacename = NULL; $$ = (Node *) n; } ; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 9575a7985c..0873553d17 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3220,7 +3220,6 @@ typedef struct VacuumStmt List *options; /* list of DefElem nodes */ List *rels; /* list of VacuumRelation, or NIL for all */ bool is_vacuumcmd; /* true for VACUUM, false for ANALYZE */ - char *tablespacename; /* tablespace name to use for vacuumed relation */ } VacuumStmt; /* diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source index 352db323c8..0846aa58c3 100644 --- a/src/test/regress/input/tablespace.source +++ b/src/test/regress/input/tablespace.source @@ -57,10 +57,10 @@ WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspa ORDER BY relname; -- check VACUUM with TABLESPACE change -VACUUM (FULL, ANALYSE, FREEZE) TABLESPACE pg_default regress_tblspace_test_tbl; -- ok -VACUUM (FULL) TABLESPACE pg_default pg_authid; -- skip with warning -VACUUM (ANALYSE) TABLESPACE pg_default; -- fail -VACUUM (FULL) TABLESPACE pg_global regress_tblspace_test_tbl; -- fail +VACUUM (FULL, ANALYSE, FREEZE, TABLESPACE pg_default) regress_tblspace_test_tbl; -- ok +VACUUM (FULL, TABLESPACE pg_default) pg_authid; -- skip with warning +VACUUM (ANALYSE, TABLESPACE pg_default); -- fail +VACUUM (FULL, TABLESPACE pg_global) regress_tblspace_test_tbl; -- fail -- check that all tables moved back to pg_default SELECT relname FROM pg_class diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source index a05176069b..db28a23a23 100644 --- a/src/test/regress/output/tablespace.source +++ b/src/test/regress/output/tablespace.source @@ -76,14 +76,14 @@ ORDER BY relname; (2 rows) -- check VACUUM with TABLESPACE change -VACUUM (FULL, ANALYSE, FREEZE) TABLESPACE pg_default regress_tblspace_test_tbl; -- ok -VACUUM (FULL) TABLESPACE pg_default pg_authid; -- skip with warning +VACUUM (FULL, ANALYSE, FREEZE, TABLESPACE pg_default) regress_tblspace_test_tbl; -- ok +VACUUM (FULL, TABLESPACE pg_default) pg_authid; -- skip with warning WARNING: skipping tablespace change of "pg_authid" DETAIL: Cannot move system relation, only VACUUM is performed. -VACUUM (ANALYSE) TABLESPACE pg_default; -- fail +VACUUM (ANALYSE, TABLESPACE pg_default); -- fail ERROR: incompatible TABLESPACE option DETAIL: You can only use TABLESPACE with VACUUM FULL. -VACUUM (FULL) TABLESPACE pg_global regress_tblspace_test_tbl; -- fail +VACUUM (FULL, TABLESPACE pg_global) regress_tblspace_test_tbl; -- fail ERROR: cannot move non-shared relation to tablespace "pg_global" -- check that all tables moved back to pg_default SELECT relname FROM pg_class -- 2.17.0
>From 0098be5100129b400cc733a58ef0b2cdc6f7719b Mon Sep 17 00:00:00 2001 From: Justin Pryzby <pryz...@telsasoft.com> Date: Fri, 27 Mar 2020 17:50:46 -0500 Subject: [PATCH v15 5/6] Change REINDEX/CLUSTER to accept an option list.. ..like reindex (CONCURRENTLY) --- doc/src/sgml/ref/cluster.sgml | 6 +- doc/src/sgml/ref/reindex.sgml | 38 +++++------ src/backend/commands/cluster.c | 32 ++++++++-- src/backend/commands/indexcmds.c | 59 ++++++++--------- src/backend/nodes/copyfuncs.c | 3 +- src/backend/nodes/equalfuncs.c | 3 +- src/backend/parser/gram.y | 77 ++++++++++++++++------- src/backend/tcop/utility.c | 36 +++++++++-- src/include/commands/cluster.h | 3 +- src/include/commands/defrem.h | 7 +-- src/include/nodes/parsenodes.h | 5 +- src/test/regress/input/tablespace.source | 20 +++--- src/test/regress/output/tablespace.source | 20 +++--- 13 files changed, 201 insertions(+), 108 deletions(-) diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml index 897fea9692..1c0668af63 100644 --- a/doc/src/sgml/ref/cluster.sgml +++ b/doc/src/sgml/ref/cluster.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -CLUSTER [VERBOSE] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">index_name</replaceable> ] [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] +CLUSTER [VERBOSE] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">index_name</replaceable> ] CLUSTER [VERBOSE] </synopsis> </refsynopsisdiv> @@ -116,6 +116,10 @@ CLUSTER [VERBOSE] </para> </listitem> </varlistentry> + +XXX + [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] + </variablelist> </refsect1> diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index e989729327..950bf2b538 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -21,11 +21,12 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable> [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] +REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable> <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase> VERBOSE + TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> </synopsis> </refsynopsisdiv> @@ -141,19 +142,6 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN </listitem> </varlistentry> - <varlistentry> - <term><replaceable class="parameter">name</replaceable></term> - <listitem> - <para> - The name of the specific index, table, or database to be - reindexed. Index and table names can be schema-qualified. - Presently, <command>REINDEX DATABASE</command> and <command>REINDEX SYSTEM</command> - can only reindex the current database, so their parameter must match - the current database's name. - </para> - </listitem> - </varlistentry> - <varlistentry> <term><literal>CONCURRENTLY</literal></term> <listitem> @@ -174,6 +162,15 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN </listitem> </varlistentry> + <varlistentry> + <term><literal>VERBOSE</literal></term> + <listitem> + <para> + Prints a progress report as each index is reindexed. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>TABLESPACE</literal></term> <listitem> @@ -188,22 +185,27 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN </varlistentry> <varlistentry> - <term><replaceable class="parameter">new_tablespace</replaceable></term> + <term><replaceable class="parameter">name</replaceable></term> <listitem> <para> - The tablespace where indexes will be rebuilt. + The name of the specific index, table, or database to be + reindexed. Index and table names can be schema-qualified. + Presently, <command>REINDEX DATABASE</command> and <command>REINDEX SYSTEM</command> + can only reindex the current database, so their parameter must match + the current database's name. </para> </listitem> </varlistentry> <varlistentry> - <term><literal>VERBOSE</literal></term> + <term><replaceable class="parameter">new_tablespace</replaceable></term> <listitem> <para> - Prints a progress report as each index is reindexed. + The tablespace where indexes will be rebuilt. </para> </listitem> </varlistentry> + </variablelist> </refsect1> diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index abc84f8ff1..cef4beb2c8 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -36,6 +36,7 @@ #include "catalog/pg_tablespace.h" #include "catalog/toasting.h" #include "commands/cluster.h" +#include "commands/defrem.h" #include "commands/progress.h" #include "commands/tablecmds.h" #include "commands/tablespace.h" @@ -101,22 +102,43 @@ static List *get_tables_to_cluster(MemoryContext cluster_context); *--------------------------------------------------------------------------- */ void -cluster(ClusterStmt *stmt, bool isTopLevel) +cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel) { - /* Oid of tablespace to use for clustered relation. */ + ListCell *lc; + /* Name and Oid of tablespace to use for clustered relation. */ + char *tablespaceName = NULL; Oid tablespaceOid = InvalidOid; + /* Parse list of generic parameter not handled by the parser */ + foreach(lc, stmt->params) + { + DefElem *opt = (DefElem *) lfirst(lc); + + if (strcmp(opt->defname, "verbose") == 0) + stmt->options |= CLUOPT_VERBOSE; + // XXX: handle boolean opt: VERBOSE off + else if (strcmp(opt->defname, "tablespace") == 0) + tablespaceName = defGetString(opt); + // XXX: if (tablespaceOid == GLOBALTABLESPACE_OID) + else + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("unrecognized CLUSTER option \"%s\"", + opt->defname), + parser_errposition(pstate, opt->location))); + } + /* Select tablespace Oid to use. */ - if (stmt->tablespacename) + if (tablespaceName) { - tablespaceOid = get_tablespace_oid(stmt->tablespacename, false); + tablespaceOid = get_tablespace_oid(tablespaceName, 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\"", - stmt->tablespacename))); + tablespaceName))); } if (stmt->relation != NULL) diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index d19d53f551..9c09f2cbdc 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -2366,8 +2366,9 @@ ChooseIndexColumnNames(List *indexElems) * Recreate a specific index. */ void -ReindexIndex(RangeVar *indexRelation, char *newTableSpaceName, int options, bool concurrent) +ReindexIndex(ReindexStmt *stmt) { + RangeVar *indexRelation = stmt->relation; struct ReindexIndexCallbackState state; Oid indOid; Oid tablespaceOid = InvalidOid; @@ -2384,10 +2385,10 @@ ReindexIndex(RangeVar *indexRelation, char *newTableSpaceName, int options, bool * upgrade the lock, but that's OK, because other sessions can't hold * locks on our temporary table. */ - state.concurrent = concurrent; + state.concurrent = stmt->concurrent; state.locked_table_oid = InvalidOid; indOid = RangeVarGetRelidExtended(indexRelation, - concurrent ? ShareUpdateExclusiveLock : AccessExclusiveLock, + stmt->concurrent ? ShareUpdateExclusiveLock : AccessExclusiveLock, 0, RangeVarCallbackForReindexIndex, &state); @@ -2407,25 +2408,25 @@ ReindexIndex(RangeVar *indexRelation, char *newTableSpaceName, int options, bool persistence = irel->rd_rel->relpersistence; /* Define new tablespaceOid if it is wanted by caller */ - if (newTableSpaceName) + if (stmt->tablespacename) { - tablespaceOid = get_tablespace_oid(newTableSpaceName, false); + tablespaceOid = get_tablespace_oid(stmt->tablespacename, 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))); + stmt->tablespacename))); } index_close(irel, NoLock); - if (concurrent && persistence != RELPERSISTENCE_TEMP) - ReindexRelationConcurrently(indOid, tablespaceOid, options); + if (stmt->concurrent && persistence != RELPERSISTENCE_TEMP) + ReindexRelationConcurrently(indOid, tablespaceOid, stmt->options); else reindex_index(indOid, tablespaceOid, false, persistence, - options | REINDEXOPT_REPORT_PROGRESS); + stmt->options | REINDEXOPT_REPORT_PROGRESS); } /* @@ -2503,8 +2504,9 @@ RangeVarCallbackForReindexIndex(const RangeVar *relation, * Recreate all indexes of a table (and of its toast table, if any) */ Oid -ReindexTable(RangeVar *relation, char *newTableSpaceName, int options, bool concurrent) +ReindexTable(ReindexStmt *stmt) { + RangeVar *relation = stmt->relation; Oid heapOid; bool result; Oid tablespaceOid = InvalidOid; @@ -2518,26 +2520,26 @@ ReindexTable(RangeVar *relation, char *newTableSpaceName, int options, bool conc * locks on our temporary table. */ heapOid = RangeVarGetRelidExtended(relation, - concurrent ? ShareUpdateExclusiveLock : ShareLock, + stmt->concurrent ? ShareUpdateExclusiveLock : ShareLock, 0, RangeVarCallbackOwnsTable, NULL); /* Define new tablespaceOid if it is wanted by caller */ - if (newTableSpaceName) + if (stmt->tablespacename) { - tablespaceOid = get_tablespace_oid(newTableSpaceName, false); + tablespaceOid = get_tablespace_oid(stmt->tablespacename, 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))); + stmt->tablespacename))); } - if (concurrent && get_rel_persistence(heapOid) != RELPERSISTENCE_TEMP) + if (stmt->concurrent && get_rel_persistence(heapOid) != RELPERSISTENCE_TEMP) { - result = ReindexRelationConcurrently(heapOid, tablespaceOid, options); + result = ReindexRelationConcurrently(heapOid, tablespaceOid, stmt->options); if (!result) ereport(NOTICE, @@ -2550,7 +2552,7 @@ ReindexTable(RangeVar *relation, char *newTableSpaceName, int options, bool conc tablespaceOid, REINDEX_REL_PROCESS_TOAST | REINDEX_REL_CHECK_CONSTRAINTS, - options | REINDEXOPT_REPORT_PROGRESS); + stmt->options | REINDEXOPT_REPORT_PROGRESS); if (!result) ereport(NOTICE, (errmsg("table \"%s\" has no indexes to reindex", @@ -2569,9 +2571,10 @@ ReindexTable(RangeVar *relation, char *newTableSpaceName, int options, bool conc * That means this must not be called within a user transaction block! */ void -ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, char *newTableSpaceName, - int options, bool concurrent) +ReindexMultipleTables(ReindexStmt *stmt) { + const char *objectName = stmt->name; + ReindexObjectType objectKind = stmt->kind; Oid objectOid; Oid tablespaceOid = InvalidOid; Relation relationRelation; @@ -2591,7 +2594,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, char objectKind == REINDEX_OBJECT_SYSTEM || objectKind == REINDEX_OBJECT_DATABASE); - if (objectKind == REINDEX_OBJECT_SYSTEM && concurrent) + if (objectKind == REINDEX_OBJECT_SYSTEM && stmt->concurrent) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot reindex system catalogs concurrently"))); @@ -2624,16 +2627,16 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, char } /* Define new tablespaceOid if it is wanted by caller */ - if (newTableSpaceName) + if (stmt->tablespacename) { - tablespaceOid = get_tablespace_oid(newTableSpaceName, false); + tablespaceOid = get_tablespace_oid(stmt->tablespacename, 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))); + stmt->tablespacename))); } /* @@ -2715,7 +2718,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, char * Skip system tables, since index_create() would reject indexing them * concurrently (and it would likely fail if we tried). */ - if (concurrent && + if (stmt->concurrent && IsCatalogRelationOid(relid)) { if (!concurrent_warning) @@ -2786,9 +2789,9 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, char /* functions in indexes may want a snapshot set */ PushActiveSnapshot(GetTransactionSnapshot()); - if (concurrent && get_rel_persistence(relid) != RELPERSISTENCE_TEMP) + if (stmt->concurrent && get_rel_persistence(relid) != RELPERSISTENCE_TEMP) { - (void) ReindexRelationConcurrently(relid, tablespaceOid, options); + (void) ReindexRelationConcurrently(relid, tablespaceOid, stmt->options); /* ReindexRelationConcurrently() does the verbose output */ } else @@ -2799,9 +2802,9 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, char tablespaceOid, REINDEX_REL_PROCESS_TOAST | REINDEX_REL_CHECK_CONSTRAINTS, - options | REINDEXOPT_REPORT_PROGRESS); + stmt->options | REINDEXOPT_REPORT_PROGRESS); - if (result && (options & REINDEXOPT_VERBOSE)) + if (result && (stmt->options & REINDEXOPT_VERBOSE)) ereport(INFO, (errmsg("table \"%s.%s\" was reindexed", get_namespace_name(get_rel_namespace(relid)), diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index d2d82ea35e..a2aa687771 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3317,7 +3317,7 @@ _copyClusterStmt(const ClusterStmt *from) COPY_NODE_FIELD(relation); COPY_STRING_FIELD(indexname); COPY_SCALAR_FIELD(options); - COPY_STRING_FIELD(tablespacename); + COPY_NODE_FIELD(params); return newnode; } @@ -4406,6 +4406,7 @@ _copyReindexStmt(const ReindexStmt *from) COPY_SCALAR_FIELD(kind); COPY_NODE_FIELD(relation); COPY_STRING_FIELD(name); + COPY_NODE_FIELD(rawoptions); COPY_SCALAR_FIELD(options); COPY_SCALAR_FIELD(concurrent); COPY_STRING_FIELD(tablespacename); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 66f4a403a5..3967d0ce08 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1215,7 +1215,7 @@ _equalClusterStmt(const ClusterStmt *a, const ClusterStmt *b) COMPARE_NODE_FIELD(relation); COMPARE_STRING_FIELD(indexname); COMPARE_SCALAR_FIELD(options); - COMPARE_SCALAR_FIELD(tablespacename); + COMPARE_NODE_FIELD(params); return true; } @@ -2130,6 +2130,7 @@ _equalReindexStmt(const ReindexStmt *a, const ReindexStmt *b) COMPARE_SCALAR_FIELD(kind); COMPARE_NODE_FIELD(relation); COMPARE_STRING_FIELD(name); + COMPARE_NODE_FIELD(rawoptions); COMPARE_SCALAR_FIELD(options); COMPARE_SCALAR_FIELD(concurrent); COMPARE_STRING_FIELD(tablespacename); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 23d8c2194a..c152d78846 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -511,7 +511,10 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <list> explain_option_list %type <ival> reindex_target_type reindex_target_multitable -%type <ival> reindex_option_list reindex_option_elem +%type <str> reindex_option_name +%type <node> reindex_option_arg +%type <list> reindex_option_list +%type <defelt> reindex_option_elem %type <node> copy_generic_opt_arg copy_generic_opt_arg_list_item %type <defelt> copy_generic_opt_elem @@ -8395,52 +8398,48 @@ DropTransformStmt: DROP TRANSFORM opt_if_exists FOR Typename LANGUAGE name opt_d * * QUERY: * - * REINDEX [ (options) ] type [CONCURRENTLY] <name> [ TABLESPACE <tablespace_name> ] + * REINDEX [ (options) ] type [CONCURRENTLY] <name> *****************************************************************************/ ReindexStmt: - REINDEX reindex_target_type opt_concurrently qualified_name OptTableSpace + REINDEX reindex_target_type opt_concurrently qualified_name { ReindexStmt *n = makeNode(ReindexStmt); n->kind = $2; n->concurrent = $3; n->relation = $4; - n->tablespacename = $5; n->name = NULL; - n->options = 0; + n->rawoptions = NIL; $$ = (Node *)n; } - | REINDEX reindex_target_multitable opt_concurrently name OptTableSpace + | REINDEX reindex_target_multitable opt_concurrently name { ReindexStmt *n = makeNode(ReindexStmt); n->kind = $2; n->concurrent = $3; n->name = $4; - n->tablespacename = $5; n->relation = NULL; - n->options = 0; + n->rawoptions = NIL; $$ = (Node *)n; } - | REINDEX '(' reindex_option_list ')' reindex_target_type opt_concurrently qualified_name OptTableSpace + | REINDEX '(' reindex_option_list ')' reindex_target_type opt_concurrently qualified_name { ReindexStmt *n = makeNode(ReindexStmt); n->kind = $5; n->concurrent = $6; n->relation = $7; n->name = NULL; - n->options = $3; - n->tablespacename = $8; + n->rawoptions = $3; $$ = (Node *)n; } - | REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name OptTableSpace + | REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name { ReindexStmt *n = makeNode(ReindexStmt); n->kind = $5; n->concurrent = $6; n->name = $7; n->relation = NULL; - n->options = $3; - n->tablespacename = $8; + n->rawoptions = $3; $$ = (Node *)n; } ; @@ -8454,11 +8453,31 @@ reindex_target_multitable: | DATABASE { $$ = REINDEX_OBJECT_DATABASE; } ; reindex_option_list: - reindex_option_elem { $$ = $1; } - | reindex_option_list ',' reindex_option_elem { $$ = $1 | $3; } + reindex_option_elem + { + $$ = list_make1($1); + } + | reindex_option_list ',' reindex_option_elem + { + $$ = lappend($1, $3); + } ; + reindex_option_elem: - VERBOSE { $$ = REINDEXOPT_VERBOSE; } + reindex_option_name reindex_option_arg + { + $$ = makeDefElem($1, $2, @1); + } + ; + +reindex_option_name: + NonReservedWord { $$ = $1; } + ; + +reindex_option_arg: + opt_boolean_or_string { $$ = (Node *) makeString($1); } + | NumericOnly { $$ = (Node *) $1; } + | /* EMPTY */ { $$ = NULL; } ; /***************************************************************************** @@ -10599,14 +10618,15 @@ CreateConversionStmt: /***************************************************************************** * * QUERY: - * CLUSTER [VERBOSE] <qualified_name> [ USING <index_name> ] [ TABLESPACE <tablespace_name> ] - * CLUSTER [VERBOSE] [ TABLESPACE <tablespace_name> ] + * CLUSTER [VERBOSE] <qualified_name> [ USING <index_name> ] + * CLUSTER [VERBOSE] [(options)] <qualified_name> [ USING <index_name> ] + * CLUSTER [VERBOSE] * CLUSTER [VERBOSE] <index_name> ON <qualified_name> (for pre-8.3) * *****************************************************************************/ ClusterStmt: - CLUSTER opt_verbose qualified_name cluster_index_specification OptTableSpace + CLUSTER opt_verbose qualified_name cluster_index_specification { ClusterStmt *n = makeNode(ClusterStmt); n->relation = $3; @@ -10614,7 +10634,18 @@ ClusterStmt: n->options = 0; if ($2) n->options |= CLUOPT_VERBOSE; - n->tablespacename = $5; + n->params = NIL; + $$ = (Node*)n; + } +/* XXX: reusing reindex_option_list */ + | CLUSTER opt_verbose '(' reindex_option_list ')' qualified_name cluster_index_specification + { + ClusterStmt *n = makeNode(ClusterStmt); + n->relation = $6; + n->indexname = $7; + if ($2) + n->options |= CLUOPT_VERBOSE; + n->params = $4; $$ = (Node*)n; } | CLUSTER opt_verbose @@ -10625,7 +10656,7 @@ ClusterStmt: n->options = 0; if ($2) n->options |= CLUOPT_VERBOSE; - n->tablespacename = NULL; + n->params = NIL; $$ = (Node*)n; } /* kept for pre-8.3 compatibility */ @@ -10637,7 +10668,7 @@ ClusterStmt: n->options = 0; if ($2) n->options |= CLUOPT_VERBOSE; - n->tablespacename = NULL; + n->params = NIL; $$ = (Node*)n; } ; diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 1d9e448a1c..ee9e3ed836 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -524,6 +524,33 @@ ProcessUtility(PlannedStmt *pstmt, dest, qc); } +/* Parse rawoptions into options flags and tablespace */ +static +void parse_reindex_options(ParseState *pstate, ReindexStmt *stmt) +{ + ListCell *lc; + /* Parse options list. */ + foreach(lc, stmt->rawoptions) + { + DefElem *opt = (DefElem *) lfirst(lc); + + if (strcmp(opt->defname, "verbose") == 0) + stmt->options |= REINDEXOPT_VERBOSE; + // XXX: handle boolean opt: VERBOSE off + else if (strcmp(opt->defname, "concurrently") == 0) + stmt->concurrent = true; + else if (strcmp(opt->defname, "tablespace") == 0) + stmt->tablespacename = defGetString(opt); + // XXX: if (tablespaceOid == GLOBALTABLESPACE_OID) + else + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("unrecognized REINDEX option \"%s\"", + opt->defname), + parser_errposition(pstate, opt->location))); + } +} + /* * standard_ProcessUtility itself deals only with utility commands for * which we do not provide event trigger support. Commands that do have @@ -816,7 +843,7 @@ standard_ProcessUtility(PlannedStmt *pstmt, break; case T_ClusterStmt: - cluster((ClusterStmt *) parsetree, isTopLevel); + cluster(pstate, (ClusterStmt *) parsetree, isTopLevel); break; case T_VacuumStmt: @@ -921,13 +948,14 @@ standard_ProcessUtility(PlannedStmt *pstmt, PreventInTransactionBlock(isTopLevel, "REINDEX CONCURRENTLY"); + parse_reindex_options(pstate, stmt); switch (stmt->kind) { case REINDEX_OBJECT_INDEX: - ReindexIndex(stmt->relation, stmt->tablespacename, stmt->options, stmt->concurrent); + ReindexIndex(stmt); break; case REINDEX_OBJECT_TABLE: - ReindexTable(stmt->relation, stmt->tablespacename, stmt->options, stmt->concurrent); + ReindexTable(stmt); break; case REINDEX_OBJECT_SCHEMA: case REINDEX_OBJECT_SYSTEM: @@ -943,7 +971,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->tablespacename, stmt->options, stmt->concurrent); + ReindexMultipleTables(stmt); break; default: elog(ERROR, "unrecognized object type: %d", diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h index aecc0c312a..bc9f881d8c 100644 --- a/src/include/commands/cluster.h +++ b/src/include/commands/cluster.h @@ -14,11 +14,12 @@ #define CLUSTER_H #include "nodes/parsenodes.h" +#include "parser/parse_node.h" #include "storage/lock.h" #include "utils/relcache.h" -extern void cluster(ClusterStmt *stmt, bool isTopLevel); +extern void cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel); extern void cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, int options); extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid, bool recheck, LOCKMODE lockmode); diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 6c4f0996fa..7020edbdbb 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -34,10 +34,9 @@ extern ObjectAddress DefineIndex(Oid relationId, bool check_not_in_use, bool skip_build, bool quiet); -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, - char *newTableSpaceName, int options, bool concurrent); +extern void ReindexIndex(ReindexStmt *stmt); +extern Oid ReindexTable(ReindexStmt *stmt); +extern void ReindexMultipleTables(ReindexStmt *stmt); 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 0873553d17..21b1f51950 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3203,8 +3203,8 @@ typedef struct ClusterStmt NodeTag type; RangeVar *relation; /* relation being indexed, or NULL if all */ char *indexname; /* original index defined */ - char *tablespacename; /* tablespace name to use for clustered relation */ int options; /* OR of ClusterOption flags */ + List *params; /* Params not further parsed by the grammar */ } ClusterStmt; /* ---------------------- @@ -3363,7 +3363,8 @@ typedef struct ReindexStmt * etc. */ RangeVar *relation; /* Table or index to reindex */ const char *name; /* name of database to reindex */ - int options; /* Reindex options flags */ + List *rawoptions; /* Raw options */ + int options; /* Parsed options */ 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 0846aa58c3..7c70f6dc4c 100644 --- a/src/test/regress/input/tablespace.source +++ b/src/test/regress/input/tablespace.source @@ -26,20 +26,20 @@ 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; +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'); -- 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; -- fail +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 indexes moved to new tablespace SELECT relname FROM pg_class @@ -68,7 +68,7 @@ WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspa ORDER BY relname; -- move indexes back to pg_default tablespace -REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl TABLESPACE pg_default; -- ok +REINDEX (TABLESPACE pg_default) TABLE CONCURRENTLY regress_tblspace_test_tbl; -- ok -- check that all relations moved back to pg_default SELECT relname FROM pg_class diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source index db28a23a23..c6dcee3985 100644 --- a/src/test/regress/output/tablespace.source +++ b/src/test/regress/output/tablespace.source @@ -28,8 +28,8 @@ INSERT INTO regress_tblspace_test_tbl (num1, num2, num3) 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; +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'); @@ -38,17 +38,17 @@ WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspa (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 +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: permission denied: "pg_authid_rolname_index" is a system catalog -REINDEX SYSTEM CONCURRENTLY postgres TABLESPACE regress_tblspace; -- fail +REINDEX (TABLESPACE regress_tblspace) SYSTEM CONCURRENTLY postgres; -- fail ERROR: cannot reindex system catalogs concurrently -REINDEX TABLE CONCURRENTLY pg_am TABLESPACE regress_tblspace; -- fail +REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_am; -- fail ERROR: cannot reindex system catalogs concurrently -REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail +REINDEX (TABLESPACE pg_global) INDEX regress_tblspace_test_tbl_idx; -- fail ERROR: cannot move non-shared relation to tablespace "pg_global" -REINDEX TABLE pg_am TABLESPACE regress_tblspace; -- fail +REINDEX (TABLESPACE regress_tblspace) TABLE pg_am; -- fail ERROR: permission denied: "pg_am_name_index" is a system catalog -- check that all indexes moved to new tablespace SELECT relname FROM pg_class @@ -95,7 +95,7 @@ ORDER BY relname; (1 row) -- move indexes back to pg_default tablespace -REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl TABLESPACE pg_default; -- ok +REINDEX (TABLESPACE pg_default) TABLE CONCURRENTLY regress_tblspace_test_tbl; -- 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'); -- 2.17.0
>From 927820dfbeed9cfe51c24f462271cf8cb13c0ed0 Mon Sep 17 00:00:00 2001 From: Justin Pryzby <pryz...@telsasoft.com> Date: Tue, 31 Mar 2020 20:35:41 -0500 Subject: [PATCH v15 6/6] Implement VACUUM FULL/CLUSTER INDEX_TABLESPACE <tablespace> --- src/backend/commands/cluster.c | 77 ++++++++++++++++------- src/backend/commands/matview.c | 3 +- src/backend/commands/tablecmds.c | 2 +- src/backend/commands/vacuum.c | 65 ++++++++++++------- src/backend/postmaster/autovacuum.c | 1 + src/include/commands/cluster.h | 5 +- src/include/commands/vacuum.h | 6 +- src/test/regress/input/tablespace.source | 6 +- src/test/regress/output/tablespace.source | 6 +- 9 files changed, 113 insertions(+), 58 deletions(-) diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index cef4beb2c8..ffd1dac814 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -70,7 +70,8 @@ typedef struct } RelToCluster; -static void rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTableSpaceOid, bool verbose); +static Oid cluster_get_tablespace_oid(const char *tablespaceName); +static void rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTableSpaceOid, Oid NewIdxTableSpaceOid, bool verbose); static void copy_table_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose, bool *pSwapToastByContent, TransactionId *pFreezeXid, MultiXactId *pCutoffMulti); @@ -106,8 +107,10 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel) { ListCell *lc; /* Name and Oid of tablespace to use for clustered relation. */ - char *tablespaceName = NULL; + char *tablespaceName = NULL, + *idxtablespaceName = NULL; Oid tablespaceOid = InvalidOid; + Oid idxtablespaceOid = InvalidOid; /* Parse list of generic parameter not handled by the parser */ foreach(lc, stmt->params) @@ -120,6 +123,8 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel) else if (strcmp(opt->defname, "tablespace") == 0) tablespaceName = defGetString(opt); // XXX: if (tablespaceOid == GLOBALTABLESPACE_OID) + else if (strcmp(opt->defname, "index_tablespace") == 0) + idxtablespaceName = defGetString(opt); else ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), @@ -128,18 +133,8 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel) parser_errposition(pstate, opt->location))); } - /* Select tablespace Oid to use. */ - if (tablespaceName) - { - tablespaceOid = get_tablespace_oid(tablespaceName, 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\"", - tablespaceName))); - } + tablespaceOid = cluster_get_tablespace_oid(tablespaceName); + idxtablespaceOid = cluster_get_tablespace_oid(idxtablespaceName); if (stmt->relation != NULL) { @@ -222,7 +217,7 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel) table_close(rel, NoLock); /* Do the job. */ - cluster_rel(tableOid, indexOid, tablespaceOid, stmt->options); + cluster_rel(tableOid, indexOid, tablespaceOid, idxtablespaceOid, stmt->options); } else { @@ -271,7 +266,7 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel) PushActiveSnapshot(GetTransactionSnapshot()); /* Do the job. */ cluster_rel(rvtc->tableOid, rvtc->indexOid, tablespaceOid, - stmt->options | CLUOPT_RECHECK); + idxtablespaceOid, stmt->options | CLUOPT_RECHECK); PopActiveSnapshot(); CommitTransactionCommand(); } @@ -305,7 +300,7 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel) * InvalidOid, use the tablespace in-use instead. */ void -cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, int options) +cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, Oid indextablespaceOid, int options) { Relation OldHeap; bool verbose = ((options & CLUOPT_VERBOSE) != 0); @@ -485,7 +480,7 @@ cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, int options) TransferPredicateLocksToHeapRelation(OldHeap); /* rebuild_relation does all the dirty work */ - rebuild_relation(OldHeap, indexOid, tablespaceOid, verbose); + rebuild_relation(OldHeap, indexOid, tablespaceOid, indextablespaceOid, verbose); /* NB: rebuild_relation does table_close() on OldHeap */ @@ -635,6 +630,27 @@ mark_index_clustered(Relation rel, Oid indexOid, bool is_internal) table_close(pg_index, RowExclusiveLock); } +/* Return Oid of given tablespace */ +static Oid +cluster_get_tablespace_oid(const char *tablespaceName) +{ + Oid ret; + + if (tablespaceName == NULL) + return InvalidOid; + + ret = get_tablespace_oid(tablespaceName, false); + + // XXX: check this in cluster_rel ? + /* Can't move a non-shared relation into pg_global */ + if (ret == GLOBALTABLESPACE_OID) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot move non-shared relation to tablespace \"%s\"", + tablespaceName))); + return ret; +} + /* * rebuild_relation: rebuild an existing relation in index or physical order * @@ -644,10 +660,11 @@ mark_index_clustered(Relation rel, Oid indexOid, bool is_internal) * NB: this routine closes OldHeap at the right time; caller should not. */ static void -rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTablespaceOid, bool verbose) +rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTablespaceOid, Oid NewIdxTablespaceOid, bool verbose) { Oid tableOid = RelationGetRelid(OldHeap); Oid tableSpace = OldHeap->rd_rel->reltablespace; + Oid idxtableSpace; Oid OIDNewHeap; char relpersistence; bool is_system_catalog; @@ -659,6 +676,22 @@ rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTablespaceOid, bool verb if (OidIsValid(NewTablespaceOid)) tableSpace = NewTablespaceOid; + if (OidIsValid(NewIdxTablespaceOid)) + idxtableSpace = NewIdxTablespaceOid; + else if (!OidIsValid(indexOid)) + idxtableSpace = InvalidOid; + else + { + /* Look up in syscache */ + bool isNull; + HeapTuple tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(indexOid)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for relation %u", indexOid); + idxtableSpace = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_reltablespace, + &isNull); + ReleaseSysCache(tuple); + } + /* Mark the correct index as clustered */ if (OidIsValid(indexOid)) mark_index_clustered(OldHeap, indexOid, true); @@ -686,7 +719,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTablespaceOid, bool verb finish_heap_swap(tableOid, OIDNewHeap, is_system_catalog, swap_toast_by_content, false, true, frozenXid, cutoffMulti, - relpersistence); + relpersistence, idxtableSpace); } @@ -1415,7 +1448,7 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, bool is_internal, TransactionId frozenXid, MultiXactId cutoffMulti, - char newrelpersistence) + char newrelpersistence, Oid idxtableSpace) { ObjectAddress object; Oid mapped_tables[4]; @@ -1477,7 +1510,7 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, PROGRESS_CLUSTER_PHASE_REBUILD_INDEX); - reindex_relation(OIDOldHeap, InvalidOid, reindex_flags, 0); + reindex_relation(OIDOldHeap, idxtableSpace, reindex_flags, 0); /* Report that we are now doing clean up */ pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index e5a5eef102..40c57baccd 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -848,7 +848,8 @@ static void refresh_by_heap_swap(Oid matviewOid, Oid OIDNewHeap, char relpersistence) { finish_heap_swap(matviewOid, OIDNewHeap, false, false, true, true, - RecentXmin, ReadNextMultiXactId(), relpersistence); + RecentXmin, ReadNextMultiXactId(), relpersistence, + InvalidOid); } /* diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 5f6a9fe3e2..fc05c194ef 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -4916,7 +4916,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode, !OidIsValid(tab->newTableSpace), RecentXmin, ReadNextMultiXactId(), - persistence); + persistence, InvalidOid); } else { diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 005fb97a7b..7c9733dd55 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -90,6 +90,7 @@ static void vac_truncate_clog(TransactionId frozenXID, static bool vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params); static double compute_parallel_delay(void); static VacOptTernaryValue get_vacopt_ternary_value(DefElem *def); +static Oid vacuum_get_tablespace_oid(VacuumParams *params, const char *tablespacename); /* * Primary entry point for manual VACUUM and ANALYZE commands @@ -110,9 +111,9 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) bool parallel_option = false; ListCell *lc; - /* Name and Oid of tablespace to use for relations after VACUUM FULL. */ - char *tablespacename = NULL; - Oid tablespaceOid = InvalidOid; + /* Tablespace to use for relations after VACUUM FULL. */ + char *tablespacename = NULL, + *indextablespacename = NULL; /* Set default value */ params.index_cleanup = VACOPT_TERNARY_DEFAULT; @@ -152,6 +153,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) params.truncate = get_vacopt_ternary_value(opt); else if (strcmp(opt->defname, "tablespace") == 0) tablespacename = defGetString(opt); + else if (strcmp(opt->defname, "index_tablespace") == 0) + indextablespacename = defGetString(opt); else if (strcmp(opt->defname, "parallel") == 0) { parallel_option = true; @@ -214,25 +217,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) errmsg("cannot specify both FULL and PARALLEL options"))); /* Get tablespace Oid to use. */ - if (tablespacename) - { - if ((params.options & VACOPT_FULL) == 0) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("incompatible TABLESPACE option"), - errdetail("You can only use TABLESPACE with VACUUM FULL."))); - - tablespaceOid = get_tablespace_oid(tablespacename, 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\"", - tablespacename))); - - } - params.tablespace_oid = tablespaceOid; + params.tablespace_oid = vacuum_get_tablespace_oid(¶ms, tablespacename); + params.index_tablespace_oid = vacuum_get_tablespace_oid(¶ms, indextablespacename); /* * Make sure VACOPT_ANALYZE is specified if any column lists are present. @@ -1704,7 +1690,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) LockRelId onerelid; Oid toast_relid, save_userid, - tablespaceOid = InvalidOid; + tablespaceOid = InvalidOid, + indextablespaceOid = InvalidOid; // XXX: are these needed? int save_sec_context; int save_nestlevel; @@ -1853,7 +1840,10 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) errdetail("Cannot move system relation, only VACUUM is performed."))); } else + { tablespaceOid = params->tablespace_oid; + indextablespaceOid = params->index_tablespace_oid; + } /* * Get a session-level lock too. This will protect our access to the @@ -1924,7 +1914,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) cluster_options |= CLUOPT_VERBOSE; /* VACUUM FULL is now a variant of CLUSTER; see cluster.c */ - cluster_rel(relid, InvalidOid, tablespaceOid, cluster_options); + cluster_rel(relid, InvalidOid, tablespaceOid, indextablespaceOid, cluster_options); } else table_relation_vacuum(onerel, params, vac_strategy); @@ -2152,3 +2142,30 @@ get_vacopt_ternary_value(DefElem *def) { return defGetBoolean(def) ? VACOPT_TERNARY_ENABLED : VACOPT_TERNARY_DISABLED; } + +/* Get tablespace OID with vacuum-specific checks */ +static Oid +vacuum_get_tablespace_oid(VacuumParams *params, const char *tablespacename) +{ + Oid ret; + + if (tablespacename == NULL) + return InvalidOid; + + if ((params->options & VACOPT_FULL) == 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("incompatible TABLESPACE option"), + errdetail("You can only use TABLESPACE with VACUUM FULL."))); + + ret = get_tablespace_oid(tablespacename, false); + + /* Can't move a non-shared relation into pg_global */ + if (ret == GLOBALTABLESPACE_OID) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot move non-shared relation to tablespace \"%s\"", + tablespacename))); + + return ret; +} diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 8acc321faa..6ba069509a 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -2898,6 +2898,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, tab->at_params.is_wraparound = wraparound; tab->at_params.log_min_duration = log_min_duration; tab->at_params.tablespace_oid = InvalidOid; + tab->at_params.index_tablespace_oid = InvalidOid; tab->at_vacuum_cost_limit = vac_cost_limit; tab->at_vacuum_cost_delay = vac_cost_delay; tab->at_relname = NULL; diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h index bc9f881d8c..515e810505 100644 --- a/src/include/commands/cluster.h +++ b/src/include/commands/cluster.h @@ -20,7 +20,7 @@ extern void cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel); -extern void cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, int options); +extern void cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, Oid indextablespaceOid, int options); extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid, bool recheck, LOCKMODE lockmode); extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal); @@ -34,6 +34,7 @@ extern void finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, bool is_internal, TransactionId frozenXid, MultiXactId minMulti, - char newrelpersistence); + char newrelpersistence, + Oid idxtablespaceOid); #endif /* CLUSTER_H */ diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 6758e9812f..f432648e15 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -229,8 +229,10 @@ typedef struct VacuumParams * disabled. */ int nworkers; - Oid tablespace_oid; /* tablespace Oid to use for relations - * after VACUUM FULL */ + + /* tablespace Oids to use for relations rebuilt by VACUUM FULL */ + Oid tablespace_oid; + Oid index_tablespace_oid; } VacuumParams; /* GUC parameters */ diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source index 7c70f6dc4c..6942775bfb 100644 --- a/src/test/regress/input/tablespace.source +++ b/src/test/regress/input/tablespace.source @@ -47,9 +47,9 @@ WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspa ORDER BY relname; -- check CLUSTER with TABLESPACE change -CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok -CLUSTER pg_authid USING pg_authid_rolname_index TABLESPACE regress_tblspace; -- fail -CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail +CLUSTER (TABLESPACE regress_tblspace) regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx; -- ok +CLUSTER (TABLESPACE regress_tblspace) pg_authid USING pg_authid_rolname_index; -- fail +CLUSTER (TABLESPACE pg_global) regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx; -- fail -- check that all relations moved to new tablespace SELECT relname FROM pg_class diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source index c6dcee3985..2825984394 100644 --- a/src/test/regress/output/tablespace.source +++ b/src/test/regress/output/tablespace.source @@ -60,10 +60,10 @@ ORDER BY relname; (1 row) -- check CLUSTER with TABLESPACE change -CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok -CLUSTER pg_authid USING pg_authid_rolname_index TABLESPACE regress_tblspace; -- fail +CLUSTER (TABLESPACE regress_tblspace) regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx; -- ok +CLUSTER (TABLESPACE regress_tblspace) pg_authid USING pg_authid_rolname_index; -- fail ERROR: cannot cluster a shared catalog -CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail +CLUSTER (TABLESPACE pg_global) regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx; -- fail ERROR: cannot move non-shared relation to tablespace "pg_global" -- check that all relations moved to new tablespace SELECT relname FROM pg_class -- 2.17.0