From 0d971ce85f62baca7f6f713fa75a1bc20e09b3a2 Mon Sep 17 00:00:00 2001 From: Alexey Kondratov Date: Fri, 21 Dec 2018 14:54:10 +0300 Subject: [PATCH] Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace. --- doc/src/sgml/ref/cluster.sgml | 13 ++- doc/src/sgml/ref/reindex.sgml | 10 ++ doc/src/sgml/ref/vacuum.sgml | 12 ++ src/backend/catalog/index.c | 128 ++++++++++++++++++---- src/backend/commands/cluster.c | 26 +++-- src/backend/commands/indexcmds.c | 23 +++- src/backend/commands/tablecmds.c | 59 +++++----- src/backend/commands/vacuum.c | 39 ++++++- src/backend/parser/gram.y | 62 +++++++++-- src/backend/tcop/utility.c | 16 ++- src/include/catalog/index.h | 4 +- src/include/commands/cluster.h | 2 +- src/include/commands/defrem.h | 6 +- src/include/commands/tablecmds.h | 2 + src/include/commands/vacuum.h | 2 + src/include/nodes/parsenodes.h | 3 + src/test/regress/input/tablespace.source | 43 ++++++++ src/test/regress/output/tablespace.source | 57 ++++++++++ 18 files changed, 424 insertions(+), 83 deletions(-) diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml index 4da60d8d56..6e61587809 100644 --- a/doc/src/sgml/ref/cluster.sgml +++ b/doc/src/sgml/ref/cluster.sgml @@ -21,8 +21,8 @@ PostgreSQL documentation -CLUSTER [VERBOSE] table_name [ USING index_name ] -CLUSTER [VERBOSE] +CLUSTER [VERBOSE] table_name [ USING index_name ] [ SET TABLESPACE new_tablespace ] +CLUSTER [VERBOSE] [ SET TABLESPACE new_tablespace ] @@ -99,6 +99,15 @@ CLUSTER [VERBOSE] + + new_tablespace + + + The name of the specific tablespace to store clustered relations. + + + + VERBOSE diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index 47cef987d4..661820c1e2 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -22,6 +22,7 @@ PostgreSQL documentation REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name +REINDEX [ ( VERBOSE ) ] { INDEX | TABLE } name [ SET TABLESPACE new_tablespace ] @@ -151,6 +152,15 @@ REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } + + new_tablespace + + + The name of the specific tablespace to store rebuilt indexes. + + + + VERBOSE diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index fd911f5776..b4e3c59e1f 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -23,6 +23,8 @@ PostgreSQL documentation VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ] +VACUUM ( FULL [, ...] ) [ SET TABLESPACE new_tablespace ] [ table_and_columns [, ...] ] +VACUUM FULL [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ SET TABLESPACE new_tablespace ] [ table_and_columns [, ...] ] where option can be one of: @@ -202,6 +204,16 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ new_tablespace + + + The name of the specific tablespace to write new copy of the table. + + + + diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 8709e8c22c..6ebd9c5147 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -3624,15 +3624,21 @@ IndexGetRelation(Oid indexId, bool missing_ok) * reindex_index - This routine is used to recreate a single index */ void -reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, +reindex_index(Oid indexId, Oid tablespaceOid, bool skip_constraint_checks, char persistence, int options) { Relation iRel, - heapRelation; - Oid heapId; + heapRelation, + pg_class; + Oid heapId, + newIndexRelfilenodeOid = InvalidOid; IndexInfo *indexInfo; volatile bool skipped_constraint = false; PGRUsage ru0; + RelFileNode newrnode; + SMgrRelation dstrel; + HeapTuple tuple; + Form_pg_class rd_rel; pg_rusage_init(&ru0); @@ -3657,21 +3663,96 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, elog(ERROR, "unsupported relation kind for index \"%s\"", RelationGetRelationName(iRel)); - /* - * Don't allow reindex on temp tables of other backends ... their local - * buffer manager is not going to cope. - */ - if (RELATION_IS_OTHER_TEMP(iRel)) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot reindex temporary tables of other sessions"))); - /* * Also check for active uses of the index in the current transaction; we * don't want to reindex underneath an open indexscan. */ CheckTableNotInUse(iRel, "REINDEX INDEX"); + if (OidIsValid(tablespaceOid)) + { + /* Check that relocation is possible during reindex. */ + check_relation_is_movable(iRel, tablespaceOid); + + pg_class = heap_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); + + /* + * Allocate an OID for the index, unless we were told what to use. + * + * The OID will be the relfilenode as well, so make sure it doesn't + * collide with either pg_class OIDs or existing physical files. + */ + + /* Use binary-upgrade override for pg_class.oid/relfilenode? */ + if (IsBinaryUpgrade) + { + if (!OidIsValid(binary_upgrade_next_index_pg_class_oid)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("pg_class index OID value not set when in binary upgrade mode"))); + + newIndexRelfilenodeOid = binary_upgrade_next_index_pg_class_oid; + binary_upgrade_next_index_pg_class_oid = InvalidOid; + } + else + { + newIndexRelfilenodeOid = + GetNewRelFileNode(tablespaceOid, pg_class, heapRelation->rd_rel->relpersistence); + } + + /* Open old and new relation */ + newrnode = iRel->rd_node; + newrnode.relNode = newIndexRelfilenodeOid; + newrnode.spcNode = tablespaceOid; + dstrel = smgropen(newrnode, iRel->rd_backend); + + RelationOpenSmgr(iRel); + + /* + * Create and copy all forks of the relation, and schedule unlinking of + * old physical files. + * + * NOTE: any conflict in relfilenode value will be caught in + * RelationCreateStorage(). + */ + RelationCreateStorage(newrnode, iRel->rd_rel->relpersistence); + + /* Drop old relation, and close new one */ + RelationDropStorage(iRel); + smgrclose(dstrel); + + /* Update the pg_class row */ + rd_rel->reltablespace = tablespaceOid; + rd_rel->relfilenode = newIndexRelfilenodeOid; + CatalogTupleUpdate(pg_class, &tuple->t_self, tuple); + + InvokeObjectPostAlterHook(RelationRelationId, RelationGetRelid(iRel), 0); + + heap_freetuple(tuple); + + heap_close(pg_class, RowExclusiveLock); + + /* Make the updated catalog row versions visible */ + CommandCounterIncrement(); + } + else + { + /* + * Don't allow reindex on temp tables of other backends ... their local + * buffer manager is not going to cope. Check only if TABLESPACE is not + * passed, since the same validation exists in the check_relation_is_movable. + */ + if (RELATION_IS_OTHER_TEMP(iRel)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot reindex temporary tables of other sessions"))); + } + /* * All predicate locks on the index are about to be made invalid. Promote * them to relation locks on the heap. @@ -3697,9 +3778,12 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, indexInfo->ii_ExclusionStrats = NULL; } - /* We'll build a new physical relation for the index */ - RelationSetNewRelfilenode(iRel, persistence, InvalidTransactionId, - InvalidMultiXactId); + if (!OidIsValid(newIndexRelfilenodeOid)) + { + /* We'll build a new physical relation for the index */ + RelationSetNewRelfilenode(iRel, persistence, InvalidTransactionId, + InvalidMultiXactId); + } /* Initialize the index and rebuild */ /* Note: we do not need to re-establish pkey setting */ @@ -3758,14 +3842,14 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, pg_index = heap_open(IndexRelationId, RowExclusiveLock); indexTuple = SearchSysCacheCopy1(INDEXRELID, - ObjectIdGetDatum(indexId)); + ObjectIdGetDatum(indexId)); if (!HeapTupleIsValid(indexTuple)) elog(ERROR, "cache lookup failed for index %u", indexId); indexForm = (Form_pg_index) GETSTRUCT(indexTuple); index_bad = (!indexForm->indisvalid || - !indexForm->indisready || - !indexForm->indislive); + !indexForm->indisready || + !indexForm->indislive); if (index_bad || (indexForm->indcheckxmin && !indexInfo->ii_BrokenHotChain) || early_pruning_enabled) @@ -3797,7 +3881,7 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, ereport(INFO, (errmsg("index \"%s\" was reindexed", get_rel_name(indexId)), - errdetail_internal("%s", + errdetail_internal("%s", pg_rusage_show(&ru0)))); /* Close rels, but keep locks */ @@ -3841,7 +3925,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; @@ -3942,7 +4026,7 @@ reindex_relation(Oid relid, int flags, int options) if (is_pg_class) RelationSetIndexList(rel, doneIndexes); - reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS), + reindex_index(indexOid, tablespaceOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS), persistence, options); CommandCounterIncrement(); @@ -3978,7 +4062,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 610e425a56..b19fa0e43e 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -35,6 +35,7 @@ #include "catalog/toasting.h" #include "commands/cluster.h" #include "commands/tablecmds.h" +#include "commands/tablespace.h" #include "commands/vacuum.h" #include "miscadmin.h" #include "optimizer/planner.h" @@ -67,7 +68,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_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose, bool *pSwapToastByContent, TransactionId *pFreezeXid, MultiXactId *pCutoffMulti); @@ -105,6 +106,13 @@ static void reform_and_rewrite_tuple(HeapTuple tuple, 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); + if (stmt->relation != NULL) { /* This is the single-relation case. */ @@ -186,7 +194,7 @@ cluster(ClusterStmt *stmt, bool isTopLevel) heap_close(rel, NoLock); /* Do the job. */ - cluster_rel(tableOid, indexOid, stmt->options); + cluster_rel(tableOid, indexOid, tableSpaceOid, stmt->options); } else { @@ -234,7 +242,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(); @@ -266,7 +274,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); @@ -412,7 +420,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 heap_close() on OldHeap */ } @@ -569,7 +577,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; @@ -580,6 +588,10 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose) TransactionId frozenXid; MultiXactId cutoffMulti; + /* Use new TeableSpace if passed. */ + if (OidIsValid(NewTableSpaceOid)) + tableSpace = NewTableSpaceOid; + /* Mark the correct index as clustered */ if (OidIsValid(indexOid)) mark_index_clustered(OldHeap, indexOid, true); @@ -1574,7 +1586,7 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, else if (newrelpersistence == RELPERSISTENCE_PERMANENT) reindex_flags |= REINDEX_REL_FORCE_INDEXES_PERMANENT; - reindex_relation(OIDOldHeap, reindex_flags, 0); + reindex_relation(OIDOldHeap, InvalidOid, reindex_flags, 0); /* * If the relation being rebuild is pg_class, swap_relation_files() diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 6c06167fb2..c281b68111 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -2205,10 +2205,11 @@ ChooseIndexColumnNames(List *indexElems) * Recreate a specific index. */ void -ReindexIndex(RangeVar *indexRelation, int options) +ReindexIndex(RangeVar *indexRelation, char *tableSpaceName, int options) { Oid indOid; Oid heapOid = InvalidOid; + Oid tableSpaceOid = InvalidOid; Relation irel; char persistence; @@ -2235,9 +2236,13 @@ ReindexIndex(RangeVar *indexRelation, int options) } persistence = irel->rd_rel->relpersistence; + + if (tableSpaceName) + tableSpaceOid = get_tablespace_oid(tableSpaceName, false); + index_close(irel, NoLock); - reindex_index(indOid, false, persistence, options); + reindex_index(indOid, tableSpaceOid, false, persistence, options); } /* @@ -2305,15 +2310,20 @@ RangeVarCallbackForReindexIndex(const RangeVar *relation, * Recreate all indexes of a table (and of its toast table, if any) */ Oid -ReindexTable(RangeVar *relation, int options) +ReindexTable(RangeVar *relation, char *tableSpaceName, int options) { Oid heapOid; + Oid tableSpaceOid = InvalidOid; /* The lock level used here should match reindex_relation(). */ heapOid = RangeVarGetRelidExtended(relation, ShareLock, 0, RangeVarCallbackOwnsTable, NULL); + if (tableSpaceName) + tableSpaceOid = get_tablespace_oid(tableSpaceName, false); + if (!reindex_relation(heapOid, + tableSpaceOid, REINDEX_REL_PROCESS_TOAST | REINDEX_REL_CHECK_CONSTRAINTS, options)) @@ -2333,10 +2343,11 @@ ReindexTable(RangeVar *relation, int options) * 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 *tableSpaceName, int options) { Oid objectOid; + Oid tableSpaceOid = InvalidOid; Relation relationRelation; HeapScanDesc scan; ScanKeyData scan_keys[1]; @@ -2379,6 +2390,9 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, objectName); } + if (tableSpaceName) + tableSpaceOid = get_tablespace_oid(tableSpaceName, false); + /* * Create a memory context that will survive forced transaction commits we * do below. Since it is a child of PortalContext, it will go away @@ -2485,6 +2499,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, /* functions in indexes may want a snapshot set */ PushActiveSnapshot(GetTransactionSnapshot()); if (reindex_relation(relid, + tableSpaceOid, REINDEX_REL_PROCESS_TOAST | REINDEX_REL_CHECK_CONSTRAINTS, options)) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index ce0c7b3153..307947c777 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -1672,7 +1672,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); @@ -10837,30 +10837,7 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode) return; } - /* - * We cannot support moving mapped relations into different tablespaces. - * (In particular this eliminates all shared catalogs.) - */ - if (RelationIsMapped(rel)) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot move system relation \"%s\"", - RelationGetRelationName(rel)))); - - /* Can't move a non-shared relation into pg_global */ - if (newTableSpace == GLOBALTABLESPACE_OID) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("only shared relations can be placed in pg_global tablespace"))); - - /* - * Don't allow moving temp tables of other backends ... their local buffer - * manager is not going to cope. - */ - if (RELATION_IS_OTHER_TEMP(rel)) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot move temporary tables of other sessions"))); + check_relation_is_movable(rel, newTableSpace); reltoastrelid = rel->rd_rel->reltoastrelid; /* Fetch the list of indexes on toast relation if necessary */ @@ -11516,6 +11493,38 @@ CreateInheritance(Relation child_rel, Relation parent_rel) heap_close(catalogRelation, RowExclusiveLock); } +/* + * Validate that relation can be moved to the specified tablespace. + */ +extern void +check_relation_is_movable(Relation rel, Oid tablespaceOid) +{ + /* + * We cannot support moving mapped relations into different tablespaces. + * (In particular this eliminates all shared catalogs.) + */ + if (RelationIsMapped(rel)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot move system relation \"%s\"", + RelationGetRelationName(rel)))); + + /* Can't move a non-shared relation into pg_global */ + if (tablespaceOid == GLOBALTABLESPACE_OID) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("only shared relations can be placed in pg_global tablespace"))); + + /* + * Don't allow moving temp tables of other backends ... their local buffer + * manager is not going to cope. + */ + if (RELATION_IS_OTHER_TEMP(rel)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot move temporary tables of other sessions"))); +} + /* * Obtain the source-text form of the constraint expression for a check * constraint, given its pg_constraint tuple diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 25b3b0312c..5a5f92d803 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -30,12 +30,14 @@ #include "access/multixact.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 "commands/cluster.h" #include "commands/vacuum.h" +#include "commands/tablespace.h" #include "miscadmin.h" #include "nodes/makefuncs.h" #include "pgstat.h" @@ -87,6 +89,8 @@ void ExecVacuum(VacuumStmt *vacstmt, bool isTopLevel) { VacuumParams params; + Oid tableSpaceOid = InvalidOid; /* Oid of tablespace to use for relations + * store after VACUUM FULL. */ /* sanity checks on options */ Assert(vacstmt->options & (VACOPT_VACUUM | VACOPT_ANALYZE)); @@ -131,6 +135,18 @@ ExecVacuum(VacuumStmt *vacstmt, bool isTopLevel) params.multixact_freeze_table_age = -1; } + /* Get tablespace Oid to use. */ + if (vacstmt->tablespacename) + { + if (vacstmt->options & VACOPT_FULL) + tableSpaceOid = get_tablespace_oid(vacstmt->tablespacename, false); + else + ereport(ERROR, + (errmsg("incompatible SET TABLESPACE option"), + errdetail("You can only use SET TABLESPACE with VACUUM FULL."))); + } + params.tablespace_oid = tableSpaceOid; + /* user-invoked vacuum is never "for wraparound" */ params.is_wraparound = false; @@ -1526,8 +1542,9 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) LOCKMODE lmode; Relation onerel; LockRelId onerelid; - Oid toast_relid; - Oid save_userid; + Oid toast_relid, + save_userid, + new_tablespaceoid = InvalidOid; int save_sec_context; int save_nestlevel; @@ -1659,6 +1676,22 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) return true; } + /* + * We cannot support moving system relations into different tablespaces. + */ + if (options & VACOPT_FULL && OidIsValid(params->tablespace_oid) && IsSystemRelation(onerel)) + { + 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 + { + new_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 @@ -1708,7 +1741,7 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, 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, new_tablespaceoid, cluster_options); } else lazy_vacuum_rel(onerel, options, params, vac_strategy); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 2c2208ffb7..6283bc6867 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -543,7 +543,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type constraints_set_list %type constraints_set_mode -%type OptTableSpace OptConsTableSpace +%type OptTableSpace OptConsTableSpace opt_set_tablespace_name %type OptTableSpaceOwner %type opt_check_option @@ -3938,6 +3938,11 @@ OptTableSpace: TABLESPACE name { $$ = $2; } | /*EMPTY*/ { $$ = NULL; } ; +opt_set_tablespace_name: + SET TABLESPACE name { $$ = $3; } + | /*EMPTY*/ { $$ = NULL; } + ; + OptConsTableSpace: USING INDEX TABLESPACE name { $$ = $4; } | /*EMPTY*/ { $$ = NULL; } ; @@ -8314,44 +8319,48 @@ DropTransformStmt: DROP TRANSFORM opt_if_exists FOR Typename LANGUAGE name opt_d * * QUERY: * - * REINDEX [ (options) ] type + * REINDEX [ (options) ] type [ SET TABLESPACE ] *****************************************************************************/ ReindexStmt: - REINDEX reindex_target_type qualified_name + REINDEX reindex_target_type qualified_name opt_set_tablespace_name { ReindexStmt *n = makeNode(ReindexStmt); n->kind = $2; n->relation = $3; + n->tablespacename = $4; n->name = NULL; n->options = 0; $$ = (Node *)n; } - | REINDEX reindex_target_multitable name + | REINDEX reindex_target_multitable name opt_set_tablespace_name { ReindexStmt *n = makeNode(ReindexStmt); n->kind = $2; n->name = $3; + n->tablespacename = $4; n->relation = NULL; n->options = 0; $$ = (Node *)n; } - | REINDEX '(' reindex_option_list ')' reindex_target_type qualified_name + | REINDEX '(' reindex_option_list ')' reindex_target_type qualified_name opt_set_tablespace_name { ReindexStmt *n = makeNode(ReindexStmt); n->kind = $5; n->relation = $6; n->name = NULL; n->options = $3; + n->tablespacename = $7; $$ = (Node *)n; } - | REINDEX '(' reindex_option_list ')' reindex_target_multitable name + | REINDEX '(' reindex_option_list ')' reindex_target_multitable name opt_set_tablespace_name { ReindexStmt *n = makeNode(ReindexStmt); n->kind = $5; n->name = $6; n->relation = NULL; n->options = $3; + n->tablespacename = $7; $$ = (Node *)n; } ; @@ -10421,14 +10430,14 @@ CreateConversionStmt: /***************************************************************************** * * QUERY: - * CLUSTER [VERBOSE] [ USING ] - * CLUSTER [VERBOSE] + * CLUSTER [VERBOSE] [ USING ] [ SET TABLESPACE ] + * CLUSTER [VERBOSE] [ SET TABLESPACE ] * CLUSTER [VERBOSE] ON (for pre-8.3) * *****************************************************************************/ ClusterStmt: - CLUSTER opt_verbose qualified_name cluster_index_specification + CLUSTER opt_verbose qualified_name cluster_index_specification opt_set_tablespace_name { ClusterStmt *n = makeNode(ClusterStmt); n->relation = $3; @@ -10436,9 +10445,10 @@ ClusterStmt: n->options = 0; if ($2) n->options |= CLUOPT_VERBOSE; + n->tablespacename = $5; $$ = (Node*)n; } - | CLUSTER opt_verbose + | CLUSTER opt_verbose opt_set_tablespace_name { ClusterStmt *n = makeNode(ClusterStmt); n->relation = NULL; @@ -10446,6 +10456,7 @@ ClusterStmt: n->options = 0; if ($2) n->options |= CLUOPT_VERBOSE; + n->tablespacename = $3; $$ = (Node*)n; } /* kept for pre-8.3 compatibility */ @@ -10457,6 +10468,7 @@ ClusterStmt: n->options = 0; if ($2) n->options |= CLUOPT_VERBOSE; + n->tablespacename = NULL; $$ = (Node*)n; } ; @@ -10471,6 +10483,8 @@ cluster_index_specification: * * QUERY: * VACUUM + * VACUUM FULL [ SET TABLESPACE ] [ [, ...] ] + * VACUUM (FULL) [ SET TABLESPACE ] [ [, ...] ] * ANALYZE * *****************************************************************************/ @@ -10488,6 +10502,23 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose opt_analyze opt_vacuum_relati if ($5) n->options |= VACOPT_ANALYZE; n->rels = $6; + n->tablespacename = NULL; + $$ = (Node *)n; + } + | VACUUM opt_full opt_freeze opt_verbose opt_analyze SET TABLESPACE name opt_vacuum_relation_list + { + VacuumStmt *n = makeNode(VacuumStmt); + n->options = VACOPT_VACUUM; + if ($2) + n->options |= VACOPT_FULL; + if ($3) + n->options |= VACOPT_FREEZE; + if ($4) + n->options |= VACOPT_VERBOSE; + if ($5) + n->options |= VACOPT_ANALYZE; + n->tablespacename = $8; + n->rels = $9; $$ = (Node *)n; } | VACUUM '(' vacuum_option_list ')' opt_vacuum_relation_list @@ -10495,6 +10526,15 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose opt_analyze opt_vacuum_relati VacuumStmt *n = makeNode(VacuumStmt); n->options = VACOPT_VACUUM | $3; n->rels = $5; + n->tablespacename = NULL; + $$ = (Node *) n; + } + | VACUUM '(' vacuum_option_list ')' SET TABLESPACE name opt_vacuum_relation_list + { + VacuumStmt *n = makeNode(VacuumStmt); + n->options = VACOPT_VACUUM | $3; + n->tablespacename = $7; + n->rels = $8; $$ = (Node *) n; } ; @@ -10603,7 +10643,7 @@ vacuum_relation_list: ; opt_vacuum_relation_list: - vacuum_relation_list { $$ = $1; } + vacuum_relation_list { $$ = $1; } | /*EMPTY*/ { $$ = NIL; } ; diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 970c94ee80..b8232f63d0 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -780,15 +780,25 @@ standard_ProcessUtility(PlannedStmt *pstmt, switch (stmt->kind) { case REINDEX_OBJECT_INDEX: - ReindexIndex(stmt->relation, stmt->options); + ReindexIndex(stmt->relation, stmt->tablespacename, stmt->options); break; case REINDEX_OBJECT_TABLE: - ReindexTable(stmt->relation, stmt->options); + ReindexTable(stmt->relation, stmt->tablespacename, stmt->options); break; case REINDEX_OBJECT_SCHEMA: case REINDEX_OBJECT_SYSTEM: case REINDEX_OBJECT_DATABASE: + /* + * We cannot move system relations to a new tablespace and + * the entire schema/database very likely will has one, + * so simply reject such cases. + */ + if (stmt->tablespacename) + ereport(ERROR, + (errmsg("incompatible SET TABLESPACE option"), + errdetail("You can only use SET TABLESPACE with REINDEX { INDEX | TABLE }."))); + /* * This cannot run inside a user transaction block; if * we were inside a transaction, then its commit- and @@ -799,7 +809,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); + ReindexMultipleTables(stmt->name, stmt->kind, stmt->tablespacename, stmt->options); break; default: elog(ERROR, "unrecognized object type: %d", diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index 35a29f3498..67e260058b 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -133,7 +133,7 @@ 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, +extern void reindex_index(Oid indexId, Oid tablespaceOid, bool skip_constraint_checks, char relpersistence, int options); /* Flag bits for reindex_relation(): */ @@ -143,7 +143,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/cluster.h b/src/include/commands/cluster.h index f37a60c1c1..3b9b2275bd 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/defrem.h b/src/include/commands/defrem.h index 1d05a4bcdc..e0895003a8 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -34,9 +34,9 @@ extern ObjectAddress DefineIndex(Oid relationId, bool check_not_in_use, bool skip_build, bool quiet); -extern void ReindexIndex(RangeVar *indexRelation, int options); -extern Oid ReindexTable(RangeVar *relation, int options); -extern void ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, +extern void ReindexIndex(RangeVar *indexRelation, char *tableSpaceName, int options); +extern Oid ReindexTable(RangeVar *relation, char *tableSpaceName, int options); +extern void ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, char *tableSpaceName, int options); extern char *makeObjectName(const char *name1, const char *name2, const char *label); diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h index 2afcd5be44..61804d184b 100644 --- a/src/include/commands/tablecmds.h +++ b/src/include/commands/tablecmds.h @@ -52,6 +52,8 @@ extern void AlterRelationNamespaceInternal(Relation classRel, Oid relOid, extern void CheckTableNotInUse(Relation rel, const char *stmt); +extern void check_relation_is_movable(Relation rel, Oid tablespaceOid); + extern void ExecuteTruncate(TruncateStmt *stmt); extern void ExecuteTruncateGuts(List *explicit_rels, List *relids, List *relids_logged, DropBehavior behavior, bool restart_seqs); diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index dfff23ac55..ebdb80d8b1 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -151,6 +151,8 @@ typedef struct VacuumParams int log_min_duration; /* minimum execution threshold in ms at * which verbose logs are activated, -1 * to use default */ + Oid tablespace_oid; /* tablespace Oid to use for store relations + * after VACUUM FULL */ } VacuumParams; /* GUC parameters */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index e5bdc1cec5..7be06be3ef 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3133,6 +3133,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; @@ -3176,6 +3177,7 @@ typedef struct VacuumStmt NodeTag type; int options; /* OR of VacuumOption flags */ List *rels; /* list of VacuumRelation, or NIL for all */ + char *tablespacename; /* tablespace name to use for vacuumed relation. */ } VacuumStmt; /* ---------------------- @@ -3304,6 +3306,7 @@ typedef struct ReindexStmt RangeVar *relation; /* Table or index to reindex */ const char *name; /* name of database to reindex */ int options; /* Reindex options flags */ + char *tablespacename; } ReindexStmt; /* ---------------------- diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source index 47ae73af95..ea1d3ffd04 100644 --- a/src/test/regress/input/tablespace.source +++ b/src/test/regress/input/tablespace.source @@ -17,6 +17,46 @@ 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, 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 + FROM generate_series(1, 20000) s(i); +CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1); +CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx; -- ok + +-- check REINDEX with TABLESPACE change +REINDEX INDEX regress_tblspace_test_tbl_idx SET TABLESPACE regress_tblspace; -- ok +REINDEX TABLE regress_tblspace_test_tbl SET TABLESPACE regress_tblspace; -- ok +REINDEX TABLE pg_authid SET TABLESPACE regress_tblspace; -- fail +REINDEX SCHEMA pg_catalog SET TABLESPACE regress_tblspace; -- fail +REINDEX DATABASE postgres SET TABLESPACE regress_tblspace; -- fail +REINDEX SYSTEM postgres SET TABLESPACE regress_tblspace; -- fail + +-- check CLUSTER with TABLESPACE change +CLUSTER regress_tblspace_test_tbl SET TABLESPACE regress_tblspace; -- ok +CLUSTER pg_authid USING pg_authid_rolname_index SET TABLESPACE regress_tblspace; -- fail + +-- check VACUUM with TABLESPACE change +VACUUM (FULL) SET TABLESPACE regress_tblspace pg_authid; -- skip with warning +VACUUM (ANALYSE) SET 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') +AND relname IN ('regress_tblspace_test_tbl_idx', 'regress_tblspace_test_tbl'); + +-- move back to pg_default tablespace +REINDEX TABLE regress_tblspace_test_tbl SET TABLESPACE pg_default; -- ok +CLUSTER regress_tblspace_test_tbl SET TABLESPACE pg_default; -- ok +CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx SET TABLESPACE regress_tblspace; -- ok +VACUUM (FULL, ANALYSE, FREEZE) SET TABLESPACE pg_default 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') +AND relname IN ('regress_tblspace_test_tbl_idx', 'regress_tblspace_test_tbl'); + -- create a schema we can use CREATE SCHEMA testschema; @@ -157,6 +197,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 29d6d2232b..8d902666fd 100644 --- a/src/test/regress/output/tablespace.source +++ b/src/test/regress/output/tablespace.source @@ -20,6 +20,61 @@ 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, 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 + FROM generate_series(1, 20000) s(i); +CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1); +CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx; -- ok +-- check REINDEX with TABLESPACE change +REINDEX INDEX regress_tblspace_test_tbl_idx SET TABLESPACE regress_tblspace; -- ok +REINDEX TABLE regress_tblspace_test_tbl SET TABLESPACE regress_tblspace; -- ok +REINDEX TABLE pg_authid SET TABLESPACE regress_tblspace; -- fail +ERROR: cannot move system relation "pg_authid_rolname_index" +REINDEX SCHEMA pg_catalog SET TABLESPACE regress_tblspace; -- fail +ERROR: incompatible SET TABLESPACE option +DETAIL: You can only use SET TABLESPACE with REINDEX { INDEX | TABLE }. +REINDEX DATABASE postgres SET TABLESPACE regress_tblspace; -- fail +ERROR: incompatible SET TABLESPACE option +DETAIL: You can only use SET TABLESPACE with REINDEX { INDEX | TABLE }. +REINDEX SYSTEM postgres SET TABLESPACE regress_tblspace; -- fail +ERROR: incompatible SET TABLESPACE option +DETAIL: You can only use SET TABLESPACE with REINDEX { INDEX | TABLE }. +-- check CLUSTER with TABLESPACE change +CLUSTER regress_tblspace_test_tbl SET TABLESPACE regress_tblspace; -- ok +CLUSTER pg_authid USING pg_authid_rolname_index SET TABLESPACE regress_tblspace; -- fail +ERROR: cannot cluster a shared catalog +-- check VACUUM with TABLESPACE change +VACUUM (FULL) SET TABLESPACE regress_tblspace pg_authid; -- skip with warning +WARNING: skipping tablespace change of "pg_authid" +DETAIL: Cannot move system relation, only VACUUM is performed. +VACUUM (ANALYSE) SET TABLESPACE regress_tblspace; -- fail +ERROR: incompatible SET TABLESPACE option +DETAIL: You can only use SET TABLESPACE with VACUUM FULL. +-- check that all relations moved to new tablespace +SELECT relname FROM pg_class +WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace') +AND relname IN ('regress_tblspace_test_tbl_idx', 'regress_tblspace_test_tbl'); + relname +------------------------------- + regress_tblspace_test_tbl + regress_tblspace_test_tbl_idx +(2 rows) + +-- move back to pg_default tablespace +REINDEX TABLE regress_tblspace_test_tbl SET TABLESPACE pg_default; -- ok +CLUSTER regress_tblspace_test_tbl SET TABLESPACE pg_default; -- ok +CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx SET TABLESPACE regress_tblspace; -- ok +VACUUM (FULL, ANALYSE, FREEZE) SET TABLESPACE pg_default 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') +AND relname IN ('regress_tblspace_test_tbl_idx', 'regress_tblspace_test_tbl'); + relname +--------- +(0 rows) + -- create a schema we can use CREATE SCHEMA testschema; -- try a table @@ -277,6 +332,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.1