From d6cc6dedfc2a16df896768295b8399edff9196b7 Mon Sep 17 00:00:00 2001 From: Alexey Kondratov Date: Fri, 21 Dec 2018 14:54:10 +0300 Subject: [PATCH v11 2/5] Allow CLUSTER and VACUUM FULL to change tablespace. --- doc/src/sgml/ref/cluster.sgml | 11 ++++- doc/src/sgml/ref/vacuum.sgml | 13 +++++- src/backend/commands/cluster.c | 28 +++++++---- src/backend/commands/tablecmds.c | 57 +++++++++++++---------- src/backend/commands/vacuum.c | 39 ++++++++++++++-- src/backend/parser/gram.y | 42 +++++++++++++++-- src/include/commands/cluster.h | 2 +- src/include/commands/defrem.h | 18 +++---- src/include/commands/tablecmds.h | 2 + src/include/commands/vacuum.h | 2 + src/include/nodes/parsenodes.h | 2 + src/test/regress/input/tablespace.source | 27 ++++++++--- src/test/regress/output/tablespace.source | 43 ++++++++++++----- 13 files changed, 219 insertions(+), 67 deletions(-) diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml index 4da60d8d56..ebf23b8434 100644 --- a/doc/src/sgml/ref/cluster.sgml +++ b/doc/src/sgml/ref/cluster.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CLUSTER [VERBOSE] table_name [ USING index_name ] +CLUSTER [VERBOSE] table_name [ USING index_name ] [ TABLESPACE new_tablespace ] CLUSTER [VERBOSE] @@ -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/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 846056a353..e688edb06d 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -22,7 +22,8 @@ PostgreSQL documentation VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ] -VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ] +VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ TABLESPACE new_tablespace ] [ table_and_columns [, ...] ] +VACUUM ( FULL [, ...] ) [ TABLESPACE new_tablespace ] [ table_and_columns [, ...] ] where option can be one of: @@ -299,6 +300,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/commands/cluster.c b/src/backend/commands/cluster.c index afc752e9d6..e916eba52f 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -37,6 +37,7 @@ #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,10 +68,10 @@ 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); + bool verbose, bool *pSwapToastByContent, + TransactionId *pFreezeXid, MultiXactId *pCutoffMulti); static List *get_tables_to_cluster(MemoryContext cluster_context); @@ -101,6 +102,13 @@ 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); + if (stmt->relation != NULL) { /* This is the single-relation case. */ @@ -182,7 +190,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 +238,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 +270,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); @@ -425,7 +433,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 +592,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 +603,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); diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index d1f954265c..a1bc0a9bb8 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -12905,30 +12905,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 */ @@ -13525,6 +13502,38 @@ CreateInheritance(Relation child_rel, Relation parent_rel) table_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 59731d687f..7b20314b98 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -31,6 +31,7 @@ #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" @@ -38,6 +39,7 @@ #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" @@ -107,6 +109,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) bool parallel_option = false; ListCell *lc; + Oid tableSpaceOid = InvalidOid; /* Oid of tablespace to use for relations + * store after VACUUM FULL. */ /* Set default value */ params.index_cleanup = VACOPT_TERNARY_DEFAULT; params.truncate = VACOPT_TERNARY_DEFAULT; @@ -241,6 +245,18 @@ 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); + 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; @@ -1672,8 +1688,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, + new_tablespaceoid = InvalidOid; int save_sec_context; int save_nestlevel; @@ -1807,6 +1824,22 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) return true; } + /* + * We cannot support moving system relations into different tablespaces. + */ + if (params->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 @@ -1876,7 +1909,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, new_tablespaceoid, cluster_options); } else table_relation_vacuum(onerel, params, vac_strategy); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index df8f63f989..1f388017be 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -10589,14 +10589,14 @@ CreateConversionStmt: /***************************************************************************** * * QUERY: - * CLUSTER [VERBOSE] [ USING ] - * CLUSTER [VERBOSE] + * CLUSTER [VERBOSE] [ USING ] [ TABLESPACE ] + * CLUSTER [VERBOSE] [ 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 OptTableSpace { ClusterStmt *n = makeNode(ClusterStmt); n->relation = $3; @@ -10604,6 +10604,7 @@ ClusterStmt: n->options = 0; if ($2) n->options |= CLUOPT_VERBOSE; + n->tablespacename = $5; $$ = (Node*)n; } | CLUSTER opt_verbose @@ -10625,6 +10626,7 @@ ClusterStmt: n->options = 0; if ($2) n->options |= CLUOPT_VERBOSE; + n->tablespacename = NULL; $$ = (Node*)n; } ; @@ -10639,6 +10641,8 @@ cluster_index_specification: * * QUERY: * VACUUM + * VACUUM FULL [ TABLESPACE ] [ [, ...] ] + * VACUUM (FULL) [ TABLESPACE ] [ [, ...] ] * ANALYZE * *****************************************************************************/ @@ -10661,6 +10665,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 @@ -10669,6 +10695,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; } ; diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h index e05884781b..ab7a0ad642 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 6c4f0996fa..bb5c359922 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -25,15 +25,15 @@ extern void RemoveObjects(DropStmt *stmt); /* commands/indexcmds.c */ extern ObjectAddress DefineIndex(Oid relationId, - IndexStmt *stmt, - Oid indexRelationId, - Oid parentIndexId, - Oid parentConstraintId, - bool is_alter_table, - bool check_rights, - bool check_not_in_use, - bool skip_build, - bool quiet); + IndexStmt *stmt, + Oid indexRelationId, + Oid parentIndexId, + Oid parentConstraintId, + bool is_alter_table, + bool check_rights, + 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, diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h index c1581ad178..21f9cef535 100644 --- a/src/include/commands/tablecmds.h +++ b/src/include/commands/tablecmds.h @@ -55,6 +55,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 2779bea5c9..55003488ec 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 store relations + * after VACUUM FULL */ } VacuumParams; /* GUC parameters */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 5075a79bd3..5fb0f72141 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3202,6 +3202,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; @@ -3218,6 +3219,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 7d698d4294..8ed97afb9c 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 @@ -45,23 +45,38 @@ WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspa 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 SCHEMA pg_catalog TABLESPACE regress_tblspace; -- fail +REINDEX DATABASE postgres TABLESPACE regress_tblspace; -- fail +REINDEX SYSTEM postgres TABLESPACE regress_tblspace; -- fail REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail -REINDEX TABLE pg_am TABLESPACE regress_tblspace; -- ok + +REINDEX SCHEMA pg_catalog TABLESPACE regress_tblspace; -- fail +REINDEX DATABASE postgres TABLESPACE regress_tblspace; -- fail +REINDEX SYSTEM postgres TABLESPACE regress_tblspace; -- fail + +-- 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 + +-- check VACUUM with TABLESPACE change +VACUUM (FULL) TABLESPACE regress_tblspace pg_authid; -- skip with warning +VACUUM (ANALYSE) 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') ORDER BY relname; -- move back to pg_default tablespace REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl TABLESPACE pg_default; -- ok -REINDEX TABLE pg_am TABLESPACE pg_default; -- ok +CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE pg_default; -- ok +VACUUM (FULL, ANALYSE, FREEZE) 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'); +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; diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source index bd17feaa73..5634cf20ff 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 @@ -33,6 +33,7 @@ REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; ROLLBACK; BEGIN; REINDEX TABLE pg_am TABLESPACE regress_tblspace; +ERROR: cannot move system relation "pg_am_name_index" ROLLBACK; SELECT relname FROM pg_class WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace'); @@ -56,30 +57,50 @@ REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; -- ok REINDEX TABLE pg_authid TABLESPACE regress_tblspace; -- fail ERROR: cannot move system relation "pg_authid_rolname_index" -REINDEX SYSTEM CONCURRENTLY postgres TABLESPACE regress_tblspace; -- fail -ERROR: cannot reindex system catalogs concurrently -REINDEX TABLE CONCURRENTLY pg_am TABLESPACE regress_tblspace; -- fail -ERROR: cannot reindex system catalogs concurrently +REINDEX SCHEMA pg_catalog TABLESPACE regress_tblspace; -- fail +WARNING: cannot change tablespace of indexes for mapped relations, skipping all +REINDEX DATABASE postgres TABLESPACE regress_tblspace; -- fail +ERROR: can only reindex the currently open database +REINDEX SYSTEM postgres TABLESPACE regress_tblspace; -- fail +ERROR: can only reindex the currently open database REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail ERROR: cannot move non-shared relation to tablespace "pg_global" -REINDEX TABLE pg_am TABLESPACE regress_tblspace; -- ok +REINDEX SCHEMA pg_catalog TABLESPACE regress_tblspace; -- fail +WARNING: cannot change tablespace of indexes for mapped relations, skipping all +REINDEX DATABASE postgres TABLESPACE regress_tblspace; -- fail +ERROR: can only reindex the currently open database +REINDEX SYSTEM postgres TABLESPACE regress_tblspace; -- fail +ERROR: can only reindex the currently open database +-- 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 +-- check VACUUM with TABLESPACE change +VACUUM (FULL) 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) 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') ORDER BY relname; relname ------------------------------- - pg_am_name_index - pg_am_oid_index + regress_tblspace_test_tbl regress_tblspace_test_tbl_idx -(3 rows) +(2 rows) -- move back to pg_default tablespace REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl TABLESPACE pg_default; -- ok -REINDEX TABLE pg_am TABLESPACE pg_default; -- ok +CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE pg_default; -- ok +VACUUM (FULL, ANALYSE, FREEZE) 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'); +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) -- 2.17.0