public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexey Kondratov <[email protected]>
Subject: [PATCH v10 2/5] Allow CLUSTER and VACUUM FULL to change tablespace.
Date: Fri, 21 Dec 2018 14:54:10 +0300
---
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
<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 the 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..e688edb06d 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -22,7 +22,8 @@ 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>
@@ -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 the specific tablespace to write 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 9e5b28903a..0e13c5192e 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 30ac18c0ce..ed8e405618 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -12861,30 +12861,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 */
@@ -13481,6 +13458,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 d625d17bf4..cfa64b80fd 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] <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;
@@ -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 <tablespace_name> ] [ <table_and_columns> [, ...] ]
+ * VACUUM (FULL) [ TABLESPACE <tablespace_name> ] [ <table_and_columns> [, ...] ]
* 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 c3a906b387..0eb9f99120 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 c27d255d8d..8d9e862cd6 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
--17pEHd4RhPHOinZp
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v10-0003-Capitalize-consistently.patch"
view thread (59+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v10 2/5] Allow CLUSTER and VACUUM FULL to change tablespace.
In-Reply-To: <no-message-id-1881679@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox