Hi Hackers,
I would like to propose a change, which allow CLUSTER, VACUUM FULL and
REINDEX to modify relation tablespace on the fly. Actually, all these
commands rebuild relation filenodes from the scratch, thus it seems
natural to allow specifying them a new location. It may be helpful, when
a server went out of disk, so you can attach new partition and perform
e.g. VACUUM FULL, which will free some space and move data to a new
location at the same time. Otherwise, you cannot complete VACUUM FULL
until you have up to x2 relation disk space on a single partition.
Please, find attached a patch, which extend CLUSTER, VACUUM FULL and
REINDEX with additional options:
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE } name [ SET TABLESPACE
new_tablespace ]
CLUSTER [VERBOSE] table_name [ USING index_name ] [ SET TABLESPACE
new_tablespace ]
CLUSTER [VERBOSE] [ SET TABLESPACE new_tablespace ]
VACUUM ( FULL [, ...] ) [ SET TABLESPACE new_tablespace ] [
table_and_columns [, ...] ]
VACUUM FULL [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ SET TABLESPACE
new_tablespace ] [ table_and_columns [, ...] ]
Thereby I have a few questions:
1) What do you think about this concept in general?
2) Is SET TABLESPACE an appropriate syntax for this functionality? I
thought also about a plain TABLESPACE keyword, but it seems to be
misleading, and WITH (options) clause like in CREATE SUBSCRIPTION ...
WITH (options). So I preferred SET TABLESPACE, since the same syntax is
used currently in ALTER to change tablespace, but maybe someone will
have a better idea.
3) I was not able to update the lexer for VACUUM FULL to use SET
TABLESPACE after table_and_columns and completely get rid of
shift/reduce conflicts. I guess it happens, since table_and_columns is
optional and may be of variable length, but have no idea how to deal
with it. Any thoughts?
Regards
--
Alexey Kondratov
Postgres Professionalhttps://www.postgrespro.com
Russian Postgres Company
>From 0d971ce85f62baca7f6f713fa75a1bc20e09b3a2 Mon Sep 17 00:00:00 2001
From: Alexey Kondratov <alex.lu...@gmail.com>
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
<refsynopsisdiv>
<synopsis>
-CLUSTER [VERBOSE] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">index_name</replaceable> ]
-CLUSTER [VERBOSE]
+CLUSTER [VERBOSE] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">index_name</replaceable> ] [ SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ]
+CLUSTER [VERBOSE] [ SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ]
</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/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
<refsynopsisdiv>
<synopsis>
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } <replaceable class="parameter">name</replaceable>
+REINDEX [ ( VERBOSE ) ] { INDEX | TABLE } <replaceable class="parameter">name</replaceable> [ SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ]
</synopsis>
</refsynopsisdiv>
@@ -151,6 +152,15 @@ REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } <replacea
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">new_tablespace</replaceable></term>
+ <listitem>
+ <para>
+ The name of the specific tablespace to store rebuilt indexes.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>VERBOSE</literal></term>
<listitem>
diff --git a/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
<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 [, ...] ) [ SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
+VACUUM FULL [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ SET 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>
@@ -202,6 +204,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/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 <list> constraints_set_list
%type <boolean> constraints_set_mode
-%type <str> OptTableSpace OptConsTableSpace
+%type <str> OptTableSpace OptConsTableSpace opt_set_tablespace_name
%type <rolespec> OptTableSpaceOwner
%type <ival> 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 <name>
+ * REINDEX [ (options) ] type <name> [ SET TABLESPACE <tablespace_name> ]
*****************************************************************************/
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] <qualified_name> [ USING <index_name> ]
- * CLUSTER [VERBOSE]
+ * CLUSTER [VERBOSE] <qualified_name> [ USING <index_name> ] [ SET TABLESPACE <tablespace_name> ]
+ * CLUSTER [VERBOSE] [ SET 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 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 <tablespace_name> ] [ <table_and_columns> [, ...] ]
+ * VACUUM (FULL) [ SET TABLESPACE <tablespace_name> ] [ <table_and_columns> [, ...] ]
* 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
