On Wed, Apr 01, 2020 at 06:57:18AM -0500, Justin Pryzby wrote:
> Alexey suggested that those changes should be done as a separate patch, with
> the tablespace options built on top. Which makes sense. I had quite some fun
> rebasing these with patches in that order.
>
> However, I've kept my changes separate from Alexey's patch, to make it easier
> for him to integrate. So there's "fix!" commits which are not logically
> separate and should be read as if they're merged with their parent commits.
> That makes the patchset look kind of dirty. So I'm first going to send the
> "before rebase" patchset. There's a few fixme items, but I think this is in
> pretty good shape, and I'd appreciate review.
>
> I'll follow up later with the "after rebase" patchset.
Attached. As I said, the v15 patches might be easier to review, even though
v16 is closer to what's desirable to merge.
> Maybe Alexey will want to integrate that.
Or maybe you'd want me to squish my changes into yours and resend after any
review comments ?
--
Justin
>From cbb8e856e0913f1a94d28d7164adabad1362d58c Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Fri, 27 Mar 2020 17:50:46 -0500
Subject: [PATCH v16 1/7] Change REINDEX/CLUSTER to accept an option list..
..like reindex (CONCURRENTLY)
Change docs in the style of VACUUM. See also: 52dcfda48778d16683c64ca4372299a099a15b96
---
doc/src/sgml/ref/cluster.sgml | 13 ++++----
doc/src/sgml/ref/reindex.sgml | 26 +++++++--------
src/backend/commands/cluster.c | 21 ++++++++++++-
src/backend/commands/indexcmds.c | 41 +++++++++++++-----------
src/backend/nodes/copyfuncs.c | 2 ++
src/backend/nodes/equalfuncs.c | 2 ++
src/backend/parser/gram.y | 54 +++++++++++++++++++++++++++-----
src/backend/tcop/utility.c | 33 ++++++++++++++++---
src/include/commands/cluster.h | 3 +-
src/include/commands/defrem.h | 7 ++---
src/include/nodes/parsenodes.h | 4 ++-
11 files changed, 149 insertions(+), 57 deletions(-)
diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml
index 4da60d8d56..bd0682ddfd 100644
--- a/doc/src/sgml/ref/cluster.sgml
+++ b/doc/src/sgml/ref/cluster.sgml
@@ -82,31 +82,32 @@ CLUSTER [VERBOSE]
<variablelist>
<varlistentry>
- <term><replaceable class="parameter">table_name</replaceable></term>
+ <term><literal>VERBOSE</literal></term>
<listitem>
<para>
- The name (possibly schema-qualified) of a table.
+ Prints a progress report as each table is clustered.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><replaceable class="parameter">index_name</replaceable></term>
+ <term><replaceable class="parameter">table_name</replaceable></term>
<listitem>
<para>
- The name of an index.
+ The name (possibly schema-qualified) of a table.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>VERBOSE</literal></term>
+ <term><replaceable class="parameter">index_name</replaceable></term>
<listitem>
<para>
- Prints a progress report as each table is clustered.
+ The name of an index.
</para>
</listitem>
</varlistentry>
+
</variablelist>
</refsect1>
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index c54a7c420d..200526b6f4 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -141,19 +141,6 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
</listitem>
</varlistentry>
- <varlistentry>
- <term><replaceable class="parameter">name</replaceable></term>
- <listitem>
- <para>
- The name of the specific index, table, or database to be
- reindexed. Index and table names can be schema-qualified.
- Presently, <command>REINDEX DATABASE</command> and <command>REINDEX SYSTEM</command>
- can only reindex the current database, so their parameter must match
- the current database's name.
- </para>
- </listitem>
- </varlistentry>
-
<varlistentry>
<term><literal>CONCURRENTLY</literal></term>
<listitem>
@@ -182,6 +169,19 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name of the specific index, table, or database to be
+ reindexed. Index and table names can be schema-qualified.
+ Presently, <command>REINDEX DATABASE</command> and <command>REINDEX SYSTEM</command>
+ can only reindex the current database, so their parameter must match
+ the current database's name.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</refsect1>
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index fc1cea0236..6c3fdd3874 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -35,6 +35,7 @@
#include "catalog/pg_am.h"
#include "catalog/toasting.h"
#include "commands/cluster.h"
+#include "commands/defrem.h"
#include "commands/progress.h"
#include "commands/tablecmds.h"
#include "commands/vacuum.h"
@@ -99,8 +100,26 @@ static List *get_tables_to_cluster(MemoryContext cluster_context);
*---------------------------------------------------------------------------
*/
void
-cluster(ClusterStmt *stmt, bool isTopLevel)
+cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
{
+ ListCell *lc;
+
+ /* Parse list of generic parameter not handled by the parser */
+ foreach(lc, stmt->params)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ if (strcmp(opt->defname, "verbose") == 0)
+ stmt->options |= CLUOPT_VERBOSE;
+ // XXX: handle boolean opt: VERBOSE off
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unrecognized CLUSTER option \"%s\"",
+ opt->defname),
+ parser_errposition(pstate, opt->location)));
+ }
+
if (stmt->relation != NULL)
{
/* This is the single-relation case. */
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 2e5997b5c3..e0f87f2dbf 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -2366,8 +2366,9 @@ ChooseIndexColumnNames(List *indexElems)
* Recreate a specific index.
*/
void
-ReindexIndex(RangeVar *indexRelation, int options, bool concurrent)
+ReindexIndex(ReindexStmt *stmt)
{
+ RangeVar *indexRelation = stmt->relation;
struct ReindexIndexCallbackState state;
Oid indOid;
Relation irel;
@@ -2383,10 +2384,10 @@ ReindexIndex(RangeVar *indexRelation, int options, bool concurrent)
* upgrade the lock, but that's OK, because other sessions can't hold
* locks on our temporary table.
*/
- state.concurrent = concurrent;
+ state.concurrent = stmt->concurrent;
state.locked_table_oid = InvalidOid;
indOid = RangeVarGetRelidExtended(indexRelation,
- concurrent ? ShareUpdateExclusiveLock : AccessExclusiveLock,
+ stmt->concurrent ? ShareUpdateExclusiveLock : AccessExclusiveLock,
0,
RangeVarCallbackForReindexIndex,
&state);
@@ -2406,11 +2407,11 @@ ReindexIndex(RangeVar *indexRelation, int options, bool concurrent)
persistence = irel->rd_rel->relpersistence;
index_close(irel, NoLock);
- if (concurrent && persistence != RELPERSISTENCE_TEMP)
- ReindexRelationConcurrently(indOid, options);
+ if (stmt->concurrent && persistence != RELPERSISTENCE_TEMP)
+ ReindexRelationConcurrently(indOid, stmt->options);
else
reindex_index(indOid, false, persistence,
- options | REINDEXOPT_REPORT_PROGRESS);
+ stmt->options | REINDEXOPT_REPORT_PROGRESS);
}
/*
@@ -2488,8 +2489,9 @@ RangeVarCallbackForReindexIndex(const RangeVar *relation,
* Recreate all indexes of a table (and of its toast table, if any)
*/
Oid
-ReindexTable(RangeVar *relation, int options, bool concurrent)
+ReindexTable(ReindexStmt *stmt)
{
+ RangeVar *relation = stmt->relation;
Oid heapOid;
bool result;
@@ -2502,13 +2504,13 @@ ReindexTable(RangeVar *relation, int options, bool concurrent)
* locks on our temporary table.
*/
heapOid = RangeVarGetRelidExtended(relation,
- concurrent ? ShareUpdateExclusiveLock : ShareLock,
+ stmt->concurrent ? ShareUpdateExclusiveLock : ShareLock,
0,
RangeVarCallbackOwnsTable, NULL);
- if (concurrent && get_rel_persistence(heapOid) != RELPERSISTENCE_TEMP)
+ if (stmt->concurrent && get_rel_persistence(heapOid) != RELPERSISTENCE_TEMP)
{
- result = ReindexRelationConcurrently(heapOid, options);
+ result = ReindexRelationConcurrently(heapOid, stmt->options);
if (!result)
ereport(NOTICE,
@@ -2520,7 +2522,7 @@ ReindexTable(RangeVar *relation, int options, bool concurrent)
result = reindex_relation(heapOid,
REINDEX_REL_PROCESS_TOAST |
REINDEX_REL_CHECK_CONSTRAINTS,
- options | REINDEXOPT_REPORT_PROGRESS);
+ stmt->options | REINDEXOPT_REPORT_PROGRESS);
if (!result)
ereport(NOTICE,
(errmsg("table \"%s\" has no indexes to reindex",
@@ -2539,9 +2541,10 @@ ReindexTable(RangeVar *relation, int options, bool concurrent)
* That means this must not be called within a user transaction block!
*/
void
-ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
- int options, bool concurrent)
+ReindexMultipleTables(ReindexStmt *stmt)
{
+ const char *objectName = stmt->name;
+ ReindexObjectType objectKind = stmt->kind;
Oid objectOid;
Relation relationRelation;
TableScanDesc scan;
@@ -2559,7 +2562,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
objectKind == REINDEX_OBJECT_SYSTEM ||
objectKind == REINDEX_OBJECT_DATABASE);
- if (objectKind == REINDEX_OBJECT_SYSTEM && concurrent)
+ if (objectKind == REINDEX_OBJECT_SYSTEM && stmt->concurrent)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot reindex system catalogs concurrently")));
@@ -2670,7 +2673,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
* Skip system tables, since index_create() would reject indexing them
* concurrently (and it would likely fail if we tried).
*/
- if (concurrent &&
+ if (stmt->concurrent &&
IsCatalogRelationOid(relid))
{
if (!concurrent_warning)
@@ -2712,9 +2715,9 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
/* functions in indexes may want a snapshot set */
PushActiveSnapshot(GetTransactionSnapshot());
- if (concurrent && get_rel_persistence(relid) != RELPERSISTENCE_TEMP)
+ if (stmt->concurrent && get_rel_persistence(relid) != RELPERSISTENCE_TEMP)
{
- (void) ReindexRelationConcurrently(relid, options);
+ (void) ReindexRelationConcurrently(relid, stmt->options);
/* ReindexRelationConcurrently() does the verbose output */
}
else
@@ -2724,9 +2727,9 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
result = reindex_relation(relid,
REINDEX_REL_PROCESS_TOAST |
REINDEX_REL_CHECK_CONSTRAINTS,
- options | REINDEXOPT_REPORT_PROGRESS);
+ stmt->options | REINDEXOPT_REPORT_PROGRESS);
- if (result && (options & REINDEXOPT_VERBOSE))
+ if (result && (stmt->options & REINDEXOPT_VERBOSE))
ereport(INFO,
(errmsg("table \"%s.%s\" was reindexed",
get_namespace_name(get_rel_namespace(relid)),
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index c9a90d1191..a4672f1bb8 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3317,6 +3317,7 @@ _copyClusterStmt(const ClusterStmt *from)
COPY_NODE_FIELD(relation);
COPY_STRING_FIELD(indexname);
COPY_SCALAR_FIELD(options);
+ COPY_NODE_FIELD(params);
return newnode;
}
@@ -4405,6 +4406,7 @@ _copyReindexStmt(const ReindexStmt *from)
COPY_SCALAR_FIELD(kind);
COPY_NODE_FIELD(relation);
COPY_STRING_FIELD(name);
+ COPY_NODE_FIELD(rawoptions);
COPY_SCALAR_FIELD(options);
COPY_SCALAR_FIELD(concurrent);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index d05ca26fcf..cb22426dbd 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1215,6 +1215,7 @@ _equalClusterStmt(const ClusterStmt *a, const ClusterStmt *b)
COMPARE_NODE_FIELD(relation);
COMPARE_STRING_FIELD(indexname);
COMPARE_SCALAR_FIELD(options);
+ COMPARE_NODE_FIELD(params);
return true;
}
@@ -2129,6 +2130,7 @@ _equalReindexStmt(const ReindexStmt *a, const ReindexStmt *b)
COMPARE_SCALAR_FIELD(kind);
COMPARE_NODE_FIELD(relation);
COMPARE_STRING_FIELD(name);
+ COMPARE_NODE_FIELD(rawoptions);
COMPARE_SCALAR_FIELD(options);
COMPARE_SCALAR_FIELD(concurrent);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index eb0bf12cd8..f1ec2b4951 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -511,7 +511,10 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> explain_option_list
%type <ival> reindex_target_type reindex_target_multitable
-%type <ival> reindex_option_list reindex_option_elem
+%type <str> reindex_option_name
+%type <node> reindex_option_arg
+%type <list> reindex_option_list
+%type <defelt> reindex_option_elem
%type <node> copy_generic_opt_arg copy_generic_opt_arg_list_item
%type <defelt> copy_generic_opt_elem
@@ -8406,7 +8409,7 @@ ReindexStmt:
n->concurrent = $3;
n->relation = $4;
n->name = NULL;
- n->options = 0;
+ n->rawoptions = NIL;
$$ = (Node *)n;
}
| REINDEX reindex_target_multitable opt_concurrently name
@@ -8416,7 +8419,7 @@ ReindexStmt:
n->concurrent = $3;
n->name = $4;
n->relation = NULL;
- n->options = 0;
+ n->rawoptions = NIL;
$$ = (Node *)n;
}
| REINDEX '(' reindex_option_list ')' reindex_target_type opt_concurrently qualified_name
@@ -8426,7 +8429,7 @@ ReindexStmt:
n->concurrent = $6;
n->relation = $7;
n->name = NULL;
- n->options = $3;
+ n->rawoptions = $3;
$$ = (Node *)n;
}
| REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name
@@ -8436,7 +8439,7 @@ ReindexStmt:
n->concurrent = $6;
n->name = $7;
n->relation = NULL;
- n->options = $3;
+ n->rawoptions = $3;
$$ = (Node *)n;
}
;
@@ -8450,11 +8453,31 @@ reindex_target_multitable:
| DATABASE { $$ = REINDEX_OBJECT_DATABASE; }
;
reindex_option_list:
- reindex_option_elem { $$ = $1; }
- | reindex_option_list ',' reindex_option_elem { $$ = $1 | $3; }
+ reindex_option_elem
+ {
+ $$ = list_make1($1);
+ }
+ | reindex_option_list ',' reindex_option_elem
+ {
+ $$ = lappend($1, $3);
+ }
;
+
reindex_option_elem:
- VERBOSE { $$ = REINDEXOPT_VERBOSE; }
+ reindex_option_name reindex_option_arg
+ {
+ $$ = makeDefElem($1, $2, @1);
+ }
+ ;
+
+reindex_option_name:
+ NonReservedWord { $$ = $1; }
+ ;
+
+reindex_option_arg:
+ opt_boolean_or_string { $$ = (Node *) makeString($1); }
+ | NumericOnly { $$ = (Node *) $1; }
+ | /* EMPTY */ { $$ = NULL; }
;
/*****************************************************************************
@@ -10596,6 +10619,7 @@ CreateConversionStmt:
*
* QUERY:
* CLUSTER [VERBOSE] <qualified_name> [ USING <index_name> ]
+ * CLUSTER [VERBOSE] [(options)] <qualified_name> [ USING <index_name> ]
* CLUSTER [VERBOSE]
* CLUSTER [VERBOSE] <index_name> ON <qualified_name> (for pre-8.3)
*
@@ -10610,6 +10634,18 @@ ClusterStmt:
n->options = 0;
if ($2)
n->options |= CLUOPT_VERBOSE;
+ n->params = NIL;
+ $$ = (Node*)n;
+ }
+/* XXX: reusing reindex_option_list */
+ | CLUSTER opt_verbose '(' reindex_option_list ')' qualified_name cluster_index_specification
+ {
+ ClusterStmt *n = makeNode(ClusterStmt);
+ n->relation = $6;
+ n->indexname = $7;
+ if ($2)
+ n->options |= CLUOPT_VERBOSE;
+ n->params = $4;
$$ = (Node*)n;
}
| CLUSTER opt_verbose
@@ -10620,6 +10656,7 @@ ClusterStmt:
n->options = 0;
if ($2)
n->options |= CLUOPT_VERBOSE;
+ n->params = NIL;
$$ = (Node*)n;
}
/* kept for pre-8.3 compatibility */
@@ -10631,6 +10668,7 @@ ClusterStmt:
n->options = 0;
if ($2)
n->options |= CLUOPT_VERBOSE;
+ n->params = NIL;
$$ = (Node*)n;
}
;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index b1f7f6e2d0..d6674a9e38 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -524,6 +524,30 @@ ProcessUtility(PlannedStmt *pstmt,
dest, qc);
}
+/* Parse rawoptions into options flags and tablespace */
+static
+void parse_reindex_options(ParseState *pstate, ReindexStmt *stmt)
+{
+ ListCell *lc;
+ /* Parse options list. */
+ foreach(lc, stmt->rawoptions)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ if (strcmp(opt->defname, "verbose") == 0)
+ stmt->options |= REINDEXOPT_VERBOSE;
+ // XXX: handle boolean opt: VERBOSE off
+ else if (strcmp(opt->defname, "concurrently") == 0)
+ stmt->concurrent = true;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unrecognized REINDEX option \"%s\"",
+ opt->defname),
+ parser_errposition(pstate, opt->location)));
+ }
+}
+
/*
* standard_ProcessUtility itself deals only with utility commands for
* which we do not provide event trigger support. Commands that do have
@@ -816,7 +840,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
break;
case T_ClusterStmt:
- cluster((ClusterStmt *) parsetree, isTopLevel);
+ cluster(pstate, (ClusterStmt *) parsetree, isTopLevel);
break;
case T_VacuumStmt:
@@ -921,13 +945,14 @@ standard_ProcessUtility(PlannedStmt *pstmt,
PreventInTransactionBlock(isTopLevel,
"REINDEX CONCURRENTLY");
+ parse_reindex_options(pstate, stmt);
switch (stmt->kind)
{
case REINDEX_OBJECT_INDEX:
- ReindexIndex(stmt->relation, stmt->options, stmt->concurrent);
+ ReindexIndex(stmt);
break;
case REINDEX_OBJECT_TABLE:
- ReindexTable(stmt->relation, stmt->options, stmt->concurrent);
+ ReindexTable(stmt);
break;
case REINDEX_OBJECT_SCHEMA:
case REINDEX_OBJECT_SYSTEM:
@@ -943,7 +968,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
(stmt->kind == REINDEX_OBJECT_SCHEMA) ? "REINDEX SCHEMA" :
(stmt->kind == REINDEX_OBJECT_SYSTEM) ? "REINDEX SYSTEM" :
"REINDEX DATABASE");
- ReindexMultipleTables(stmt->name, stmt->kind, stmt->options, stmt->concurrent);
+ ReindexMultipleTables(stmt);
break;
default:
elog(ERROR, "unrecognized object type: %d",
diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h
index e05884781b..674cdcd0cd 100644
--- a/src/include/commands/cluster.h
+++ b/src/include/commands/cluster.h
@@ -14,11 +14,12 @@
#define CLUSTER_H
#include "nodes/parsenodes.h"
+#include "parser/parse_node.h"
#include "storage/lock.h"
#include "utils/relcache.h"
-extern void cluster(ClusterStmt *stmt, bool isTopLevel);
+extern void cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel);
extern void cluster_rel(Oid tableOid, Oid indexOid, int options);
extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid,
bool recheck, LOCKMODE lockmode);
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index c77c9a6ed5..7020edbdbb 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -34,10 +34,9 @@ extern ObjectAddress DefineIndex(Oid relationId,
bool check_not_in_use,
bool skip_build,
bool quiet);
-extern void ReindexIndex(RangeVar *indexRelation, int options, bool concurrent);
-extern Oid ReindexTable(RangeVar *relation, int options, bool concurrent);
-extern void ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
- int options, bool concurrent);
+extern void ReindexIndex(ReindexStmt *stmt);
+extern Oid ReindexTable(ReindexStmt *stmt);
+extern void ReindexMultipleTables(ReindexStmt *stmt);
extern char *makeObjectName(const char *name1, const char *name2,
const char *label);
extern char *ChooseRelationName(const char *name1, const char *name2,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 77943f0637..f72587a584 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3204,6 +3204,7 @@ typedef struct ClusterStmt
RangeVar *relation; /* relation being indexed, or NULL if all */
char *indexname; /* original index defined */
int options; /* OR of ClusterOption flags */
+ List *params; /* Params not further parsed by the grammar */
} ClusterStmt;
/* ----------------------
@@ -3362,7 +3363,8 @@ typedef struct ReindexStmt
* etc. */
RangeVar *relation; /* Table or index to reindex */
const char *name; /* name of database to reindex */
- int options; /* Reindex options flags */
+ List *rawoptions; /* Raw options */
+ int options; /* Parsed options */
bool concurrent; /* reindex concurrently? */
} ReindexStmt;
--
2.17.0
>From 63c314c5e9576f51178a3f9f2a53a5bf06ae3623 Mon Sep 17 00:00:00 2001
From: Alexey Kondratov <kondratov.alek...@gmail.com>
Date: Mon, 23 Mar 2020 21:10:29 +0300
Subject: [PATCH v16 2/7] Allow REINDEX to change tablespace
REINDEX already does full relation rewrite, this patch adds a
possibility to specify a new tablespace where new relfilenode
will be created.
---
doc/src/sgml/ref/reindex.sgml | 24 +++-
src/backend/catalog/index.c | 89 +++++++++++++--
src/backend/commands/cluster.c | 2 +-
src/backend/commands/indexcmds.c | 133 +++++++++++++++++++++-
src/backend/commands/tablecmds.c | 2 +-
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/parser/gram.y | 12 +-
src/bin/psql/tab-complete.c | 6 +
src/include/catalog/index.h | 7 +-
src/include/nodes/parsenodes.h | 1 +
src/test/regress/input/tablespace.source | 39 +++++++
src/test/regress/output/tablespace.source | 50 ++++++++
13 files changed, 343 insertions(+), 24 deletions(-)
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 200526b6f4..f3ceb6f14a 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable>
+REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable> [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ]
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
@@ -161,6 +161,28 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>TABLESPACE</literal></term>
+ <listitem>
+ <para>
+ This specifies a tablespace, where all rebuilt indexes will be created.
+ Cannot be used with "mapped" relations. If <literal>SCHEMA</literal>,
+ <literal>DATABASE</literal> or <literal>SYSTEM</literal> is specified, then
+ all unsuitable relations will be skipped and a single <literal>WARNING</literal>
+ will be generated.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_tablespace</replaceable></term>
+ <listitem>
+ <para>
+ The name of a specific tablespace to store rebuilt indexes.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>VERBOSE</literal></term>
<listitem>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index bd7ec923e9..5267ecfffb 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1247,9 +1247,13 @@ index_create(Relation heapRelation,
* Create concurrently an index based on the definition of the one provided by
* caller. The index is inserted into catalogs and needs to be built later
* on. This is called during concurrent reindex processing.
+ *
+ * "tablespaceOid" is the new tablespace to use for this index. If
+ * InvalidOid, use the tablespace in-use instead.
*/
Oid
-index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, const char *newName)
+index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
+ Oid tablespaceOid, const char *newName)
{
Relation indexRelation;
IndexInfo *oldInfo,
@@ -1379,7 +1383,8 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, const char
newInfo,
indexColNames,
indexRelation->rd_rel->relam,
- indexRelation->rd_rel->reltablespace,
+ OidIsValid(tablespaceOid) ?
+ tablespaceOid : indexRelation->rd_rel->reltablespace,
indexRelation->rd_indcollation,
indclass->values,
indcoloptions->values,
@@ -3429,10 +3434,12 @@ IndexGetRelation(Oid indexId, bool missing_ok)
/*
* reindex_index - This routine is used to recreate a single index
+ *
+ * See comments of reindex_relation() for details about "tablespaceOid".
*/
void
-reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
- int options)
+reindex_index(Oid indexId, Oid tablespaceOid, bool skip_constraint_checks,
+ char persistence, int options)
{
Relation iRel,
heapRelation;
@@ -3441,6 +3448,7 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
volatile bool skipped_constraint = false;
PGRUsage ru0;
bool progress = (options & REINDEXOPT_REPORT_PROGRESS) != 0;
+ bool set_tablespace = OidIsValid(tablespaceOid);
pg_rusage_init(&ru0);
@@ -3479,6 +3487,27 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
elog(ERROR, "unsupported relation kind for index \"%s\"",
RelationGetRelationName(iRel));
+ /*
+ * We don't support moving system relations into different tablespaces,
+ * unless allow_system_table_mods=1.
+ */
+ if (set_tablespace &&
+ !allowSystemTableMods && IsSystemRelation(iRel))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied: \"%s\" is a system catalog",
+ RelationGetRelationName(iRel))));
+
+ /*
+ * We cannot support moving mapped relations into different tablespaces.
+ * (In particular this eliminates all shared catalogs.)
+ */
+ if (set_tablespace && RelationIsMapped(iRel))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot change tablespace of mapped relation \"%s\"",
+ RelationGetRelationName(iRel))));
+
/*
* Don't allow reindex on temp tables of other backends ... their local
* buffer manager is not going to cope.
@@ -3505,6 +3534,47 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
*/
CheckTableNotInUse(iRel, "REINDEX INDEX");
+ if (tablespaceOid == MyDatabaseTableSpace)
+ tablespaceOid = InvalidOid;
+
+ /*
+ * Set the new tablespace for the relation. Do that only in the
+ * case where the reindex caller wishes to enforce a new tablespace.
+ */
+ if (set_tablespace &&
+ tablespaceOid != iRel->rd_rel->reltablespace)
+ {
+ Relation pg_class;
+ Form_pg_class rd_rel;
+ HeapTuple tuple;
+
+ /* First get a modifiable copy of the relation's pg_class row */
+ pg_class = table_open(RelationRelationId, RowExclusiveLock);
+
+ tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(indexId));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u", indexId);
+ rd_rel = (Form_pg_class) GETSTRUCT(tuple);
+
+ /*
+ * Mark the relation as ready to be dropped at transaction commit,
+ * before making visible the new tablespace change so as this won't
+ * miss things.
+ */
+ RelationDropStorage(iRel);
+
+ /* Update the pg_class row */
+ rd_rel->reltablespace = tablespaceOid;
+ CatalogTupleUpdate(pg_class, &tuple->t_self, tuple);
+
+ heap_freetuple(tuple);
+
+ table_close(pg_class, RowExclusiveLock);
+
+ /* Make sure the reltablespace change is visible */
+ CommandCounterIncrement();
+ }
+
/*
* All predicate locks on the index are about to be made invalid. Promote
* them to relation locks on the heap.
@@ -3643,6 +3713,10 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
* reindex_relation - This routine is used to recreate all indexes
* of a relation (and optionally its toast relation too, if any).
*
+ * "tablespaceOid" defines the new tablespace where the indexes of
+ * the relation will be rebuilt. If InvalidOid is used, the current
+ * tablespace of each index is used instead.
+ *
* "flags" is a bitmask that can include any combination of these bits:
*
* REINDEX_REL_PROCESS_TOAST: if true, process the toast table too (if any).
@@ -3675,7 +3749,7 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
* index rebuild.
*/
bool
-reindex_relation(Oid relid, int flags, int options)
+reindex_relation(Oid relid, Oid tablespaceOid, int flags, int options)
{
Relation rel;
Oid toast_relid;
@@ -3766,7 +3840,8 @@ reindex_relation(Oid relid, int flags, int options)
continue;
}
- reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
+ reindex_index(indexOid, tablespaceOid,
+ !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
persistence, options);
CommandCounterIncrement();
@@ -3799,7 +3874,7 @@ reindex_relation(Oid relid, int flags, int options)
* still hold the lock on the master table.
*/
if ((flags & REINDEX_REL_PROCESS_TOAST) && OidIsValid(toast_relid))
- result |= reindex_relation(toast_relid, flags, options);
+ result |= reindex_relation(toast_relid, tablespaceOid, flags, options);
return result;
}
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 6c3fdd3874..0f25576a32 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -1425,7 +1425,7 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
PROGRESS_CLUSTER_PHASE_REBUILD_INDEX);
- reindex_relation(OIDOldHeap, reindex_flags, 0);
+ reindex_relation(OIDOldHeap, InvalidOid, reindex_flags, 0);
/* Report that we are now doing clean up */
pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index e0f87f2dbf..3ff8d4f006 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -87,7 +87,7 @@ static char *ChooseIndexNameAddition(List *colnames);
static List *ChooseIndexColumnNames(List *indexElems);
static void RangeVarCallbackForReindexIndex(const RangeVar *relation,
Oid relId, Oid oldRelId, void *arg);
-static bool ReindexRelationConcurrently(Oid relationOid, int options);
+static bool ReindexRelationConcurrently(Oid relationOid, Oid tablespaceOid, int options);
static void ReindexPartitionedIndex(Relation parentIdx);
static void update_relispartition(Oid relationId, bool newval);
static bool CompareOpclassOptions(Datum *opts1, Datum *opts2, int natts);
@@ -2371,6 +2371,7 @@ ReindexIndex(ReindexStmt *stmt)
RangeVar *indexRelation = stmt->relation;
struct ReindexIndexCallbackState state;
Oid indOid;
+ Oid tablespaceOid = InvalidOid;
Relation irel;
char persistence;
@@ -2405,12 +2406,26 @@ ReindexIndex(ReindexStmt *stmt)
}
persistence = irel->rd_rel->relpersistence;
+
+ /* Define new tablespaceOid if it is wanted by caller */
+ if (stmt->tablespacename)
+ {
+ tablespaceOid = get_tablespace_oid(stmt->tablespacename, false);
+
+ /* Can't move a non-shared relation into pg_global */
+ if (tablespaceOid == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move non-shared relation to tablespace \"%s\"",
+ stmt->tablespacename)));
+ }
+
index_close(irel, NoLock);
if (stmt->concurrent && persistence != RELPERSISTENCE_TEMP)
- ReindexRelationConcurrently(indOid, stmt->options);
+ ReindexRelationConcurrently(indOid, tablespaceOid, stmt->options);
else
- reindex_index(indOid, false, persistence,
+ reindex_index(indOid, tablespaceOid, false, persistence,
stmt->options | REINDEXOPT_REPORT_PROGRESS);
}
@@ -2494,6 +2509,7 @@ ReindexTable(ReindexStmt *stmt)
RangeVar *relation = stmt->relation;
Oid heapOid;
bool result;
+ Oid tablespaceOid = InvalidOid;
/*
* The lock level used here should match reindex_relation().
@@ -2508,9 +2524,22 @@ ReindexTable(ReindexStmt *stmt)
0,
RangeVarCallbackOwnsTable, NULL);
+ /* Define new tablespaceOid if it is wanted by caller */
+ if (stmt->tablespacename)
+ {
+ tablespaceOid = get_tablespace_oid(stmt->tablespacename, false);
+
+ /* Can't move a non-shared relation into pg_global */
+ if (tablespaceOid == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move non-shared relation to tablespace \"%s\"",
+ stmt->tablespacename)));
+ }
+
if (stmt->concurrent && get_rel_persistence(heapOid) != RELPERSISTENCE_TEMP)
{
- result = ReindexRelationConcurrently(heapOid, stmt->options);
+ result = ReindexRelationConcurrently(heapOid, tablespaceOid, stmt->options);
if (!result)
ereport(NOTICE,
@@ -2520,6 +2549,7 @@ ReindexTable(ReindexStmt *stmt)
else
{
result = reindex_relation(heapOid,
+ tablespaceOid,
REINDEX_REL_PROCESS_TOAST |
REINDEX_REL_CHECK_CONSTRAINTS,
stmt->options | REINDEXOPT_REPORT_PROGRESS);
@@ -2546,6 +2576,7 @@ ReindexMultipleTables(ReindexStmt *stmt)
const char *objectName = stmt->name;
ReindexObjectType objectKind = stmt->kind;
Oid objectOid;
+ Oid tablespaceOid = InvalidOid;
Relation relationRelation;
TableScanDesc scan;
ScanKeyData scan_keys[1];
@@ -2556,6 +2587,7 @@ ReindexMultipleTables(ReindexStmt *stmt)
ListCell *l;
int num_keys;
bool concurrent_warning = false;
+ bool tablespace_warning = false;
AssertArg(objectName);
Assert(objectKind == REINDEX_OBJECT_SCHEMA ||
@@ -2594,6 +2626,19 @@ ReindexMultipleTables(ReindexStmt *stmt)
objectName);
}
+ /* Define new tablespaceOid if it is wanted by caller */
+ if (stmt->tablespacename)
+ {
+ tablespaceOid = get_tablespace_oid(stmt->tablespacename, false);
+
+ /* Can't move a non-shared relation into pg_global */
+ if (tablespaceOid == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move non-shared relation to tablespace \"%s\"",
+ stmt->tablespacename)));
+ }
+
/*
* Create a memory context that will survive forced transaction commits we
* do below. Since it is a child of PortalContext, it will go away
@@ -2684,6 +2729,35 @@ ReindexMultipleTables(ReindexStmt *stmt)
continue;
}
+ if (OidIsValid(tablespaceOid) &&
+ IsSystemClass(relid, classtuple))
+ {
+ if (!allowSystemTableMods)
+ {
+ /* Skip all system relations, if not allowSystemTableMods */
+ if (!tablespace_warning)
+ ereport(WARNING,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("cannot change tablespace of indexes on system relations, skipping all")));
+ tablespace_warning = true;
+ continue;
+ }
+ else if (!OidIsValid(classtuple->relfilenode))
+ {
+ /*
+ * Skip all mapped relations.
+ * relfilenode == 0 checks after that, similarly to
+ * RelationIsMapped().
+ */
+ if (!tablespace_warning)
+ ereport(WARNING,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot change tablespace of indexes on mapped relations, skipping all")));
+ tablespace_warning = true;
+ continue;
+ }
+ }
+
/* Save the list of relation OIDs in private context */
old = MemoryContextSwitchTo(private_context);
@@ -2717,7 +2791,7 @@ ReindexMultipleTables(ReindexStmt *stmt)
if (stmt->concurrent && get_rel_persistence(relid) != RELPERSISTENCE_TEMP)
{
- (void) ReindexRelationConcurrently(relid, stmt->options);
+ (void) ReindexRelationConcurrently(relid, tablespaceOid, stmt->options);
/* ReindexRelationConcurrently() does the verbose output */
}
else
@@ -2725,6 +2799,7 @@ ReindexMultipleTables(ReindexStmt *stmt)
bool result;
result = reindex_relation(relid,
+ tablespaceOid,
REINDEX_REL_PROCESS_TOAST |
REINDEX_REL_CHECK_CONSTRAINTS,
stmt->options | REINDEXOPT_REPORT_PROGRESS);
@@ -2757,6 +2832,9 @@ ReindexMultipleTables(ReindexStmt *stmt)
* itself will be rebuilt. If 'relationOid' belongs to a partitioned table
* then we issue a warning to mention these are not yet supported.
*
+ * 'tablespaceOid' defines the new tablespace where the indexes of
+ * the relation will be rebuilt.
+ *
* The locks taken on parent tables and involved indexes are kept until the
* transaction is committed, at which point a session lock is taken on each
* relation. Both of these protect against concurrent schema changes.
@@ -2771,7 +2849,7 @@ ReindexMultipleTables(ReindexStmt *stmt)
* anyway, and a non-concurrent reindex is more efficient.
*/
static bool
-ReindexRelationConcurrently(Oid relationOid, int options)
+ReindexRelationConcurrently(Oid relationOid, Oid tablespaceOid, int options)
{
List *heapRelationIds = NIL;
List *indexIds = NIL;
@@ -2844,6 +2922,27 @@ ReindexRelationConcurrently(Oid relationOid, int options)
/* Open relation to get its indexes */
heapRelation = table_open(relationOid, ShareUpdateExclusiveLock);
+ /*
+ * We don't support moving system relations into different tablespaces,
+ * unless allow_system_table_mods=1.
+ */
+ if (OidIsValid(tablespaceOid) &&
+ !allowSystemTableMods && IsSystemRelation(heapRelation))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied: \"%s\" is a system catalog",
+ RelationGetRelationName(heapRelation))));
+
+ /*
+ * We cannot support moving mapped relations into different tablespaces.
+ * (In particular this eliminates all shared catalogs.)
+ */
+ if (OidIsValid(tablespaceOid) && RelationIsMapped(heapRelation))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot change tablespace of indexes on mapped relation \"%s\"",
+ RelationGetRelationName(heapRelation))));
+
/* Add all the valid indexes of relation to list */
foreach(lc, RelationGetIndexList(heapRelation))
{
@@ -3026,6 +3125,27 @@ ReindexRelationConcurrently(Oid relationOid, int options)
if (indexRel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
elog(ERROR, "cannot reindex a temporary table concurrently");
+ /*
+ * We don't support moving system relations into different tablespaces,
+ * unless allow_system_table_mods=1.
+ */
+ if (OidIsValid(tablespaceOid) &&
+ !allowSystemTableMods && IsSystemRelation(indexRel))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied: \"%s\" is a system catalog",
+ RelationGetRelationName(indexRel))));
+
+ /*
+ * We cannot support moving mapped relations into different tablespaces.
+ * (In particular this eliminates all shared catalogs.)
+ */
+ if (OidIsValid(tablespaceOid) && RelationIsMapped(indexRel))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot change tablespace of mapped relation \"%s\"",
+ RelationGetRelationName(indexRel))));
+
pgstat_progress_start_command(PROGRESS_COMMAND_CREATE_INDEX,
RelationGetRelid(heapRel));
pgstat_progress_update_param(PROGRESS_CREATEIDX_COMMAND,
@@ -3045,6 +3165,7 @@ ReindexRelationConcurrently(Oid relationOid, int options)
/* Create new index definition based on given index */
newIndexId = index_concurrently_create_copy(heapRel,
indexId,
+ tablespaceOid,
concurrentName);
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index c8c88be2c9..5f6a9fe3e2 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1871,7 +1871,7 @@ ExecuteTruncateGuts(List *explicit_rels, List *relids, List *relids_logged,
/*
* Reconstruct the indexes to match, and we're done.
*/
- reindex_relation(heap_relid, REINDEX_REL_PROCESS_TOAST, 0);
+ reindex_relation(heap_relid, InvalidOid, REINDEX_REL_PROCESS_TOAST, 0);
}
pgstat_count_truncate(rel);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index a4672f1bb8..a2aa687771 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4409,6 +4409,7 @@ _copyReindexStmt(const ReindexStmt *from)
COPY_NODE_FIELD(rawoptions);
COPY_SCALAR_FIELD(options);
COPY_SCALAR_FIELD(concurrent);
+ COPY_STRING_FIELD(tablespacename);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index cb22426dbd..3967d0ce08 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2133,6 +2133,7 @@ _equalReindexStmt(const ReindexStmt *a, const ReindexStmt *b)
COMPARE_NODE_FIELD(rawoptions);
COMPARE_SCALAR_FIELD(options);
COMPARE_SCALAR_FIELD(concurrent);
+ COMPARE_STRING_FIELD(tablespacename);
return true;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index f1ec2b4951..31fe651490 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -8398,31 +8398,33 @@ DropTransformStmt: DROP TRANSFORM opt_if_exists FOR Typename LANGUAGE name opt_d
*
* QUERY:
*
- * REINDEX [ (options) ] type [CONCURRENTLY] <name>
+ * REINDEX [ (options) ] type [CONCURRENTLY] <name> [ TABLESPACE <tablespace_name> ]
*****************************************************************************/
ReindexStmt:
- REINDEX reindex_target_type opt_concurrently qualified_name
+ REINDEX reindex_target_type opt_concurrently qualified_name OptTableSpace
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $2;
n->concurrent = $3;
n->relation = $4;
+ n->tablespacename = $5;
n->name = NULL;
n->rawoptions = NIL;
$$ = (Node *)n;
}
- | REINDEX reindex_target_multitable opt_concurrently name
+ | REINDEX reindex_target_multitable opt_concurrently name OptTableSpace
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $2;
n->concurrent = $3;
n->name = $4;
+ n->tablespacename = $5;
n->relation = NULL;
n->rawoptions = NIL;
$$ = (Node *)n;
}
- | REINDEX '(' reindex_option_list ')' reindex_target_type opt_concurrently qualified_name
+ | REINDEX '(' reindex_option_list ')' reindex_target_type opt_concurrently qualified_name OptTableSpace
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $5;
@@ -8432,7 +8434,7 @@ ReindexStmt:
n->rawoptions = $3;
$$ = (Node *)n;
}
- | REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name
+ | REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name OptTableSpace
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $5;
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index ca8f0d75a6..f2d0e81bfe 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3436,6 +3436,12 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
else if (Matches("REINDEX", "SYSTEM|DATABASE", "CONCURRENTLY"))
COMPLETE_WITH_QUERY(Query_for_list_of_databases);
+ else if (Matches("REINDEX", MatchAny, "CONCURRENTLY", MatchAny))
+ COMPLETE_WITH("TABLESPACE");
+ else if (Matches("REINDEX", MatchAny, MatchAny))
+ COMPLETE_WITH("TABLESPACE");
+ else if (TailMatches("TABLESPACE"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
/* SECURITY LABEL */
else if (Matches("SECURITY"))
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index a2890c1314..f38e978b45 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -80,6 +80,7 @@ extern Oid index_create(Relation heapRelation,
extern Oid index_concurrently_create_copy(Relation heapRelation,
Oid oldIndexId,
+ Oid tablespaceOid,
const char *newName);
extern void index_concurrently_build(Oid heapRelationId,
@@ -131,8 +132,8 @@ extern void validate_index(Oid heapId, Oid indexId, Snapshot snapshot);
extern void index_set_state_flags(Oid indexId, IndexStateFlagsAction action);
-extern void reindex_index(Oid indexId, bool skip_constraint_checks,
- char relpersistence, int options);
+extern void reindex_index(Oid indexId, Oid tablespaceOid, bool skip_constraint_checks,
+ char relpersistence, int options);
/* Flag bits for reindex_relation(): */
#define REINDEX_REL_PROCESS_TOAST 0x01
@@ -141,7 +142,7 @@ extern void reindex_index(Oid indexId, bool skip_constraint_checks,
#define REINDEX_REL_FORCE_INDEXES_UNLOGGED 0x08
#define REINDEX_REL_FORCE_INDEXES_PERMANENT 0x10
-extern bool reindex_relation(Oid relid, int flags, int options);
+extern bool reindex_relation(Oid relid, Oid tablespaceOid, int flags, int options);
extern bool ReindexIsProcessingHeap(Oid heapOid);
extern bool ReindexIsProcessingIndex(Oid indexOid);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f72587a584..21b1f51950 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3366,6 +3366,7 @@ typedef struct ReindexStmt
List *rawoptions; /* Raw options */
int options; /* Parsed options */
bool concurrent; /* reindex concurrently? */
+ char *tablespacename; /* name of tablespace to store index */
} ReindexStmt;
/* ----------------------
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index a5f61a35dc..77ea4cbcee 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -17,6 +17,42 @@ ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- f
ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
+-- create table to test REINDEX with TABLESPACE change
+CREATE TABLE regress_tblspace_test_tbl (num1 bigint, num2 double precision, num3 double precision);
+INSERT INTO regress_tblspace_test_tbl (num1, num2, num3)
+ SELECT round(random()*100), random(), random()*42
+ FROM generate_series(1, 20000) s(i);
+CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1);
+
+-- check that REINDEX with TABLESPACE change is transactional
+BEGIN;
+REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace;
+REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace;
+ROLLBACK;
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+
+-- check REINDEX with TABLESPACE change
+REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok
+REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; -- ok
+REINDEX TABLE pg_authid TABLESPACE regress_tblspace; -- fail
+REINDEX SYSTEM CONCURRENTLY postgres TABLESPACE regress_tblspace; -- fail
+REINDEX TABLE CONCURRENTLY pg_am TABLESPACE regress_tblspace; -- fail
+REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail
+REINDEX TABLE pg_am TABLESPACE regress_tblspace; -- fail
+
+-- check that all relations moved to new tablespace
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+ORDER BY relname;
+
+-- move indexes back to pg_default tablespace
+REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl TABLESPACE pg_default; -- ok
+
+-- check that all relations moved back to pg_default
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+
-- create a schema we can use
CREATE SCHEMA testschema;
@@ -279,6 +315,9 @@ ALTER TABLE ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default
-- Should succeed
DROP TABLESPACE regress_tblspace_renamed;
+DROP INDEX regress_tblspace_test_tbl_idx;
+DROP TABLE regress_tblspace_test_tbl;
+
DROP SCHEMA testschema CASCADE;
DROP ROLE regress_tablespace_user1;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 162b591b31..0db9929f44 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -20,6 +20,54 @@ ERROR: unrecognized parameter "some_nonexistent_parameter"
ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
ERROR: RESET must not include values for parameters
ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
+-- create table to test REINDEX with TABLESPACE change
+CREATE TABLE regress_tblspace_test_tbl (num1 bigint, num2 double precision, num3 double precision);
+INSERT INTO regress_tblspace_test_tbl (num1, num2, num3)
+ SELECT round(random()*100), random(), random()*42
+ FROM generate_series(1, 20000) s(i);
+CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1);
+-- check that REINDEX with TABLESPACE change is transactional
+BEGIN;
+REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace;
+REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace;
+ROLLBACK;
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+ relname
+---------
+(0 rows)
+
+-- check REINDEX with TABLESPACE change
+REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok
+REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; -- ok
+REINDEX TABLE pg_authid TABLESPACE regress_tblspace; -- fail
+ERROR: permission denied: "pg_authid_rolname_index" is a system catalog
+REINDEX SYSTEM CONCURRENTLY postgres TABLESPACE regress_tblspace; -- fail
+ERROR: cannot reindex system catalogs concurrently
+REINDEX TABLE CONCURRENTLY pg_am TABLESPACE regress_tblspace; -- fail
+ERROR: cannot reindex system catalogs concurrently
+REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail
+ERROR: cannot move non-shared relation to tablespace "pg_global"
+REINDEX TABLE pg_am TABLESPACE regress_tblspace; -- fail
+ERROR: permission denied: "pg_am_name_index" is a system catalog
+-- check that all relations moved to new tablespace
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+ORDER BY relname;
+ relname
+-------------------------------
+ regress_tblspace_test_tbl_idx
+(1 row)
+
+-- move indexes back to pg_default tablespace
+REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl TABLESPACE pg_default; -- ok
+-- check that all relations moved back to pg_default
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+ relname
+---------
+(0 rows)
+
-- create a schema we can use
CREATE SCHEMA testschema;
-- try a table
@@ -736,6 +784,8 @@ ALTER TABLE ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default
NOTICE: no matching relations in tablespace "regress_tblspace_renamed" found
-- Should succeed
DROP TABLESPACE regress_tblspace_renamed;
+DROP INDEX regress_tblspace_test_tbl_idx;
+DROP TABLE regress_tblspace_test_tbl;
DROP SCHEMA testschema CASCADE;
NOTICE: drop cascades to 6 other objects
DETAIL: drop cascades to table testschema.foo
--
2.17.0
>From b7c3ccf34cd9fd9db09ed4bd8534ba522bf48c4e Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Wed, 1 Apr 2020 04:46:57 -0500
Subject: [PATCH v16 3/7] fix!Parenthesized syntax: REINDEX (TABLESPACE ..)
---
doc/src/sgml/ref/reindex.sgml | 21 +++++++++++----------
src/backend/parser/gram.y | 12 +++++-------
src/backend/tcop/utility.c | 3 +++
src/test/regress/input/tablespace.source | 20 ++++++++++----------
src/test/regress/output/tablespace.source | 20 ++++++++++----------
5 files changed, 39 insertions(+), 37 deletions(-)
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index f3ceb6f14a..635c762f18 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -21,11 +21,12 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable> [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ]
+REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable>
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
VERBOSE
+ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -174,15 +175,6 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
</listitem>
</varlistentry>
- <varlistentry>
- <term><replaceable class="parameter">new_tablespace</replaceable></term>
- <listitem>
- <para>
- The name of a specific tablespace to store rebuilt indexes.
- </para>
- </listitem>
- </varlistentry>
-
<varlistentry>
<term><literal>VERBOSE</literal></term>
<listitem>
@@ -204,6 +196,15 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_tablespace</replaceable></term>
+ <listitem>
+ <para>
+ The tablespace where indexes will be rebuilt.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</refsect1>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 31fe651490..f1ec2b4951 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -8398,33 +8398,31 @@ DropTransformStmt: DROP TRANSFORM opt_if_exists FOR Typename LANGUAGE name opt_d
*
* QUERY:
*
- * REINDEX [ (options) ] type [CONCURRENTLY] <name> [ TABLESPACE <tablespace_name> ]
+ * REINDEX [ (options) ] type [CONCURRENTLY] <name>
*****************************************************************************/
ReindexStmt:
- REINDEX reindex_target_type opt_concurrently qualified_name OptTableSpace
+ REINDEX reindex_target_type opt_concurrently qualified_name
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $2;
n->concurrent = $3;
n->relation = $4;
- n->tablespacename = $5;
n->name = NULL;
n->rawoptions = NIL;
$$ = (Node *)n;
}
- | REINDEX reindex_target_multitable opt_concurrently name OptTableSpace
+ | REINDEX reindex_target_multitable opt_concurrently name
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $2;
n->concurrent = $3;
n->name = $4;
- n->tablespacename = $5;
n->relation = NULL;
n->rawoptions = NIL;
$$ = (Node *)n;
}
- | REINDEX '(' reindex_option_list ')' reindex_target_type opt_concurrently qualified_name OptTableSpace
+ | REINDEX '(' reindex_option_list ')' reindex_target_type opt_concurrently qualified_name
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $5;
@@ -8434,7 +8432,7 @@ ReindexStmt:
n->rawoptions = $3;
$$ = (Node *)n;
}
- | REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name OptTableSpace
+ | REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $5;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index d6674a9e38..ee9e3ed836 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -539,6 +539,9 @@ void parse_reindex_options(ParseState *pstate, ReindexStmt *stmt)
// XXX: handle boolean opt: VERBOSE off
else if (strcmp(opt->defname, "concurrently") == 0)
stmt->concurrent = true;
+ else if (strcmp(opt->defname, "tablespace") == 0)
+ stmt->tablespacename = defGetString(opt);
+ // XXX: if (tablespaceOid == GLOBALTABLESPACE_OID)
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 77ea4cbcee..57715b3cca 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -26,20 +26,20 @@ CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1);
-- check that REINDEX with TABLESPACE change is transactional
BEGIN;
-REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace;
-REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace;
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_tbl_idx;
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl;
ROLLBACK;
SELECT relname FROM pg_class
WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
-- check REINDEX with TABLESPACE change
-REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok
-REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; -- ok
-REINDEX TABLE pg_authid TABLESPACE regress_tblspace; -- fail
-REINDEX SYSTEM CONCURRENTLY postgres TABLESPACE regress_tblspace; -- fail
-REINDEX TABLE CONCURRENTLY pg_am TABLESPACE regress_tblspace; -- fail
-REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail
-REINDEX TABLE pg_am TABLESPACE regress_tblspace; -- fail
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_tbl_idx; -- ok
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl; -- ok
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_authid; -- fail
+REINDEX (TABLESPACE regress_tblspace) SYSTEM CONCURRENTLY postgres; -- fail
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_am; -- fail
+REINDEX (TABLESPACE pg_global) INDEX regress_tblspace_test_tbl_idx; -- fail
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_am; -- fail
-- check that all relations moved to new tablespace
SELECT relname FROM pg_class
@@ -47,7 +47,7 @@ WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspa
ORDER BY relname;
-- move indexes back to pg_default tablespace
-REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl TABLESPACE pg_default; -- ok
+REINDEX (TABLESPACE pg_default) TABLE CONCURRENTLY regress_tblspace_test_tbl; -- ok
-- check that all relations moved back to pg_default
SELECT relname FROM pg_class
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 0db9929f44..7733254416 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -28,8 +28,8 @@ INSERT INTO regress_tblspace_test_tbl (num1, num2, num3)
CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1);
-- check that REINDEX with TABLESPACE change is transactional
BEGIN;
-REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace;
-REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace;
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_tbl_idx;
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl;
ROLLBACK;
SELECT relname FROM pg_class
WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
@@ -38,17 +38,17 @@ WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspa
(0 rows)
-- check REINDEX with TABLESPACE change
-REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok
-REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; -- ok
-REINDEX TABLE pg_authid TABLESPACE regress_tblspace; -- fail
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_tbl_idx; -- ok
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl; -- ok
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_authid; -- fail
ERROR: permission denied: "pg_authid_rolname_index" is a system catalog
-REINDEX SYSTEM CONCURRENTLY postgres TABLESPACE regress_tblspace; -- fail
+REINDEX (TABLESPACE regress_tblspace) SYSTEM CONCURRENTLY postgres; -- fail
ERROR: cannot reindex system catalogs concurrently
-REINDEX TABLE CONCURRENTLY pg_am TABLESPACE regress_tblspace; -- fail
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_am; -- fail
ERROR: cannot reindex system catalogs concurrently
-REINDEX INDEX regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail
+REINDEX (TABLESPACE pg_global) INDEX regress_tblspace_test_tbl_idx; -- fail
ERROR: cannot move non-shared relation to tablespace "pg_global"
-REINDEX TABLE pg_am TABLESPACE regress_tblspace; -- fail
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_am; -- fail
ERROR: permission denied: "pg_am_name_index" is a system catalog
-- check that all relations moved to new tablespace
SELECT relname FROM pg_class
@@ -60,7 +60,7 @@ ORDER BY relname;
(1 row)
-- move indexes back to pg_default tablespace
-REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl TABLESPACE pg_default; -- ok
+REINDEX (TABLESPACE pg_default) TABLE CONCURRENTLY regress_tblspace_test_tbl; -- ok
-- check that all relations moved back to pg_default
SELECT relname FROM pg_class
WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
--
2.17.0
>From cd8144beb9d2a77a29fe7a042ac3f67fbeb710bd Mon Sep 17 00:00:00 2001
From: Alexey Kondratov <kondratov.alek...@gmail.com>
Date: Tue, 24 Mar 2020 18:16:06 +0300
Subject: [PATCH v16 4/7] Allow CLUSTER and VACUUM FULL to change tablespace
---
doc/src/sgml/ref/cluster.sgml | 11 ++++-
doc/src/sgml/ref/vacuum.sgml | 11 +++++
src/backend/commands/cluster.c | 42 ++++++++++++++++---
src/backend/commands/vacuum.c | 51 +++++++++++++++++++++--
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/parser/gram.y | 38 ++++++++++++++++-
src/backend/postmaster/autovacuum.c | 1 +
src/include/commands/cluster.h | 2 +-
src/include/commands/vacuum.h | 2 +
src/include/nodes/parsenodes.h | 2 +
src/test/regress/input/tablespace.source | 23 +++++++++-
src/test/regress/output/tablespace.source | 37 +++++++++++++++-
13 files changed, 208 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml
index bd0682ddfd..4847837765 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>
@@ -108,6 +108,15 @@ CLUSTER [VERBOSE]
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">new_tablespace</replaceable></term>
+ <listitem>
+ <para>
+ The name of a specific tablespace to store clustered relations.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect1>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 846056a353..0a00125a36 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -23,6 +23,7 @@ PostgreSQL documentation
<synopsis>
VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
+VACUUM ( FULL [, ...] ) [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
@@ -299,6 +300,16 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_tablespace</replaceable></term>
+ <listitem>
+ <para>
+ The name of a specific tablespace to write a new copy of the table.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect1>
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 0f25576a32..7c52bb470f 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -33,11 +33,13 @@
#include "catalog/namespace.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_am.h"
+#include "catalog/pg_tablespace.h"
#include "catalog/toasting.h"
#include "commands/cluster.h"
#include "commands/defrem.h"
#include "commands/progress.h"
#include "commands/tablecmds.h"
+#include "commands/tablespace.h"
#include "commands/vacuum.h"
#include "miscadmin.h"
#include "optimizer/optimizer.h"
@@ -68,7 +70,7 @@ typedef struct
} RelToCluster;
-static void rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose);
+static void rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTableSpaceOid, bool verbose);
static void copy_table_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex,
bool verbose, bool *pSwapToastByContent,
TransactionId *pFreezeXid, MultiXactId *pCutoffMulti);
@@ -201,7 +203,7 @@ cluster(ParseState *pstate, 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
{
@@ -249,7 +251,7 @@ cluster(ParseState *pstate, 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();
@@ -281,7 +283,7 @@ cluster(ParseState *pstate, 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);
@@ -394,6 +396,23 @@ cluster_rel(Oid tableOid, Oid indexOid, int options)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot cluster a shared catalog")));
+ if (OidIsValid(tablespaceOid) &&
+ !allowSystemTableMods && IsSystemRelation(OldHeap))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied: \"%s\" is a system catalog",
+ RelationGetRelationName(OldHeap))));
+
+ /*
+ * We cannot support moving mapped relations into different tablespaces.
+ * (In particular this eliminates all shared catalogs.)
+ */
+ if (OidIsValid(tablespaceOid) && RelationIsMapped(OldHeap))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot change tablespace of mapped relation \"%s\"",
+ RelationGetRelationName(OldHeap))));
+
/*
* Don't process temp tables of other backends ... their local buffer
* manager is not going to cope.
@@ -444,7 +463,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 */
@@ -603,7 +622,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;
@@ -614,6 +633,10 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
TransactionId frozenXid;
MultiXactId cutoffMulti;
+ /* Use new tablespace if passed. */
+ if (OidIsValid(NewTablespaceOid))
+ tableSpace = NewTablespaceOid;
+
/* Mark the correct index as clustered */
if (OidIsValid(indexOid))
mark_index_clustered(OldHeap, indexOid, true);
@@ -1058,6 +1081,13 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
*/
Assert(!target_is_pg_class);
+ if (!allowSystemTableMods && IsSystemClass(r1, relform1) &&
+ relform1->reltablespace != relform2->reltablespace)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied: \"%s\" is a system catalog",
+ get_rel_name(r1))));
+
swaptemp = relform1->relfilenode;
relform1->relfilenode = relform2->relfilenode;
relform2->relfilenode = swaptemp;
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 59731d687f..95bec8d6ba 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -31,13 +31,16 @@
#include "access/tableam.h"
#include "access/transam.h"
#include "access/xact.h"
+#include "catalog/catalog.h"
#include "catalog/namespace.h"
#include "catalog/pg_database.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_namespace.h"
+#include "catalog/pg_tablespace.h"
#include "commands/cluster.h"
#include "commands/defrem.h"
#include "commands/vacuum.h"
+#include "commands/tablespace.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "pgstat.h"
@@ -106,6 +109,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
bool disable_page_skipping = false;
bool parallel_option = false;
ListCell *lc;
+ Oid tablespaceOid = InvalidOid; /* Oid of tablespace to use for relations
+ * after VACUUM FULL. */
/* Set default value */
params.index_cleanup = VACOPT_TERNARY_DEFAULT;
@@ -241,6 +246,28 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
params.multixact_freeze_table_age = -1;
}
+ /* Get tablespace Oid to use. */
+ if (vacstmt->tablespacename)
+ {
+ if (params.options & VACOPT_FULL)
+ {
+ tablespaceOid = get_tablespace_oid(vacstmt->tablespacename, false);
+
+ /* Can't move a non-shared relation into pg_global */
+ if (tablespaceOid == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move non-shared relation to tablespace \"%s\"",
+ vacstmt->tablespacename)));
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("incompatible TABLESPACE option"),
+ errdetail("You can only use TABLESPACE with VACUUM FULL.")));
+ }
+ params.tablespace_oid = tablespaceOid;
+
/* user-invoked vacuum is never "for wraparound" */
params.is_wraparound = false;
@@ -1672,8 +1699,9 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
LOCKMODE lmode;
Relation onerel;
LockRelId onerelid;
- Oid toast_relid;
- Oid save_userid;
+ Oid toast_relid,
+ save_userid,
+ tablespaceOid = InvalidOid;
int save_sec_context;
int save_nestlevel;
@@ -1807,6 +1835,23 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
return true;
}
+ /*
+ * We cannot support moving system relations into different tablespaces,
+ * unless allow_system_table_mods=1.
+ */
+ if (params->options & VACOPT_FULL &&
+ OidIsValid(params->tablespace_oid) &&
+ IsSystemRelation(onerel) && !allowSystemTableMods)
+ {
+ ereport(WARNING,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("skipping tablespace change of \"%s\"",
+ RelationGetRelationName(onerel)),
+ errdetail("Cannot move system relation, only VACUUM is performed.")));
+ }
+ else
+ tablespaceOid = params->tablespace_oid;
+
/*
* Get a session-level lock too. This will protect our access to the
* relation across multiple transactions, so that we can vacuum the
@@ -1876,7 +1921,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
cluster_options |= CLUOPT_VERBOSE;
/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
- cluster_rel(relid, InvalidOid, cluster_options);
+ cluster_rel(relid, InvalidOid, tablespaceOid, cluster_options);
}
else
table_relation_vacuum(onerel, params, vac_strategy);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index a2aa687771..e04ee775c2 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3903,6 +3903,7 @@ _copyVacuumStmt(const VacuumStmt *from)
COPY_NODE_FIELD(options);
COPY_NODE_FIELD(rels);
COPY_SCALAR_FIELD(is_vacuumcmd);
+ COPY_STRING_FIELD(tablespacename);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 3967d0ce08..9ffad8be90 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1703,6 +1703,7 @@ _equalVacuumStmt(const VacuumStmt *a, const VacuumStmt *b)
COMPARE_NODE_FIELD(options);
COMPARE_NODE_FIELD(rels);
COMPARE_SCALAR_FIELD(is_vacuumcmd);
+ COMPARE_SCALAR_FIELD(tablespacename);
return true;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index f1ec2b4951..a91617c4e2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10626,7 +10626,7 @@ CreateConversionStmt:
*****************************************************************************/
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;
@@ -10683,6 +10683,8 @@ cluster_index_specification:
*
* QUERY:
* VACUUM
+ * VACUUM FULL [ TABLESPACE <tablespace_name> ] [ <table_and_columns> [, ...] ]
+ * VACUUM (FULL) [ TABLESPACE <tablespace_name> ] [ <table_and_columns> [, ...] ]
* ANALYZE
*
*****************************************************************************/
@@ -10705,6 +10707,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
@@ -10713,6 +10737,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;
}
;
@@ -10726,6 +10760,7 @@ AnalyzeStmt: analyze_keyword opt_verbose opt_vacuum_relation_list
makeDefElem("verbose", NULL, @2));
n->rels = $3;
n->is_vacuumcmd = false;
+ n->tablespacename = NULL;
$$ = (Node *)n;
}
| analyze_keyword '(' vac_analyze_option_list ')' opt_vacuum_relation_list
@@ -10734,6 +10769,7 @@ AnalyzeStmt: analyze_keyword opt_verbose opt_vacuum_relation_list
n->options = $3;
n->rels = $5;
n->is_vacuumcmd = false;
+ n->tablespacename = NULL;
$$ = (Node *) n;
}
;
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 7e97ffab27..8acc321faa 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2897,6 +2897,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
tab->at_params.multixact_freeze_table_age = multixact_freeze_table_age;
tab->at_params.is_wraparound = wraparound;
tab->at_params.log_min_duration = log_min_duration;
+ tab->at_params.tablespace_oid = InvalidOid;
tab->at_vacuum_cost_limit = vac_cost_limit;
tab->at_vacuum_cost_delay = vac_cost_delay;
tab->at_relname = NULL;
diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h
index 674cdcd0cd..bc9f881d8c 100644
--- a/src/include/commands/cluster.h
+++ b/src/include/commands/cluster.h
@@ -20,7 +20,7 @@
extern void cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel);
-extern void cluster_rel(Oid tableOid, Oid indexOid, int options);
+extern void cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, int options);
extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid,
bool recheck, LOCKMODE lockmode);
extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal);
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 2779bea5c9..6758e9812f 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -229,6 +229,8 @@ typedef struct VacuumParams
* disabled.
*/
int nworkers;
+ Oid tablespace_oid; /* tablespace Oid to use for relations
+ * after VACUUM FULL */
} VacuumParams;
/* GUC parameters */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 21b1f51950..2236aaa2dc 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3203,6 +3203,7 @@ typedef struct ClusterStmt
NodeTag type;
RangeVar *relation; /* relation being indexed, or NULL if all */
char *indexname; /* original index defined */
+ char *tablespacename; /* tablespace name to use for clustered relation */
int options; /* OR of ClusterOption flags */
List *params; /* Params not further parsed by the grammar */
} ClusterStmt;
@@ -3220,6 +3221,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 57715b3cca..03c5fb9e9e 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -17,7 +17,7 @@ ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- f
ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
--- create table to test REINDEX with TABLESPACE change
+-- create table to test REINDEX, CLUSTER and VACUUM FULL with TABLESPACE change
CREATE TABLE regress_tblspace_test_tbl (num1 bigint, num2 double precision, num3 double precision);
INSERT INTO regress_tblspace_test_tbl (num1, num2, num3)
SELECT round(random()*100), random(), random()*42
@@ -41,11 +41,32 @@ REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_am; -- fail
REINDEX (TABLESPACE pg_global) INDEX regress_tblspace_test_tbl_idx; -- fail
REINDEX (TABLESPACE regress_tblspace) TABLE pg_am; -- fail
+-- check that all indexes moved to new tablespace
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+ORDER BY relname;
+
+-- check CLUSTER with TABLESPACE change
+CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok
+CLUSTER pg_authid USING pg_authid_rolname_index TABLESPACE regress_tblspace; -- fail
+CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail
+
-- check that all relations moved to new tablespace
SELECT relname FROM pg_class
WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
ORDER BY relname;
+-- check VACUUM with TABLESPACE change
+VACUUM (FULL, ANALYSE, FREEZE) TABLESPACE pg_default regress_tblspace_test_tbl; -- ok
+VACUUM (FULL) TABLESPACE pg_default pg_authid; -- skip with warning
+VACUUM (ANALYSE) TABLESPACE pg_default; -- fail
+VACUUM (FULL) TABLESPACE pg_global regress_tblspace_test_tbl; -- fail
+
+-- check that all tables moved back to pg_default
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+ORDER BY relname;
+
-- move indexes back to pg_default tablespace
REINDEX (TABLESPACE pg_default) TABLE CONCURRENTLY regress_tblspace_test_tbl; -- ok
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 7733254416..c27fa70143 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -20,7 +20,7 @@ ERROR: unrecognized parameter "some_nonexistent_parameter"
ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
ERROR: RESET must not include values for parameters
ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
--- create table to test REINDEX with TABLESPACE change
+-- create table to test REINDEX, CLUSTER and VACUUM FULL with TABLESPACE change
CREATE TABLE regress_tblspace_test_tbl (num1 bigint, num2 double precision, num3 double precision);
INSERT INTO regress_tblspace_test_tbl (num1, num2, num3)
SELECT round(random()*100), random(), random()*42
@@ -50,9 +50,44 @@ REINDEX (TABLESPACE pg_global) INDEX regress_tblspace_test_tbl_idx; -- fail
ERROR: cannot move non-shared relation to tablespace "pg_global"
REINDEX (TABLESPACE regress_tblspace) TABLE pg_am; -- fail
ERROR: permission denied: "pg_am_name_index" is a system catalog
+-- check that all indexes moved to new tablespace
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+ORDER BY relname;
+ relname
+-------------------------------
+ regress_tblspace_test_tbl_idx
+(1 row)
+
+-- check CLUSTER with TABLESPACE change
+CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok
+CLUSTER pg_authid USING pg_authid_rolname_index TABLESPACE regress_tblspace; -- fail
+ERROR: cannot cluster a shared catalog
+CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail
+ERROR: cannot move non-shared relation to tablespace "pg_global"
-- check that all relations moved to new tablespace
SELECT relname FROM pg_class
WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+ORDER BY relname;
+ relname
+-------------------------------
+ regress_tblspace_test_tbl
+ regress_tblspace_test_tbl_idx
+(2 rows)
+
+-- check VACUUM with TABLESPACE change
+VACUUM (FULL, ANALYSE, FREEZE) TABLESPACE pg_default regress_tblspace_test_tbl; -- ok
+VACUUM (FULL) TABLESPACE pg_default pg_authid; -- skip with warning
+WARNING: skipping tablespace change of "pg_authid"
+DETAIL: Cannot move system relation, only VACUUM is performed.
+VACUUM (ANALYSE) TABLESPACE pg_default; -- fail
+ERROR: incompatible TABLESPACE option
+DETAIL: You can only use TABLESPACE with VACUUM FULL.
+VACUUM (FULL) TABLESPACE pg_global regress_tblspace_test_tbl; -- fail
+ERROR: cannot move non-shared relation to tablespace "pg_global"
+-- check that all tables moved back to pg_default
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
ORDER BY relname;
relname
-------------------------------
--
2.17.0
>From cb6a523f47e9cfbed532484c21e7ee5699e24e2d Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Thu, 26 Mar 2020 22:33:56 -0500
Subject: [PATCH v16 5/7] fixes2
---
doc/src/sgml/ref/cluster.sgml | 2 +-
doc/src/sgml/ref/reindex.sgml | 2 +-
doc/src/sgml/ref/vacuum.sgml | 4 ++--
src/backend/commands/cluster.c | 3 +++
src/backend/commands/indexcmds.c | 4 ++--
5 files changed, 9 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml
index 4847837765..a61a77a014 100644
--- a/doc/src/sgml/ref/cluster.sgml
+++ b/doc/src/sgml/ref/cluster.sgml
@@ -112,7 +112,7 @@ CLUSTER [VERBOSE]
<term><replaceable class="parameter">new_tablespace</replaceable></term>
<listitem>
<para>
- The name of a specific tablespace to store clustered relations.
+ The tablespace where the clustered relation will be rebuilt.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 635c762f18..1f692aec60 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -166,7 +166,7 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
<term><literal>TABLESPACE</literal></term>
<listitem>
<para>
- This specifies a tablespace, where all rebuilt indexes will be created.
+ This specifies that indexes will be rebuilt on a new tablespace.
Cannot be used with "mapped" relations. If <literal>SCHEMA</literal>,
<literal>DATABASE</literal> or <literal>SYSTEM</literal> is specified, then
all unsuitable relations will be skipped and a single <literal>WARNING</literal>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 0a00125a36..681486bb38 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -22,7 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
-VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
+VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
VACUUM ( FULL [, ...] ) [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
@@ -305,7 +305,7 @@ VACUUM ( FULL [, ...] ) [ TABLESPACE <replaceable class="parameter">new_tablespa
<term><replaceable class="parameter">new_tablespace</replaceable></term>
<listitem>
<para>
- The name of a specific tablespace to write a new copy of the table.
+ The tablespace where the table will be rebuilt.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 7c52bb470f..ef8b1c9a82 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -281,6 +281,9 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
* If indexOid is InvalidOid, the table will be rewritten in physical order
* instead of index order. This is the new implementation of VACUUM FULL,
* and error messages should refer to the operation as VACUUM not CLUSTER.
+ *
+ * "tablespaceOid" is the tablespace to use for the rebuilt relation. If
+ * InvalidOid, use the tablespace in-use instead.
*/
void
cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, int options)
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 3ff8d4f006..9c09f2cbdc 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -2745,8 +2745,8 @@ ReindexMultipleTables(ReindexStmt *stmt)
else if (!OidIsValid(classtuple->relfilenode))
{
/*
- * Skip all mapped relations.
- * relfilenode == 0 checks after that, similarly to
+ * Skip all mapped relations if TABLESPACE is specified.
+ * OidIsValid(relfilenode) checks that, similar to
* RelationIsMapped().
*/
if (!tablespace_warning)
--
2.17.0
>From 8e9caa318c877871f2bab2f096d2406121166f3b Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Thu, 26 Mar 2020 22:08:40 -0500
Subject: [PATCH v16 6/7] fix!Parenthesized syntax: VACUUM/CLUSTER (TABLESPACE)
---
doc/src/sgml/ref/cluster.sgml | 19 ++++++++-
doc/src/sgml/ref/vacuum.sgml | 14 ++++++-
src/backend/commands/cluster.c | 19 +++++++++
src/backend/commands/vacuum.c | 51 ++++++++++++-----------
src/backend/nodes/copyfuncs.c | 1 -
src/backend/nodes/equalfuncs.c | 1 -
src/backend/parser/gram.y | 36 +---------------
src/include/nodes/parsenodes.h | 2 -
src/test/regress/input/tablespace.source | 14 +++----
src/test/regress/output/tablespace.source | 14 +++----
10 files changed, 90 insertions(+), 81 deletions(-)
diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml
index a61a77a014..0e81e6189b 100644
--- a/doc/src/sgml/ref/cluster.sgml
+++ b/doc/src/sgml/ref/cluster.sgml
@@ -21,8 +21,14 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CLUSTER [VERBOSE] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">index_name</replaceable> ] [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ]
+CLUSTER [VERBOSE] [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">index_name</replaceable> ]
CLUSTER [VERBOSE]
+
+<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
+
+ VERBOSE
+ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
+
</synopsis>
</refsynopsisdiv>
@@ -90,6 +96,15 @@ CLUSTER [VERBOSE]
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>TABLESPACE</literal></term>
+ <listitem>
+ <para>
+ Specifies that the relation will be rebuilt on a new tablespace.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">table_name</replaceable></term>
<listitem>
@@ -112,7 +127,7 @@ CLUSTER [VERBOSE]
<term><replaceable class="parameter">new_tablespace</replaceable></term>
<listitem>
<para>
- The tablespace where the clustered relation will be rebuilt.
+ The tablespace where the relation will be rebuilt.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 681486bb38..b44b10c783 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -22,8 +22,8 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
-VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
-VACUUM ( FULL [, ...] ) [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
+VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
+VACUUM ( FULL [, ...] ) [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
@@ -36,6 +36,7 @@ VACUUM ( FULL [, ...] ) [ TABLESPACE <replaceable class="parameter">new_tablespa
INDEX_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
PARALLEL <replaceable class="parameter">integer</replaceable>
+ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -256,6 +257,15 @@ VACUUM ( FULL [, ...] ) [ TABLESPACE <replaceable class="parameter">new_tablespa
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>TABLESPACE</literal></term>
+ <listitem>
+ <para>
+ Specifies that the relation will be rebuilt on a new tablespace.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">boolean</replaceable></term>
<listitem>
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index ef8b1c9a82..cef4beb2c8 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -105,6 +105,9 @@ void
cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
{
ListCell *lc;
+ /* Name and Oid of tablespace to use for clustered relation. */
+ char *tablespaceName = NULL;
+ Oid tablespaceOid = InvalidOid;
/* Parse list of generic parameter not handled by the parser */
foreach(lc, stmt->params)
@@ -114,6 +117,9 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
if (strcmp(opt->defname, "verbose") == 0)
stmt->options |= CLUOPT_VERBOSE;
// XXX: handle boolean opt: VERBOSE off
+ else if (strcmp(opt->defname, "tablespace") == 0)
+ tablespaceName = defGetString(opt);
+ // XXX: if (tablespaceOid == GLOBALTABLESPACE_OID)
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -122,6 +128,19 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
parser_errposition(pstate, opt->location)));
}
+ /* Select tablespace Oid to use. */
+ if (tablespaceName)
+ {
+ tablespaceOid = get_tablespace_oid(tablespaceName, false);
+
+ /* Can't move a non-shared relation into pg_global */
+ if (tablespaceOid == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move non-shared relation to tablespace \"%s\"",
+ tablespaceName)));
+ }
+
if (stmt->relation != NULL)
{
/* This is the single-relation case. */
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 95bec8d6ba..005fb97a7b 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -109,8 +109,10 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
bool disable_page_skipping = false;
bool parallel_option = false;
ListCell *lc;
- Oid tablespaceOid = InvalidOid; /* Oid of tablespace to use for relations
- * after VACUUM FULL. */
+
+ /* Name and Oid of tablespace to use for relations after VACUUM FULL. */
+ char *tablespacename = NULL;
+ Oid tablespaceOid = InvalidOid;
/* Set default value */
params.index_cleanup = VACOPT_TERNARY_DEFAULT;
@@ -148,6 +150,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
params.index_cleanup = get_vacopt_ternary_value(opt);
else if (strcmp(opt->defname, "truncate") == 0)
params.truncate = get_vacopt_ternary_value(opt);
+ else if (strcmp(opt->defname, "tablespace") == 0)
+ tablespacename = defGetString(opt);
else if (strcmp(opt->defname, "parallel") == 0)
{
parallel_option = true;
@@ -209,6 +213,27 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot specify both FULL and PARALLEL options")));
+ /* Get tablespace Oid to use. */
+ if (tablespacename)
+ {
+ if ((params.options & VACOPT_FULL) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("incompatible TABLESPACE option"),
+ errdetail("You can only use TABLESPACE with VACUUM FULL.")));
+
+ tablespaceOid = get_tablespace_oid(tablespacename, false);
+
+ /* Can't move a non-shared relation into pg_global */
+ if (tablespaceOid == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move non-shared relation to tablespace \"%s\"",
+ tablespacename)));
+
+ }
+ params.tablespace_oid = tablespaceOid;
+
/*
* Make sure VACOPT_ANALYZE is specified if any column lists are present.
*/
@@ -246,28 +271,6 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
params.multixact_freeze_table_age = -1;
}
- /* Get tablespace Oid to use. */
- if (vacstmt->tablespacename)
- {
- if (params.options & VACOPT_FULL)
- {
- tablespaceOid = get_tablespace_oid(vacstmt->tablespacename, false);
-
- /* Can't move a non-shared relation into pg_global */
- if (tablespaceOid == GLOBALTABLESPACE_OID)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot move non-shared relation to tablespace \"%s\"",
- vacstmt->tablespacename)));
- }
- else
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("incompatible TABLESPACE option"),
- errdetail("You can only use TABLESPACE with VACUUM FULL.")));
- }
- params.tablespace_oid = tablespaceOid;
-
/* user-invoked vacuum is never "for wraparound" */
params.is_wraparound = false;
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index e04ee775c2..a2aa687771 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3903,7 +3903,6 @@ _copyVacuumStmt(const VacuumStmt *from)
COPY_NODE_FIELD(options);
COPY_NODE_FIELD(rels);
COPY_SCALAR_FIELD(is_vacuumcmd);
- COPY_STRING_FIELD(tablespacename);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 9ffad8be90..3967d0ce08 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1703,7 +1703,6 @@ _equalVacuumStmt(const VacuumStmt *a, const VacuumStmt *b)
COMPARE_NODE_FIELD(options);
COMPARE_NODE_FIELD(rels);
COMPARE_SCALAR_FIELD(is_vacuumcmd);
- COMPARE_SCALAR_FIELD(tablespacename);
return true;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a91617c4e2..c152d78846 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10626,7 +10626,7 @@ CreateConversionStmt:
*****************************************************************************/
ClusterStmt:
- CLUSTER opt_verbose qualified_name cluster_index_specification OptTableSpace
+ CLUSTER opt_verbose qualified_name cluster_index_specification
{
ClusterStmt *n = makeNode(ClusterStmt);
n->relation = $3;
@@ -10707,28 +10707,6 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose opt_analyze opt_vacuum_relati
makeDefElem("analyze", NULL, @5));
n->rels = $6;
n->is_vacuumcmd = true;
- n->tablespacename = NULL;
- $$ = (Node *)n;
- }
- | VACUUM opt_full opt_freeze opt_verbose opt_analyze TABLESPACE name opt_vacuum_relation_list
- {
- VacuumStmt *n = makeNode(VacuumStmt);
- n->options = NIL;
- if ($2)
- n->options = lappend(n->options,
- makeDefElem("full", NULL, @2));
- if ($3)
- n->options = lappend(n->options,
- makeDefElem("freeze", NULL, @3));
- if ($4)
- n->options = lappend(n->options,
- makeDefElem("verbose", NULL, @4));
- if ($5)
- n->options = lappend(n->options,
- makeDefElem("analyze", NULL, @5));
- n->tablespacename = $7;
- n->rels = $8;
- n->is_vacuumcmd = true;
$$ = (Node *)n;
}
| VACUUM '(' vac_analyze_option_list ')' opt_vacuum_relation_list
@@ -10737,16 +10715,6 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose opt_analyze opt_vacuum_relati
n->options = $3;
n->rels = $5;
n->is_vacuumcmd = true;
- n->tablespacename = NULL;
- $$ = (Node *) n;
- }
- | VACUUM '(' vac_analyze_option_list ')' TABLESPACE name opt_vacuum_relation_list
- {
- VacuumStmt *n = makeNode(VacuumStmt);
- n->options = $3;
- n->tablespacename = $6;
- n->rels = $7;
- n->is_vacuumcmd = true;
$$ = (Node *) n;
}
;
@@ -10760,7 +10728,6 @@ AnalyzeStmt: analyze_keyword opt_verbose opt_vacuum_relation_list
makeDefElem("verbose", NULL, @2));
n->rels = $3;
n->is_vacuumcmd = false;
- n->tablespacename = NULL;
$$ = (Node *)n;
}
| analyze_keyword '(' vac_analyze_option_list ')' opt_vacuum_relation_list
@@ -10769,7 +10736,6 @@ AnalyzeStmt: analyze_keyword opt_verbose opt_vacuum_relation_list
n->options = $3;
n->rels = $5;
n->is_vacuumcmd = false;
- n->tablespacename = NULL;
$$ = (Node *) n;
}
;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 2236aaa2dc..21b1f51950 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3203,7 +3203,6 @@ typedef struct ClusterStmt
NodeTag type;
RangeVar *relation; /* relation being indexed, or NULL if all */
char *indexname; /* original index defined */
- char *tablespacename; /* tablespace name to use for clustered relation */
int options; /* OR of ClusterOption flags */
List *params; /* Params not further parsed by the grammar */
} ClusterStmt;
@@ -3221,7 +3220,6 @@ typedef struct VacuumStmt
List *options; /* list of DefElem nodes */
List *rels; /* list of VacuumRelation, or NIL for all */
bool is_vacuumcmd; /* true for VACUUM, false for ANALYZE */
- char *tablespacename; /* tablespace name to use for vacuumed relation */
} VacuumStmt;
/*
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 03c5fb9e9e..6942775bfb 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -47,9 +47,9 @@ WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspa
ORDER BY relname;
-- check CLUSTER with TABLESPACE change
-CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok
-CLUSTER pg_authid USING pg_authid_rolname_index TABLESPACE regress_tblspace; -- fail
-CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail
+CLUSTER (TABLESPACE regress_tblspace) regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx; -- ok
+CLUSTER (TABLESPACE regress_tblspace) pg_authid USING pg_authid_rolname_index; -- fail
+CLUSTER (TABLESPACE pg_global) regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx; -- fail
-- check that all relations moved to new tablespace
SELECT relname FROM pg_class
@@ -57,10 +57,10 @@ WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspa
ORDER BY relname;
-- check VACUUM with TABLESPACE change
-VACUUM (FULL, ANALYSE, FREEZE) TABLESPACE pg_default regress_tblspace_test_tbl; -- ok
-VACUUM (FULL) TABLESPACE pg_default pg_authid; -- skip with warning
-VACUUM (ANALYSE) TABLESPACE pg_default; -- fail
-VACUUM (FULL) TABLESPACE pg_global regress_tblspace_test_tbl; -- fail
+VACUUM (FULL, ANALYSE, FREEZE, TABLESPACE pg_default) regress_tblspace_test_tbl; -- ok
+VACUUM (FULL, TABLESPACE pg_default) pg_authid; -- skip with warning
+VACUUM (ANALYSE, TABLESPACE pg_default); -- fail
+VACUUM (FULL, TABLESPACE pg_global) regress_tblspace_test_tbl; -- fail
-- check that all tables moved back to pg_default
SELECT relname FROM pg_class
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index c27fa70143..2825984394 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -60,10 +60,10 @@ ORDER BY relname;
(1 row)
-- check CLUSTER with TABLESPACE change
-CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE regress_tblspace; -- ok
-CLUSTER pg_authid USING pg_authid_rolname_index TABLESPACE regress_tblspace; -- fail
+CLUSTER (TABLESPACE regress_tblspace) regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx; -- ok
+CLUSTER (TABLESPACE regress_tblspace) pg_authid USING pg_authid_rolname_index; -- fail
ERROR: cannot cluster a shared catalog
-CLUSTER regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx TABLESPACE pg_global; -- fail
+CLUSTER (TABLESPACE pg_global) regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx; -- fail
ERROR: cannot move non-shared relation to tablespace "pg_global"
-- check that all relations moved to new tablespace
SELECT relname FROM pg_class
@@ -76,14 +76,14 @@ ORDER BY relname;
(2 rows)
-- check VACUUM with TABLESPACE change
-VACUUM (FULL, ANALYSE, FREEZE) TABLESPACE pg_default regress_tblspace_test_tbl; -- ok
-VACUUM (FULL) TABLESPACE pg_default pg_authid; -- skip with warning
+VACUUM (FULL, ANALYSE, FREEZE, TABLESPACE pg_default) regress_tblspace_test_tbl; -- ok
+VACUUM (FULL, TABLESPACE pg_default) pg_authid; -- skip with warning
WARNING: skipping tablespace change of "pg_authid"
DETAIL: Cannot move system relation, only VACUUM is performed.
-VACUUM (ANALYSE) TABLESPACE pg_default; -- fail
+VACUUM (ANALYSE, TABLESPACE pg_default); -- fail
ERROR: incompatible TABLESPACE option
DETAIL: You can only use TABLESPACE with VACUUM FULL.
-VACUUM (FULL) TABLESPACE pg_global regress_tblspace_test_tbl; -- fail
+VACUUM (FULL, TABLESPACE pg_global) regress_tblspace_test_tbl; -- fail
ERROR: cannot move non-shared relation to tablespace "pg_global"
-- check that all tables moved back to pg_default
SELECT relname FROM pg_class
--
2.17.0
>From 79d1554ad648155d209abafc7ef1f602caa8cdda Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Tue, 31 Mar 2020 20:35:41 -0500
Subject: [PATCH v16 7/7] Implement vacuum full/cluster (INDEX_TABLESPACE
<tablespace>)
TODO: docs , tests
---
src/backend/commands/cluster.c | 77 ++++++++++++++++++++---------
src/backend/commands/matview.c | 3 +-
src/backend/commands/tablecmds.c | 2 +-
src/backend/commands/vacuum.c | 65 ++++++++++++++----------
src/backend/postmaster/autovacuum.c | 1 +
src/include/commands/cluster.h | 5 +-
src/include/commands/vacuum.h | 6 ++-
7 files changed, 104 insertions(+), 55 deletions(-)
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index cef4beb2c8..9083d39d26 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -70,7 +70,8 @@ typedef struct
} RelToCluster;
-static void rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTableSpaceOid, bool verbose);
+static Oid cluster_get_tablespace_oid(const char *tablespaceName);
+static void rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTableSpaceOid, Oid NewIdxTableSpaceOid, bool verbose);
static void copy_table_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex,
bool verbose, bool *pSwapToastByContent,
TransactionId *pFreezeXid, MultiXactId *pCutoffMulti);
@@ -106,8 +107,10 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
{
ListCell *lc;
/* Name and Oid of tablespace to use for clustered relation. */
- char *tablespaceName = NULL;
+ char *tablespaceName = NULL,
+ *idxtablespaceName = NULL;
Oid tablespaceOid = InvalidOid;
+ Oid idxtablespaceOid = InvalidOid;
/* Parse list of generic parameter not handled by the parser */
foreach(lc, stmt->params)
@@ -120,6 +123,8 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
else if (strcmp(opt->defname, "tablespace") == 0)
tablespaceName = defGetString(opt);
// XXX: if (tablespaceOid == GLOBALTABLESPACE_OID)
+ else if (strcmp(opt->defname, "index_tablespace") == 0)
+ idxtablespaceName = defGetString(opt);
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -128,18 +133,8 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
parser_errposition(pstate, opt->location)));
}
- /* Select tablespace Oid to use. */
- if (tablespaceName)
- {
- tablespaceOid = get_tablespace_oid(tablespaceName, false);
-
- /* Can't move a non-shared relation into pg_global */
- if (tablespaceOid == GLOBALTABLESPACE_OID)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot move non-shared relation to tablespace \"%s\"",
- tablespaceName)));
- }
+ tablespaceOid = cluster_get_tablespace_oid(tablespaceName);
+ idxtablespaceOid = cluster_get_tablespace_oid(idxtablespaceName);
if (stmt->relation != NULL)
{
@@ -222,7 +217,7 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
table_close(rel, NoLock);
/* Do the job. */
- cluster_rel(tableOid, indexOid, tablespaceOid, stmt->options);
+ cluster_rel(tableOid, indexOid, tablespaceOid, idxtablespaceOid, stmt->options);
}
else
{
@@ -271,7 +266,7 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
PushActiveSnapshot(GetTransactionSnapshot());
/* Do the job. */
cluster_rel(rvtc->tableOid, rvtc->indexOid, tablespaceOid,
- stmt->options | CLUOPT_RECHECK);
+ idxtablespaceOid, stmt->options | CLUOPT_RECHECK);
PopActiveSnapshot();
CommitTransactionCommand();
}
@@ -305,7 +300,7 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
* InvalidOid, use the tablespace in-use instead.
*/
void
-cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, int options)
+cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, Oid idxtablespaceOid, int options)
{
Relation OldHeap;
bool verbose = ((options & CLUOPT_VERBOSE) != 0);
@@ -485,7 +480,7 @@ cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, int options)
TransferPredicateLocksToHeapRelation(OldHeap);
/* rebuild_relation does all the dirty work */
- rebuild_relation(OldHeap, indexOid, tablespaceOid, verbose);
+ rebuild_relation(OldHeap, indexOid, tablespaceOid, idxtablespaceOid, verbose);
/* NB: rebuild_relation does table_close() on OldHeap */
@@ -635,6 +630,27 @@ mark_index_clustered(Relation rel, Oid indexOid, bool is_internal)
table_close(pg_index, RowExclusiveLock);
}
+/* Return Oid of given tablespace */
+static Oid
+cluster_get_tablespace_oid(const char *tablespaceName)
+{
+ Oid ret;
+
+ if (tablespaceName == NULL)
+ return InvalidOid;
+
+ ret = get_tablespace_oid(tablespaceName, false);
+
+ // XXX: check this in cluster_rel ?
+ /* Can't move a non-shared relation into pg_global */
+ if (ret == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move non-shared relation to tablespace \"%s\"",
+ tablespaceName)));
+ return ret;
+}
+
/*
* rebuild_relation: rebuild an existing relation in index or physical order
*
@@ -644,10 +660,11 @@ mark_index_clustered(Relation rel, Oid indexOid, bool is_internal)
* NB: this routine closes OldHeap at the right time; caller should not.
*/
static void
-rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTablespaceOid, bool verbose)
+rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTablespaceOid, Oid NewIdxTablespaceOid, bool verbose)
{
Oid tableOid = RelationGetRelid(OldHeap);
Oid tableSpace = OldHeap->rd_rel->reltablespace;
+ Oid idxtableSpace;
Oid OIDNewHeap;
char relpersistence;
bool is_system_catalog;
@@ -659,6 +676,22 @@ rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTablespaceOid, bool verb
if (OidIsValid(NewTablespaceOid))
tableSpace = NewTablespaceOid;
+ if (OidIsValid(NewIdxTablespaceOid))
+ idxtableSpace = NewIdxTablespaceOid;
+ else if (!OidIsValid(indexOid))
+ idxtableSpace = InvalidOid;
+ else
+ {
+ /* Look up in syscache */
+ bool isNull;
+ HeapTuple tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(indexOid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u", indexOid);
+ idxtableSpace = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_reltablespace,
+ &isNull);
+ ReleaseSysCache(tuple);
+ }
+
/* Mark the correct index as clustered */
if (OidIsValid(indexOid))
mark_index_clustered(OldHeap, indexOid, true);
@@ -686,7 +719,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid, Oid NewTablespaceOid, bool verb
finish_heap_swap(tableOid, OIDNewHeap, is_system_catalog,
swap_toast_by_content, false, true,
frozenXid, cutoffMulti,
- relpersistence);
+ relpersistence, idxtableSpace);
}
@@ -1415,7 +1448,7 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
bool is_internal,
TransactionId frozenXid,
MultiXactId cutoffMulti,
- char newrelpersistence)
+ char newrelpersistence, Oid idxtableSpace)
{
ObjectAddress object;
Oid mapped_tables[4];
@@ -1477,7 +1510,7 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
PROGRESS_CLUSTER_PHASE_REBUILD_INDEX);
- reindex_relation(OIDOldHeap, InvalidOid, reindex_flags, 0);
+ reindex_relation(OIDOldHeap, idxtableSpace, reindex_flags, 0);
/* Report that we are now doing clean up */
pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index e5a5eef102..40c57baccd 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -848,7 +848,8 @@ static void
refresh_by_heap_swap(Oid matviewOid, Oid OIDNewHeap, char relpersistence)
{
finish_heap_swap(matviewOid, OIDNewHeap, false, false, true, true,
- RecentXmin, ReadNextMultiXactId(), relpersistence);
+ RecentXmin, ReadNextMultiXactId(), relpersistence,
+ InvalidOid);
}
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 5f6a9fe3e2..fc05c194ef 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -4916,7 +4916,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
!OidIsValid(tab->newTableSpace),
RecentXmin,
ReadNextMultiXactId(),
- persistence);
+ persistence, InvalidOid);
}
else
{
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 005fb97a7b..34f72844a9 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -90,6 +90,7 @@ static void vac_truncate_clog(TransactionId frozenXID,
static bool vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params);
static double compute_parallel_delay(void);
static VacOptTernaryValue get_vacopt_ternary_value(DefElem *def);
+static Oid vacuum_get_tablespace_oid(VacuumParams *params, const char *tablespacename);
/*
* Primary entry point for manual VACUUM and ANALYZE commands
@@ -110,9 +111,9 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
bool parallel_option = false;
ListCell *lc;
- /* Name and Oid of tablespace to use for relations after VACUUM FULL. */
- char *tablespacename = NULL;
- Oid tablespaceOid = InvalidOid;
+ /* Tablespace to use for relations after VACUUM FULL. */
+ char *tablespacename = NULL,
+ *idxtablespacename = NULL;
/* Set default value */
params.index_cleanup = VACOPT_TERNARY_DEFAULT;
@@ -152,6 +153,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
params.truncate = get_vacopt_ternary_value(opt);
else if (strcmp(opt->defname, "tablespace") == 0)
tablespacename = defGetString(opt);
+ else if (strcmp(opt->defname, "index_tablespace") == 0)
+ idxtablespacename = defGetString(opt);
else if (strcmp(opt->defname, "parallel") == 0)
{
parallel_option = true;
@@ -214,25 +217,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
errmsg("cannot specify both FULL and PARALLEL options")));
/* Get tablespace Oid to use. */
- if (tablespacename)
- {
- if ((params.options & VACOPT_FULL) == 0)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("incompatible TABLESPACE option"),
- errdetail("You can only use TABLESPACE with VACUUM FULL.")));
-
- tablespaceOid = get_tablespace_oid(tablespacename, false);
-
- /* Can't move a non-shared relation into pg_global */
- if (tablespaceOid == GLOBALTABLESPACE_OID)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot move non-shared relation to tablespace \"%s\"",
- tablespacename)));
-
- }
- params.tablespace_oid = tablespaceOid;
+ params.tablespace_oid = vacuum_get_tablespace_oid(¶ms, tablespacename);
+ params.idxtablespace_oid = vacuum_get_tablespace_oid(¶ms, idxtablespacename);
/*
* Make sure VACOPT_ANALYZE is specified if any column lists are present.
@@ -1703,8 +1689,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
Relation onerel;
LockRelId onerelid;
Oid toast_relid,
- save_userid,
- tablespaceOid = InvalidOid;
+ save_userid;
int save_sec_context;
int save_nestlevel;
@@ -1852,8 +1837,6 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
RelationGetRelationName(onerel)),
errdetail("Cannot move system relation, only VACUUM is performed.")));
}
- else
- tablespaceOid = params->tablespace_oid;
/*
* Get a session-level lock too. This will protect our access to the
@@ -1924,7 +1907,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
cluster_options |= CLUOPT_VERBOSE;
/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
- cluster_rel(relid, InvalidOid, tablespaceOid, cluster_options);
+ cluster_rel(relid, InvalidOid, params->tablespace_oid,
+ params->idxtablespace_oid, cluster_options);
}
else
table_relation_vacuum(onerel, params, vac_strategy);
@@ -2152,3 +2136,30 @@ get_vacopt_ternary_value(DefElem *def)
{
return defGetBoolean(def) ? VACOPT_TERNARY_ENABLED : VACOPT_TERNARY_DISABLED;
}
+
+/* Get tablespace OID with vacuum-specific checks */
+static Oid
+vacuum_get_tablespace_oid(VacuumParams *params, const char *tablespacename)
+{
+ Oid ret;
+
+ if (tablespacename == NULL)
+ return InvalidOid;
+
+ if ((params->options & VACOPT_FULL) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("incompatible TABLESPACE option"),
+ errdetail("You can only use TABLESPACE with VACUUM FULL.")));
+
+ ret = get_tablespace_oid(tablespacename, false);
+
+ /* Can't move a non-shared relation into pg_global */
+ if (ret == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move non-shared relation to tablespace \"%s\"",
+ tablespacename)));
+
+ return ret;
+}
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 8acc321faa..7de5797f9c 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2898,6 +2898,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
tab->at_params.is_wraparound = wraparound;
tab->at_params.log_min_duration = log_min_duration;
tab->at_params.tablespace_oid = InvalidOid;
+ tab->at_params.idxtablespace_oid = InvalidOid;
tab->at_vacuum_cost_limit = vac_cost_limit;
tab->at_vacuum_cost_delay = vac_cost_delay;
tab->at_relname = NULL;
diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h
index bc9f881d8c..515e810505 100644
--- a/src/include/commands/cluster.h
+++ b/src/include/commands/cluster.h
@@ -20,7 +20,7 @@
extern void cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel);
-extern void cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, int options);
+extern void cluster_rel(Oid tableOid, Oid indexOid, Oid tablespaceOid, Oid indextablespaceOid, int options);
extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid,
bool recheck, LOCKMODE lockmode);
extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal);
@@ -34,6 +34,7 @@ extern void finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
bool is_internal,
TransactionId frozenXid,
MultiXactId minMulti,
- char newrelpersistence);
+ char newrelpersistence,
+ Oid idxtablespaceOid);
#endif /* CLUSTER_H */
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 6758e9812f..d6f8e5de23 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -229,8 +229,10 @@ typedef struct VacuumParams
* disabled.
*/
int nworkers;
- Oid tablespace_oid; /* tablespace Oid to use for relations
- * after VACUUM FULL */
+
+ /* tablespace Oids to use for relations rebuilt by VACUUM FULL */
+ Oid tablespace_oid;
+ Oid idxtablespace_oid;
} VacuumParams;
/* GUC parameters */
--
2.17.0
