On Wed, Sep 23, 2020 at 07:43:01PM +0300, Alexey Kondratov wrote:
> On 2020-09-09 18:36, Justin Pryzby wrote:
> > Rebased on a6642b3ae: Add support for partitioned tables and indexes in
> > REINDEX
> >
> > So now this includes the new functionality and test for reindexing a
> > partitioned table onto a new tablespace. That part could use some
> > additional
> > review.
>
> I have finally had a look on your changes regarding partitioned tables.
>
> +set_rel_tablespace(Oid indexid, char *tablespace)
> +{
> + Oid tablespaceOid = tablespace ? get_tablespace_oid(tablespace, false) :
> + InvalidOid;
>
> You pass a tablespace name to set_rel_tablespace(), but it is already parsed
> into the Oid before. So I do not see why we need this extra work here
> instead of just passing Oid directly.
>
> Also set_rel_tablespace() does not check for a no-op case, i.e. if requested
> tablespace is the same as before.
>
> + /*
> + * 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)
>
> Just noticed that this check is not completely correct as well, since it
> does not check for MyDatabaseTableSpace (stored as InvalidOid) logic.
>
> I put these small fixes directly into the attached 0003.
>
> Also, I thought about your comment above set_rel_tablespace() and did a bit
> 'extreme' refactoring, which is attached as a separated patch 0004. The only
> one doubtful change IMO is reordering of RelationDropStorage() operation
> inside reindex_index(). However, it only schedules unlinking of physical
> storage at transaction commit, so this refactoring seems to be safe.
>
> If there will be no objections I would merge it with 0003.
>
> On 2020-09-09 16:03, Alexey Kondratov wrote:
> > On 2020-09-09 15:22, Michael Paquier wrote:
> > >
> > > By the way, skimming through the patch set, I was wondering if we
> > > could do the refactoring of patch 0005 as a first step
> > >
> >
> > Yes, I did it with intention to put as a first patch, but wanted to
> > get some feedback. It's easier to refactor the last patch without
> > rebasing others.
> >
> > >
> > > until I
> > > noticed this part:
> > > +common_option_name:
> > > NonReservedWord { $$ = $1; }
> > > | analyze_keyword { $$ = "analyze"; }
> > > This is not a good idea as you make ANALYZE an option available for
> > > all the commands involved in the refactoring. A portion of that could
> > > be considered though, like the use of common_option_arg.
> > >
> >
> > From the grammar perspective ANY option is available for any command
> > that uses parenthesized option list. All the checks and validations
> > are performed at the corresponding command code.
> > This analyze_keyword is actually doing only an ANALYZE word
> > normalization if it's used as an option. Why it could be harmful?
> >
>
> Michael has not replied since then, but he was relatively positive about
> 0005 initially, so I put it as a first patch now.
Thanks. I rebased Alexey's latest patch on top of recent changes to cluster.c.
This puts the generic grammar changes first. I wasn't paying much attention to
that part, so still waiting for a committer review.
--
Justin
>From 72f7af2b39587304c945e75d2b82a3a09a2cf7fa Mon Sep 17 00:00:00 2001
From: Alexey Kondratov <kondratov.alek...@gmail.com>
Date: Wed, 2 Sep 2020 23:05:16 +0300
Subject: [PATCH v29 1/7] Refactor gram.y in order to add a common
parenthesized option list
Previously there were two identical option lists
(explain_option_list and vac_analyze_option_list) + very similar
reindex_option_list. It does not seem to make
sense to maintain identical option lists in the grammar, since
all new options are added and parsed in the backend code.
That way, new common_option_list added in order to replace all
explain_option_list, vac_analyze_option_list and probably
also reindex_option_list.
---
src/backend/parser/gram.y | 61 +++++++++------------------------------
1 file changed, 14 insertions(+), 47 deletions(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 480d168346..0828c27944 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -315,10 +315,10 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
create_extension_opt_item alter_extension_opt_item
%type <ival> opt_lock lock_type cast_context
-%type <str> vac_analyze_option_name
-%type <defelt> vac_analyze_option_elem
-%type <list> vac_analyze_option_list
-%type <node> vac_analyze_option_arg
+%type <str> common_option_name
+%type <defelt> common_option_elem
+%type <list> common_option_list
+%type <node> common_option_arg
%type <defelt> drop_option
%type <boolean> opt_or_replace opt_no
opt_grant_grant_option opt_grant_admin_option
@@ -513,10 +513,6 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> generic_option_arg
%type <defelt> generic_option_elem alter_generic_option_elem
%type <list> generic_option_list alter_generic_option_list
-%type <str> explain_option_name
-%type <node> explain_option_arg
-%type <defelt> explain_option_elem
-%type <list> explain_option_list
%type <ival> reindex_target_type reindex_target_multitable
%type <ival> reindex_option_list reindex_option_elem
@@ -10485,7 +10481,7 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose opt_analyze opt_vacuum_relati
n->is_vacuumcmd = true;
$$ = (Node *)n;
}
- | VACUUM '(' vac_analyze_option_list ')' opt_vacuum_relation_list
+ | VACUUM '(' common_option_list ')' opt_vacuum_relation_list
{
VacuumStmt *n = makeNode(VacuumStmt);
n->options = $3;
@@ -10506,7 +10502,7 @@ AnalyzeStmt: analyze_keyword opt_verbose opt_vacuum_relation_list
n->is_vacuumcmd = false;
$$ = (Node *)n;
}
- | analyze_keyword '(' vac_analyze_option_list ')' opt_vacuum_relation_list
+ | analyze_keyword '(' common_option_list ')' opt_vacuum_relation_list
{
VacuumStmt *n = makeNode(VacuumStmt);
n->options = $3;
@@ -10516,12 +10512,12 @@ AnalyzeStmt: analyze_keyword opt_verbose opt_vacuum_relation_list
}
;
-vac_analyze_option_list:
- vac_analyze_option_elem
+common_option_list:
+ common_option_elem
{
$$ = list_make1($1);
}
- | vac_analyze_option_list ',' vac_analyze_option_elem
+ | common_option_list ',' common_option_elem
{
$$ = lappend($1, $3);
}
@@ -10532,19 +10528,19 @@ analyze_keyword:
| ANALYSE /* British */ {}
;
-vac_analyze_option_elem:
- vac_analyze_option_name vac_analyze_option_arg
+common_option_elem:
+ common_option_name common_option_arg
{
$$ = makeDefElem($1, $2, @1);
}
;
-vac_analyze_option_name:
+common_option_name:
NonReservedWord { $$ = $1; }
| analyze_keyword { $$ = "analyze"; }
;
-vac_analyze_option_arg:
+common_option_arg:
opt_boolean_or_string { $$ = (Node *) makeString($1); }
| NumericOnly { $$ = (Node *) $1; }
| /* EMPTY */ { $$ = NULL; }
@@ -10626,7 +10622,7 @@ ExplainStmt:
n->options = list_make1(makeDefElem("verbose", NULL, @2));
$$ = (Node *) n;
}
- | EXPLAIN '(' explain_option_list ')' ExplainableStmt
+ | EXPLAIN '(' common_option_list ')' ExplainableStmt
{
ExplainStmt *n = makeNode(ExplainStmt);
n->query = $5;
@@ -10647,35 +10643,6 @@ ExplainableStmt:
| ExecuteStmt /* by default all are $$=$1 */
;
-explain_option_list:
- explain_option_elem
- {
- $$ = list_make1($1);
- }
- | explain_option_list ',' explain_option_elem
- {
- $$ = lappend($1, $3);
- }
- ;
-
-explain_option_elem:
- explain_option_name explain_option_arg
- {
- $$ = makeDefElem($1, $2, @1);
- }
- ;
-
-explain_option_name:
- NonReservedWord { $$ = $1; }
- | analyze_keyword { $$ = "analyze"; }
- ;
-
-explain_option_arg:
- opt_boolean_or_string { $$ = (Node *) makeString($1); }
- | NumericOnly { $$ = (Node *) $1; }
- | /* EMPTY */ { $$ = NULL; }
- ;
-
/*****************************************************************************
*
* QUERY:
--
2.17.0
>From 81ac812b79b13e7a0b525406b3139bdd0cfa1ea0 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Fri, 27 Mar 2020 17:50:46 -0500
Subject: [PATCH v29 2/7] Change REINDEX/CLUSTER to accept an option list..
..like EXPLAIN (..), VACUUM (..), and ANALYZE (..).
Change docs in the style of VACUUM. See also: 52dcfda48778d16683c64ca4372299a099a15b96
---
doc/src/sgml/ref/cluster.sgml | 27 ++++++++++++++---
doc/src/sgml/ref/reindex.sgml | 43 ++++++++++++++++++---------
src/backend/commands/cluster.c | 28 ++++++++++++++++--
src/backend/nodes/copyfuncs.c | 4 +--
src/backend/nodes/equalfuncs.c | 4 +--
src/backend/parser/gram.y | 54 +++++++++++++++++++---------------
src/backend/tcop/utility.c | 44 ++++++++++++++++++++++-----
src/bin/psql/tab-complete.c | 22 ++++++++++----
src/include/commands/cluster.h | 3 +-
src/include/nodes/parsenodes.h | 4 +--
10 files changed, 169 insertions(+), 64 deletions(-)
diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml
index b9450e7366..efd8165e35 100644
--- a/doc/src/sgml/ref/cluster.sgml
+++ b/doc/src/sgml/ref/cluster.sgml
@@ -21,8 +21,13 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CLUSTER [VERBOSE] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">index_name</replaceable> ]
-CLUSTER [VERBOSE]
+CLUSTER [VERBOSE] [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">index_name</replaceable> ]
+CLUSTER [VERBOSE] [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ]
+
+<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
+
+ VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
+
</synopsis>
</refsynopsisdiv>
@@ -81,6 +86,15 @@ CLUSTER [VERBOSE]
<title>Parameters</title>
<variablelist>
+ <varlistentry>
+ <term><literal>VERBOSE</literal></term>
+ <listitem>
+ <para>
+ Prints a progress report as each table is clustered.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">table_name</replaceable></term>
<listitem>
@@ -100,10 +114,15 @@ CLUSTER [VERBOSE]
</varlistentry>
<varlistentry>
- <term><literal>VERBOSE</literal></term>
+ <term><replaceable class="parameter">boolean</replaceable></term>
<listitem>
<para>
- Prints a progress report as each table is clustered.
+ Specifies whether the selected option should be turned on or off.
+ You can write <literal>TRUE</literal>, <literal>ON</literal>, or
+ <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
+ <literal>OFF</literal>, or <literal>0</literal> to disable it. The
+ <replaceable class="parameter">boolean</replaceable> value can also
+ be omitted, in which case <literal>TRUE</literal> is assumed.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index fa43e3a972..62bb6e9d55 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -25,7 +25,7 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
- VERBOSE
+ VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
</synopsis>
</refsynopsisdiv>
@@ -145,19 +145,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>
@@ -185,6 +172,34 @@ 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>
+
+ <varlistentry>
+ <term><replaceable class="parameter">boolean</replaceable></term>
+ <listitem>
+ <para>
+ Specifies whether the selected option should be turned on or off.
+ You can write <literal>TRUE</literal>, <literal>ON</literal>, or
+ <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
+ <literal>OFF</literal>, or <literal>0</literal> to disable it. The
+ <replaceable class="parameter">boolean</replaceable> value can also
+ be omitted, in which case <literal>TRUE</literal> is assumed.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect1>
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 04d12a7ece..5e85fbacad 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,29 @@ static List *get_tables_to_cluster(MemoryContext cluster_context);
*---------------------------------------------------------------------------
*/
void
-cluster(ClusterStmt *stmt, bool isTopLevel)
+cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
{
+ ListCell *lc;
+ int options = 0;
+
+ /* Parse list of generic parameters not handled by the parser */
+ foreach(lc, stmt->params)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ if (strcmp(opt->defname, "verbose") == 0)
+ if (defGetBoolean(opt))
+ options |= CLUOPT_VERBOSE;
+ else
+ options &= ~CLUOPT_VERBOSE;
+ 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. */
@@ -170,7 +192,7 @@ cluster(ClusterStmt *stmt, bool isTopLevel)
table_close(rel, NoLock);
/* Do the job. */
- cluster_rel(tableOid, indexOid, stmt->options);
+ cluster_rel(tableOid, indexOid, options);
}
else
{
@@ -219,7 +241,7 @@ cluster(ClusterStmt *stmt, bool isTopLevel)
PushActiveSnapshot(GetTransactionSnapshot());
/* Do the job. */
cluster_rel(rvtc->tableOid, rvtc->indexOid,
- stmt->options | CLUOPT_RECHECK);
+ options | CLUOPT_RECHECK);
PopActiveSnapshot();
CommitTransactionCommand();
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 2b4d7654cc..4a72531340 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3355,7 +3355,7 @@ _copyClusterStmt(const ClusterStmt *from)
COPY_NODE_FIELD(relation);
COPY_STRING_FIELD(indexname);
- COPY_SCALAR_FIELD(options);
+ COPY_NODE_FIELD(params);
return newnode;
}
@@ -4447,7 +4447,7 @@ _copyReindexStmt(const ReindexStmt *from)
COPY_SCALAR_FIELD(kind);
COPY_NODE_FIELD(relation);
COPY_STRING_FIELD(name);
- COPY_SCALAR_FIELD(options);
+ COPY_NODE_FIELD(params);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index e2d1b987bf..3c6c40a50d 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1216,7 +1216,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;
}
@@ -2134,7 +2134,7 @@ _equalReindexStmt(const ReindexStmt *a, const ReindexStmt *b)
COMPARE_SCALAR_FIELD(kind);
COMPARE_NODE_FIELD(relation);
COMPARE_STRING_FIELD(name);
- COMPARE_SCALAR_FIELD(options);
+ COMPARE_NODE_FIELD(params);
return true;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0828c27944..ea371b8d6b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -515,7 +515,6 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> generic_option_list alter_generic_option_list
%type <ival> reindex_target_type reindex_target_multitable
-%type <ival> reindex_option_list reindex_option_elem
%type <node> copy_generic_opt_arg copy_generic_opt_arg_list_item
%type <defelt> copy_generic_opt_elem
@@ -8203,9 +8202,10 @@ ReindexStmt:
n->kind = $2;
n->relation = $4;
n->name = NULL;
- n->options = 0;
+ n->params = NIL;
if ($3)
- n->options |= REINDEXOPT_CONCURRENTLY;
+ n->params = lappend(n->params,
+ makeDefElem("concurrently", NULL, @3));
$$ = (Node *)n;
}
| REINDEX reindex_target_multitable opt_concurrently name
@@ -8214,31 +8214,34 @@ ReindexStmt:
n->kind = $2;
n->name = $4;
n->relation = NULL;
- n->options = 0;
+ n->params = NIL;
if ($3)
- n->options |= REINDEXOPT_CONCURRENTLY;
+ n->params = lappend(n->params,
+ makeDefElem("concurrently", NULL, @3));
$$ = (Node *)n;
}
- | REINDEX '(' reindex_option_list ')' reindex_target_type opt_concurrently qualified_name
+ | REINDEX '(' common_option_list ')' reindex_target_type opt_concurrently qualified_name
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $5;
n->relation = $7;
n->name = NULL;
- n->options = $3;
+ n->params = $3;
if ($6)
- n->options |= REINDEXOPT_CONCURRENTLY;
+ n->params = lappend(n->params,
+ makeDefElem("concurrently", NULL, @6));
$$ = (Node *)n;
}
- | REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name
+ | REINDEX '(' common_option_list ')' reindex_target_multitable opt_concurrently name
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $5;
n->name = $7;
n->relation = NULL;
- n->options = $3;
+ n->params = $3;
if ($6)
- n->options |= REINDEXOPT_CONCURRENTLY;
+ n->params = lappend(n->params,
+ makeDefElem("concurrently", NULL, @6));
$$ = (Node *)n;
}
;
@@ -8251,13 +8254,6 @@ reindex_target_multitable:
| SYSTEM_P { $$ = REINDEX_OBJECT_SYSTEM; }
| DATABASE { $$ = REINDEX_OBJECT_DATABASE; }
;
-reindex_option_list:
- reindex_option_elem { $$ = $1; }
- | reindex_option_list ',' reindex_option_elem { $$ = $1 | $3; }
- ;
-reindex_option_elem:
- VERBOSE { $$ = REINDEXOPT_VERBOSE; }
- ;
/*****************************************************************************
*
@@ -10408,6 +10404,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)
*
@@ -10419,9 +10416,18 @@ ClusterStmt:
ClusterStmt *n = makeNode(ClusterStmt);
n->relation = $3;
n->indexname = $4;
- n->options = 0;
+ n->params = NIL;
if ($2)
- n->options |= CLUOPT_VERBOSE;
+ n->params = lappend(n->params, makeDefElem("verbose", NULL, @2));
+ $$ = (Node*)n;
+ }
+
+ | CLUSTER '(' common_option_list ')' qualified_name cluster_index_specification
+ {
+ ClusterStmt *n = makeNode(ClusterStmt);
+ n->relation = $5;
+ n->indexname = $6;
+ n->params = $3;
$$ = (Node*)n;
}
| CLUSTER opt_verbose
@@ -10429,9 +10435,9 @@ ClusterStmt:
ClusterStmt *n = makeNode(ClusterStmt);
n->relation = NULL;
n->indexname = NULL;
- n->options = 0;
+ n->params = NIL;
if ($2)
- n->options |= CLUOPT_VERBOSE;
+ n->params = lappend(n->params, makeDefElem("verbose", NULL, @2));
$$ = (Node*)n;
}
/* kept for pre-8.3 compatibility */
@@ -10440,9 +10446,9 @@ ClusterStmt:
ClusterStmt *n = makeNode(ClusterStmt);
n->relation = $5;
n->indexname = $3;
- n->options = 0;
+ n->params = NIL;
if ($2)
- n->options |= CLUOPT_VERBOSE;
+ n->params = lappend(n->params, makeDefElem("verbose", NULL, @2));
$$ = (Node*)n;
}
;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 9a35147b26..61cffcef84 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -526,6 +526,36 @@ ProcessUtility(PlannedStmt *pstmt,
dest, qc);
}
+/* Parse params not parsed by the grammar */
+static
+void parse_reindex_params(ParseState *pstate, ReindexStmt *stmt, int *options)
+{
+ ListCell *lc;
+ foreach(lc, stmt->params)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ if (strcmp(opt->defname, "verbose") == 0)
+ {
+ if (defGetBoolean(opt))
+ *options |= REINDEXOPT_VERBOSE;
+ else
+ *options &= ~REINDEXOPT_VERBOSE;
+ }
+ else if (strcmp(opt->defname, "concurrently") == 0)
+ if (defGetBoolean(opt))
+ *options |= REINDEXOPT_CONCURRENTLY;
+ else
+ *options &= ~REINDEXOPT_CONCURRENTLY;
+ 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
@@ -818,7 +848,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
break;
case T_ClusterStmt:
- cluster((ClusterStmt *) parsetree, isTopLevel);
+ cluster(pstate, (ClusterStmt *) parsetree, isTopLevel);
break;
case T_VacuumStmt:
@@ -918,20 +948,20 @@ standard_ProcessUtility(PlannedStmt *pstmt,
case T_ReindexStmt:
{
ReindexStmt *stmt = (ReindexStmt *) parsetree;
+ int options = 0;
- if ((stmt->options & REINDEXOPT_CONCURRENTLY) != 0)
+ parse_reindex_params(pstate, stmt, &options);
+ if (options & REINDEXOPT_CONCURRENTLY)
PreventInTransactionBlock(isTopLevel,
"REINDEX CONCURRENTLY");
switch (stmt->kind)
{
case REINDEX_OBJECT_INDEX:
- ReindexIndex(stmt->relation, stmt->options,
- isTopLevel);
+ ReindexIndex(stmt->relation, options, isTopLevel);
break;
case REINDEX_OBJECT_TABLE:
- ReindexTable(stmt->relation, stmt->options,
- isTopLevel);
+ ReindexTable(stmt->relation, options, isTopLevel);
break;
case REINDEX_OBJECT_SCHEMA:
case REINDEX_OBJECT_SYSTEM:
@@ -947,7 +977,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
(stmt->kind == REINDEX_OBJECT_SCHEMA) ? "REINDEX SCHEMA" :
(stmt->kind == REINDEX_OBJECT_SYSTEM) ? "REINDEX SYSTEM" :
"REINDEX DATABASE");
- ReindexMultipleTables(stmt->name, stmt->kind, stmt->options);
+ ReindexMultipleTables(stmt->name, stmt->kind, options);
break;
default:
elog(ERROR, "unrecognized object type: %d",
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b2b4f1fd4d..3fdf5018ee 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2262,21 +2262,33 @@ psql_completion(const char *text, int start, int end)
/* CLUSTER */
else if (Matches("CLUSTER"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_clusterables, "UNION SELECT 'VERBOSE'");
- else if (Matches("CLUSTER", "VERBOSE"))
+ else if (Matches("CLUSTER", "VERBOSE") ||
+ Matches("CLUSTER", "(*)"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_clusterables, NULL);
/* If we have CLUSTER <sth>, then add "USING" */
- else if (Matches("CLUSTER", MatchAnyExcept("VERBOSE|ON")))
+ else if (Matches("CLUSTER", MatchAnyExcept("VERBOSE|ON|(|(*)")))
COMPLETE_WITH("USING");
/* If we have CLUSTER VERBOSE <sth>, then add "USING" */
- else if (Matches("CLUSTER", "VERBOSE", MatchAny))
+ else if (Matches("CLUSTER", "VERBOSE|(*)", MatchAny))
COMPLETE_WITH("USING");
/* If we have CLUSTER <sth> USING, then add the index as well */
else if (Matches("CLUSTER", MatchAny, "USING") ||
- Matches("CLUSTER", "VERBOSE", MatchAny, "USING"))
+ Matches("CLUSTER", "VERBOSE|(*)", MatchAny, "USING"))
{
completion_info_charp = prev2_wd;
COMPLETE_WITH_QUERY(Query_for_index_of_table);
}
+ else if (HeadMatches("CLUSTER", "(*") &&
+ !HeadMatches("CLUSTER", "(*)"))
+ {
+ /*
+ * This fires if we're in an unfinished parenthesized option list.
+ * get_previous_words treats a completed parenthesized option list as
+ * one word, so the above test is correct.
+ */
+ if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
+ COMPLETE_WITH("VERBOSE");
+ }
/* COMMENT */
else if (Matches("COMMENT"))
@@ -3493,7 +3505,7 @@ psql_completion(const char *text, int start, int end)
* one word, so the above test is correct.
*/
if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
- COMPLETE_WITH("VERBOSE");
+ COMPLETE_WITH("CONCURRENTLY", "VERBOSE");
}
/* SECURITY LABEL */
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/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ff584f2955..5e403870a8 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3215,7 +3215,7 @@ typedef struct ClusterStmt
NodeTag type;
RangeVar *relation; /* relation being indexed, or NULL if all */
char *indexname; /* original index defined */
- int options; /* OR of ClusterOption flags */
+ List *params; /* list of DefElem nodes */
} ClusterStmt;
/* ----------------------
@@ -3376,7 +3376,7 @@ 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 *params; /* list of DefElem nodes */
} ReindexStmt;
/* ----------------------
--
2.17.0
>From d4e78ca875c1970803d563f25073bbd8f6087ee4 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 v29 3/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 | 22 ++++
src/backend/catalog/index.c | 103 ++++++++++++++-
src/backend/commands/cluster.c | 2 +-
src/backend/commands/indexcmds.c | 152 ++++++++++++++++++----
src/backend/commands/tablecmds.c | 2 +-
src/backend/tcop/utility.c | 15 ++-
src/bin/psql/tab-complete.c | 4 +-
src/include/catalog/index.h | 5 +-
src/include/commands/defrem.h | 6 +-
src/test/regress/input/tablespace.source | 53 ++++++++
src/test/regress/output/tablespace.source | 102 +++++++++++++++
11 files changed, 425 insertions(+), 41 deletions(-)
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 62bb6e9d55..3e17cbe19b 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -26,6 +26,7 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
+ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -164,6 +165,19 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>TABLESPACE</literal></term>
+ <listitem>
+ <para>
+ 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>
+ will be generated.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>VERBOSE</literal></term>
<listitem>
@@ -200,6 +214,14 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
</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/catalog/index.c b/src/backend/catalog/index.c
index 0974f3e23a..bee68abbd2 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -56,6 +56,7 @@
#include "commands/event_trigger.h"
#include "commands/progress.h"
#include "commands/tablecmds.h"
+#include "commands/tablespace.h"
#include "commands/trigger.h"
#include "executor/executor.h"
#include "miscadmin.h"
@@ -1232,9 +1233,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,
@@ -1364,7 +1369,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,
@@ -3421,18 +3427,22 @@ 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)
+ int options, Oid tablespaceOid)
{
Relation iRel,
heapRelation;
Oid heapId;
+ Oid oldTablespaceOid;
IndexInfo *indexInfo;
volatile bool skipped_constraint = false;
PGRUsage ru0;
bool progress = (options & REINDEXOPT_REPORT_PROGRESS) != 0;
+ bool set_tablespace = OidIsValid(tablespaceOid);
pg_rusage_init(&ru0);
@@ -3484,6 +3494,35 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
get_namespace_name(RelationGetNamespace(iRel)),
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))));
+
+ /* It's not a shared catalog, so refuse to move it to shared tablespace */
+ if (tablespaceOid == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move non-shared relation to tablespace \"%s\"",
+ get_tablespace_name(tablespaceOid))));
+
+
/*
* Don't allow reindex on temp tables of other backends ... their local
* buffer manager is not going to cope.
@@ -3510,6 +3549,51 @@ 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.
+ */
+ oldTablespaceOid = iRel->rd_rel->reltablespace;
+ if (set_tablespace &&
+ (tablespaceOid != oldTablespaceOid ||
+ (tablespaceOid == MyDatabaseTableSpace && OidIsValid(oldTablespaceOid))))
+ {
+ 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);
+
+ RelationAssumeNewRelfilenode(iRel);
+
+ /* 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.
@@ -3641,6 +3725,9 @@ 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" is the tablespace where the relation's indexes will be
+ * rebuilt, or InvalidOid to keep each index on its current tablespace.
+ *
* "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).
@@ -3673,7 +3760,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, int flags, int options, Oid tablespaceOid)
{
Relation rel;
Oid toast_relid;
@@ -3762,7 +3849,7 @@ reindex_relation(Oid relid, int flags, int options)
}
reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
- persistence, options);
+ persistence, options, tablespaceOid);
CommandCounterIncrement();
@@ -3791,9 +3878,13 @@ reindex_relation(Oid relid, int flags, int options)
/*
* Note that this should fail if the toast relation is missing, so
* reset REINDEXOPT_MISSING_OK.
+ *
+ * Even if table was moved to new tablespace, normally toast cannot move.
*/
+ Oid toasttablespaceOid = allowSystemTableMods ? tablespaceOid : InvalidOid;
result |= reindex_relation(toast_relid, flags,
- options & ~(REINDEXOPT_MISSING_OK));
+ options & ~(REINDEXOPT_MISSING_OK),
+ toasttablespaceOid);
}
return result;
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 5e85fbacad..8ababbeb14 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -1412,7 +1412,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, reindex_flags, 0, InvalidOid);
/* 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 75552c64ed..516feeb402 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -87,10 +87,10 @@ 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, int options, Oid tablespaceOid);
-static void ReindexPartitions(Oid relid, int options, bool isTopLevel);
-static void ReindexMultipleInternal(List *relids, int options);
+static void ReindexPartitions(Oid relid, int options, bool isTopLevel, char *tablespace);
+static void ReindexMultipleInternal(List *relids, int options, Oid tablespaceOid);
static void reindex_error_callback(void *args);
static void update_relispartition(Oid relationId, bool newval);
static bool CompareOpclassOptions(Datum *opts1, Datum *opts2, int natts);
@@ -2432,10 +2432,12 @@ ChooseIndexColumnNames(List *indexElems)
* Recreate a specific index.
*/
void
-ReindexIndex(RangeVar *indexRelation, int options, bool isTopLevel)
+ReindexIndex(RangeVar *indexRelation, int options, bool isTopLevel, char *tablespace)
{
struct ReindexIndexCallbackState state;
Oid indOid;
+ Oid tablespaceOid = tablespace ?
+ get_tablespace_oid(tablespace, false) : InvalidOid;
char persistence;
char relkind;
@@ -2466,13 +2468,14 @@ ReindexIndex(RangeVar *indexRelation, int options, bool isTopLevel)
relkind = get_rel_relkind(indOid);
if (relkind == RELKIND_PARTITIONED_INDEX)
- ReindexPartitions(indOid, options, isTopLevel);
+ ReindexPartitions(indOid, options, isTopLevel, tablespace);
else if ((options & REINDEXOPT_CONCURRENTLY) != 0 &&
persistence != RELPERSISTENCE_TEMP)
- ReindexRelationConcurrently(indOid, options);
+ ReindexRelationConcurrently(indOid, options, tablespaceOid);
else
reindex_index(indOid, false, persistence,
- options | REINDEXOPT_REPORT_PROGRESS);
+ options | REINDEXOPT_REPORT_PROGRESS,
+ tablespaceOid);
}
/*
@@ -2551,10 +2554,12 @@ RangeVarCallbackForReindexIndex(const RangeVar *relation,
* Recreate all indexes of a table (and of its toast table, if any)
*/
Oid
-ReindexTable(RangeVar *relation, int options, bool isTopLevel)
+ReindexTable(RangeVar *relation, int options, bool isTopLevel, char *tablespace)
{
Oid heapOid;
bool result;
+ Oid tablespaceOid = tablespace ?
+ get_tablespace_oid(tablespace, false) : InvalidOid;
/*
* The lock level used here should match reindex_relation().
@@ -2571,11 +2576,11 @@ ReindexTable(RangeVar *relation, int options, bool isTopLevel)
RangeVarCallbackOwnsTable, NULL);
if (get_rel_relkind(heapOid) == RELKIND_PARTITIONED_TABLE)
- ReindexPartitions(heapOid, options, isTopLevel);
+ ReindexPartitions(heapOid, options, isTopLevel, tablespace);
else if ((options & REINDEXOPT_CONCURRENTLY) != 0 &&
get_rel_persistence(heapOid) != RELPERSISTENCE_TEMP)
{
- result = ReindexRelationConcurrently(heapOid, options);
+ result = ReindexRelationConcurrently(heapOid, options, tablespaceOid);
if (!result)
ereport(NOTICE,
@@ -2587,7 +2592,8 @@ ReindexTable(RangeVar *relation, int options, bool isTopLevel)
result = reindex_relation(heapOid,
REINDEX_REL_PROCESS_TOAST |
REINDEX_REL_CHECK_CONSTRAINTS,
- options | REINDEXOPT_REPORT_PROGRESS);
+ options | REINDEXOPT_REPORT_PROGRESS,
+ tablespaceOid);
if (!result)
ereport(NOTICE,
(errmsg("table \"%s\" has no indexes to reindex",
@@ -2607,7 +2613,7 @@ ReindexTable(RangeVar *relation, int options, bool isTopLevel)
*/
void
ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
- int options)
+ int options, char *tablespace)
{
Oid objectOid;
Relation relationRelation;
@@ -2618,7 +2624,12 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
MemoryContext old;
List *relids = NIL;
int num_keys;
+
bool concurrent_warning = false;
+ bool tablespace_warning = false;
+ bool mapped_warning = false;
+ Oid tablespaceOid = tablespace ?
+ tablespaceOid = get_tablespace_oid(tablespace, false) : InvalidOid;
AssertArg(objectName);
Assert(objectKind == REINDEX_OBJECT_SCHEMA ||
@@ -2745,6 +2756,35 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
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 if TABLESPACE is specified.
+ * OidIsValid(relfilenode) checks that, similar to
+ * RelationIsMapped().
+ */
+ if (!mapped_warning)
+ ereport(WARNING,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot change tablespace of indexes on mapped relations, skipping all")));
+ mapped_warning = true;
+ continue;
+ }
+ }
+
/* Save the list of relation OIDs in private context */
old = MemoryContextSwitchTo(private_context);
@@ -2769,7 +2809,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
* Process each relation listed in a separate transaction. Note that this
* commits and then starts a new transaction immediately.
*/
- ReindexMultipleInternal(relids, options);
+ ReindexMultipleInternal(relids, options, tablespaceOid);
MemoryContextDelete(private_context);
}
@@ -2793,6 +2833,41 @@ reindex_error_callback(void *arg)
errinfo->relnamespace, errinfo->relname);
}
+/*
+ * This is mostly duplicating ATExecSetTableSpaceNoStorage,
+ * which should maybe be factored out to a library function.
+ */
+static void
+set_rel_tablespace(Oid reloid, Oid tablespaceOid)
+{
+ Relation pg_class;
+ HeapTuple tuple;
+ Form_pg_class rd_rel;
+ Oid oldTablespaceOid;
+
+ /* Get a modifiable copy of the relation's pg_class row */
+ pg_class = table_open(RelationRelationId, RowExclusiveLock);
+
+ tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(reloid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u", reloid);
+ rd_rel = (Form_pg_class) GETSTRUCT(tuple);
+
+ /* No work if no change in tablespace. */
+ oldTablespaceOid = rd_rel->reltablespace;
+ if (tablespaceOid != oldTablespaceOid ||
+ (tablespaceOid == MyDatabaseTableSpace && OidIsValid(oldTablespaceOid)))
+ {
+ /* Update the pg_class row */
+ rd_rel->reltablespace = (tablespaceOid == MyDatabaseTableSpace) ?
+ InvalidOid : tablespaceOid;
+ CatalogTupleUpdate(pg_class, &tuple->t_self, tuple);
+ }
+
+ heap_freetuple(tuple);
+ table_close(pg_class, RowExclusiveLock);
+}
+
/*
* ReindexPartitions
*
@@ -2800,7 +2875,7 @@ reindex_error_callback(void *arg)
* by the caller.
*/
static void
-ReindexPartitions(Oid relid, int options, bool isTopLevel)
+ReindexPartitions(Oid relid, int options, bool isTopLevel, char *tablespace)
{
List *partitions = NIL;
char relkind = get_rel_relkind(relid);
@@ -2811,6 +2886,8 @@ ReindexPartitions(Oid relid, int options, bool isTopLevel)
ListCell *lc;
ErrorContextCallback errcallback;
ReindexErrorInfo errinfo;
+ Oid tablespaceOid = tablespace ?
+ get_tablespace_oid(tablespace, false) : InvalidOid;
Assert(relkind == RELKIND_PARTITIONED_INDEX ||
relkind == RELKIND_PARTITIONED_TABLE);
@@ -2858,9 +2935,27 @@ ReindexPartitions(Oid relid, int options, bool isTopLevel)
MemoryContext old_context;
/*
- * This discards partitioned tables, partitioned indexes and foreign
- * tables.
+ * Foreign tables and partitioned relations are not themselves
+ * reindexed - leaf partitions are processed directly. But any
+ * tablespace change is recorded in the catalog for partitioned
+ * relations.
*/
+ if (partkind == RELKIND_PARTITIONED_INDEX)
+ set_rel_tablespace(partoid, tablespaceOid);
+ else if (partkind == RELKIND_PARTITIONED_TABLE)
+ {
+ Relation rel = table_open(partoid, ShareLock);
+ List *indexIds = RelationGetIndexList(rel);
+ ListCell *lc;
+
+ table_close(rel, NoLock);
+ foreach (lc, indexIds)
+ {
+ Oid indexid = lfirst_oid(lc);
+ set_rel_tablespace(indexid, tablespaceOid);
+ }
+ }
+
if (!RELKIND_HAS_STORAGE(partkind))
continue;
@@ -2877,7 +2972,7 @@ ReindexPartitions(Oid relid, int options, bool isTopLevel)
* Process each partition listed in a separate transaction. Note that
* this commits and then starts a new transaction immediately.
*/
- ReindexMultipleInternal(partitions, options);
+ ReindexMultipleInternal(partitions, options, tablespaceOid);
/*
* Clean up working storage --- note we must do this after
@@ -2895,7 +2990,7 @@ ReindexPartitions(Oid relid, int options, bool isTopLevel)
* and starts a new transaction when finished.
*/
static void
-ReindexMultipleInternal(List *relids, int options)
+ReindexMultipleInternal(List *relids, int options, Oid tablespaceOid)
{
ListCell *l;
@@ -2936,7 +3031,8 @@ ReindexMultipleInternal(List *relids, int options)
{
(void) ReindexRelationConcurrently(relid,
options |
- REINDEXOPT_MISSING_OK);
+ REINDEXOPT_MISSING_OK,
+ tablespaceOid);
/* ReindexRelationConcurrently() does the verbose output */
}
else if (relkind == RELKIND_INDEX)
@@ -2944,7 +3040,7 @@ ReindexMultipleInternal(List *relids, int options)
reindex_index(relid, false, relpersistence,
options |
REINDEXOPT_REPORT_PROGRESS |
- REINDEXOPT_MISSING_OK);
+ REINDEXOPT_MISSING_OK, tablespaceOid);
PopActiveSnapshot();
/* reindex_index() does the verbose output */
}
@@ -2957,7 +3053,8 @@ ReindexMultipleInternal(List *relids, int options)
REINDEX_REL_CHECK_CONSTRAINTS,
options |
REINDEXOPT_REPORT_PROGRESS |
- REINDEXOPT_MISSING_OK);
+ REINDEXOPT_MISSING_OK,
+ tablespaceOid);
if (result && (options & REINDEXOPT_VERBOSE))
ereport(INFO,
@@ -2985,6 +3082,9 @@ ReindexMultipleInternal(List *relids, int options)
* but including its associated toast table indexes. For indexes, the index
* itself will be rebuilt.
*
+ * 'tablespaceOid' is the tablespace where the relation's indexes 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.
@@ -2999,7 +3099,7 @@ ReindexMultipleInternal(List *relids, int options)
* anyway, and a non-concurrent reindex is more efficient.
*/
static bool
-ReindexRelationConcurrently(Oid relationOid, int options)
+ReindexRelationConcurrently(Oid relationOid, int options, Oid tablespaceOid)
{
List *heapRelationIds = NIL;
List *indexIds = NIL;
@@ -3253,6 +3353,13 @@ ReindexRelationConcurrently(Oid relationOid, int options)
return false;
}
+ /* It's not a shared catalog, so refuse to move it to shared tablespace */
+ if (tablespaceOid == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move non-shared relation to tablespace \"%s\"",
+ get_tablespace_name(tablespaceOid))));
+
Assert(heapRelationIds != NIL);
/*-----
@@ -3316,6 +3423,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 a29c14bf1c..e74f0dd80a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1891,7 +1891,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, REINDEX_REL_PROCESS_TOAST, 0, InvalidOid);
}
pgstat_count_truncate(rel);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 61cffcef84..82eef2d1bd 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -528,7 +528,7 @@ ProcessUtility(PlannedStmt *pstmt,
/* Parse params not parsed by the grammar */
static
-void parse_reindex_params(ParseState *pstate, ReindexStmt *stmt, int *options)
+void parse_reindex_params(ParseState *pstate, ReindexStmt *stmt, int *options, char **tablespace)
{
ListCell *lc;
foreach(lc, stmt->params)
@@ -547,6 +547,8 @@ void parse_reindex_params(ParseState *pstate, ReindexStmt *stmt, int *options)
*options |= REINDEXOPT_CONCURRENTLY;
else
*options &= ~REINDEXOPT_CONCURRENTLY;
+ else if (strcmp(opt->defname, "tablespace") == 0)
+ *tablespace = defGetString(opt);
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -949,8 +951,9 @@ standard_ProcessUtility(PlannedStmt *pstmt,
{
ReindexStmt *stmt = (ReindexStmt *) parsetree;
int options = 0;
+ char *tablespace = NULL;
- parse_reindex_params(pstate, stmt, &options);
+ parse_reindex_params(pstate, stmt, &options, &tablespace);
if (options & REINDEXOPT_CONCURRENTLY)
PreventInTransactionBlock(isTopLevel,
"REINDEX CONCURRENTLY");
@@ -958,10 +961,12 @@ standard_ProcessUtility(PlannedStmt *pstmt,
switch (stmt->kind)
{
case REINDEX_OBJECT_INDEX:
- ReindexIndex(stmt->relation, options, isTopLevel);
+ ReindexIndex(stmt->relation, options,
+ isTopLevel, tablespace);
break;
case REINDEX_OBJECT_TABLE:
- ReindexTable(stmt->relation, options, isTopLevel);
+ ReindexTable(stmt->relation, options,
+ isTopLevel, tablespace);
break;
case REINDEX_OBJECT_SCHEMA:
case REINDEX_OBJECT_SYSTEM:
@@ -977,7 +982,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, options);
+ ReindexMultipleTables(stmt->name, stmt->kind, options, tablespace);
break;
default:
elog(ERROR, "unrecognized object type: %d",
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 3fdf5018ee..ebaca25494 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3505,7 +3505,9 @@ psql_completion(const char *text, int start, int end)
* one word, so the above test is correct.
*/
if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
- COMPLETE_WITH("CONCURRENTLY", "VERBOSE");
+ COMPLETE_WITH("CONCURRENTLY", "TABLESPACE", "VERBOSE");
+ else if (TailMatches("TABLESPACE"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
}
/* SECURITY LABEL */
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index f58e8675f3..6a667df355 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,
@@ -134,7 +135,7 @@ extern void index_set_state_flags(Oid indexId, IndexStateFlagsAction action);
extern Oid IndexGetRelation(Oid indexId, bool missing_ok);
extern void reindex_index(Oid indexId, bool skip_constraint_checks,
- char relpersistence, int options);
+ char relpersistence, int options, Oid tablespaceOid);
/* Flag bits for reindex_relation(): */
#define REINDEX_REL_PROCESS_TOAST 0x01
@@ -143,7 +144,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, int flags, int options, Oid tablespaceOid);
extern bool ReindexIsProcessingHeap(Oid heapOid);
extern bool ReindexIsProcessingIndex(Oid indexOid);
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index 7a079ef07f..ee970a0f55 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -34,10 +34,10 @@ extern ObjectAddress DefineIndex(Oid relationId,
bool check_not_in_use,
bool skip_build,
bool quiet);
-extern void ReindexIndex(RangeVar *indexRelation, int options, bool isTopLevel);
-extern Oid ReindexTable(RangeVar *relation, int options, bool isTopLevel);
+extern void ReindexIndex(RangeVar *indexRelation, int options, bool isTopLevel, char *tablespace);
+extern Oid ReindexTable(RangeVar *relation, int options, bool isTopLevel, char *tablespace);
extern void ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
- int options);
+ int options, char *tablespace);
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/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index a5f61a35dc..5d8a22cffb 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -17,6 +17,48 @@ 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 (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');
+
+-- first, let us reindex and move the entire database, after that return everything back
+REINDEX (TABLESPACE regress_tblspace) DATABASE regression; -- ok with warning
+REINDEX (TABLESPACE pg_default) DATABASE regression; -- ok with warning
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+
+-- check REINDEX with TABLESPACE change
+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
+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
+
+-- 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;
@@ -96,6 +138,14 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
\d testschema.part_a_idx
\d+ testschema.part_a_idx
+-- REINDEX partitioned indexes to new tablespace
+REINDEX (TABLESPACE pg_default) TABLE testschema.part;
+\d testschema.part
+\d testschema.part1
+REINDEX (CONCURRENTLY, TABLESPACE regress_tblspace) INDEX testschema.part_a_idx;
+\d testschema.part
+\d testschema.part1
+
-- partitioned rels cannot specify the default tablespace. These fail:
CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
CREATE TABLE testschema.dflt (a int PRIMARY KEY USING INDEX TABLESPACE pg_default) PARTITION BY LIST (a);
@@ -279,6 +329,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..1169f0318b 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -20,6 +20,65 @@ 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 (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');
+ relname
+---------
+(0 rows)
+
+-- first, let us reindex and move the entire database, after that return everything back
+REINDEX (TABLESPACE regress_tblspace) DATABASE regression; -- ok with warning
+WARNING: cannot change tablespace of indexes on system relations, skipping all
+REINDEX (TABLESPACE pg_default) DATABASE regression; -- ok with warning
+WARNING: cannot change tablespace of indexes on system relations, skipping all
+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 (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 (TABLESPACE regress_tblspace) SYSTEM CONCURRENTLY postgres; -- fail
+ERROR: cannot reindex system catalogs concurrently
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_am; -- fail
+ERROR: cannot reindex system catalogs concurrently
+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 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 (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');
+ relname
+---------
+(0 rows)
+
-- create a schema we can use
CREATE SCHEMA testschema;
-- try a table
@@ -199,6 +258,47 @@ Partitions: testschema.part1_a_idx,
testschema.part2_a_idx
Tablespace: "regress_tblspace"
+-- REINDEX partitioned indexes to new tablespace
+REINDEX (TABLESPACE pg_default) TABLE testschema.part;
+\d testschema.part
+ Partitioned table "testschema.part"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: LIST (a)
+Indexes:
+ "part_a_idx" btree (a)
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d testschema.part1
+ Table "testschema.part1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition of: testschema.part FOR VALUES IN (1)
+Indexes:
+ "part1_a_idx" btree (a)
+
+REINDEX (CONCURRENTLY, TABLESPACE regress_tblspace) INDEX testschema.part_a_idx;
+\d testschema.part
+ Partitioned table "testschema.part"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: LIST (a)
+Indexes:
+ "part_a_idx" btree (a), tablespace "regress_tblspace"
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d testschema.part1
+ Table "testschema.part1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition of: testschema.part FOR VALUES IN (1)
+Indexes:
+ "part1_a_idx" btree (a), tablespace "regress_tblspace"
+
-- partitioned rels cannot specify the default tablespace. These fail:
CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
ERROR: cannot specify default tablespace for partitioned relations
@@ -736,6 +836,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 c3a1bc7f42b791b05b555f69d794980ad00181b7 Mon Sep 17 00:00:00 2001
From: Alexey Kondratov <kondratov.alek...@gmail.com>
Date: Wed, 23 Sep 2020 18:21:16 +0300
Subject: [PATCH v29 4/7] Refactor and reuse set_rel_tablespace()
---
src/backend/catalog/index.c | 74 ++++++++++++++++++++------------
src/backend/commands/indexcmds.c | 39 +----------------
src/include/catalog/index.h | 2 +
3 files changed, 51 insertions(+), 64 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index bee68abbd2..82696edd41 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -3437,7 +3437,6 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
Relation iRel,
heapRelation;
Oid heapId;
- Oid oldTablespaceOid;
IndexInfo *indexInfo;
volatile bool skipped_constraint = false;
PGRUsage ru0;
@@ -3553,41 +3552,17 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
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.
+ * Set the new tablespace for the relation if requested.
*/
- oldTablespaceOid = iRel->rd_rel->reltablespace;
if (set_tablespace &&
- (tablespaceOid != oldTablespaceOid ||
- (tablespaceOid == MyDatabaseTableSpace && OidIsValid(oldTablespaceOid))))
+ set_rel_tablespace(indexId, tablespaceOid))
{
- 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);
-
RelationAssumeNewRelfilenode(iRel);
/* Make sure the reltablespace change is visible */
@@ -3890,6 +3865,51 @@ reindex_relation(Oid relid, int flags, int options, Oid tablespaceOid)
return result;
}
+/*
+ * set_rel_tablespace - modify relation tablespace in the pg_class entry.
+ *
+ * 'reloid' is an Oid of relation to be modified.
+ * 'tablespaceOid' is an Oid of new tablespace.
+ *
+ * Catalog modification is done only if tablespaceOid is different from
+ * the currently set. Returned bool value is indicating whether any changes
+ * were made or not.
+ */
+bool
+set_rel_tablespace(Oid reloid, Oid tablespaceOid)
+{
+ Relation pg_class;
+ HeapTuple tuple;
+ Form_pg_class rd_rel;
+ bool changed = false;
+ Oid oldTablespaceOid;
+
+ /* Get a modifiable copy of the relation's pg_class row. */
+ pg_class = table_open(RelationRelationId, RowExclusiveLock);
+
+ tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(reloid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u", reloid);
+ rd_rel = (Form_pg_class) GETSTRUCT(tuple);
+
+ /* No work if no change in tablespace. */
+ oldTablespaceOid = rd_rel->reltablespace;
+ if (tablespaceOid != oldTablespaceOid ||
+ (tablespaceOid == MyDatabaseTableSpace && OidIsValid(oldTablespaceOid)))
+ {
+ /* Update the pg_class row. */
+ rd_rel->reltablespace = (tablespaceOid == MyDatabaseTableSpace) ?
+ InvalidOid : tablespaceOid;
+ CatalogTupleUpdate(pg_class, &tuple->t_self, tuple);
+
+ changed = true;
+ }
+
+ heap_freetuple(tuple);
+ table_close(pg_class, RowExclusiveLock);
+
+ return changed;
+}
/* ----------------------------------------------------------------
* System index reindexing support
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 516feeb402..1cb9ba6d57 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -2833,41 +2833,6 @@ reindex_error_callback(void *arg)
errinfo->relnamespace, errinfo->relname);
}
-/*
- * This is mostly duplicating ATExecSetTableSpaceNoStorage,
- * which should maybe be factored out to a library function.
- */
-static void
-set_rel_tablespace(Oid reloid, Oid tablespaceOid)
-{
- Relation pg_class;
- HeapTuple tuple;
- Form_pg_class rd_rel;
- Oid oldTablespaceOid;
-
- /* Get a modifiable copy of the relation's pg_class row */
- pg_class = table_open(RelationRelationId, RowExclusiveLock);
-
- tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(reloid));
- if (!HeapTupleIsValid(tuple))
- elog(ERROR, "cache lookup failed for relation %u", reloid);
- rd_rel = (Form_pg_class) GETSTRUCT(tuple);
-
- /* No work if no change in tablespace. */
- oldTablespaceOid = rd_rel->reltablespace;
- if (tablespaceOid != oldTablespaceOid ||
- (tablespaceOid == MyDatabaseTableSpace && OidIsValid(oldTablespaceOid)))
- {
- /* Update the pg_class row */
- rd_rel->reltablespace = (tablespaceOid == MyDatabaseTableSpace) ?
- InvalidOid : tablespaceOid;
- CatalogTupleUpdate(pg_class, &tuple->t_self, tuple);
- }
-
- heap_freetuple(tuple);
- table_close(pg_class, RowExclusiveLock);
-}
-
/*
* ReindexPartitions
*
@@ -2941,7 +2906,7 @@ ReindexPartitions(Oid relid, int options, bool isTopLevel, char *tablespace)
* relations.
*/
if (partkind == RELKIND_PARTITIONED_INDEX)
- set_rel_tablespace(partoid, tablespaceOid);
+ (void) set_rel_tablespace(partoid, tablespaceOid);
else if (partkind == RELKIND_PARTITIONED_TABLE)
{
Relation rel = table_open(partoid, ShareLock);
@@ -2952,7 +2917,7 @@ ReindexPartitions(Oid relid, int options, bool isTopLevel, char *tablespace)
foreach (lc, indexIds)
{
Oid indexid = lfirst_oid(lc);
- set_rel_tablespace(indexid, tablespaceOid);
+ (void) set_rel_tablespace(indexid, tablespaceOid);
}
}
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 6a667df355..a7745ba816 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -137,6 +137,8 @@ extern Oid IndexGetRelation(Oid indexId, bool missing_ok);
extern void reindex_index(Oid indexId, bool skip_constraint_checks,
char relpersistence, int options, Oid tablespaceOid);
+extern bool set_rel_tablespace(Oid reloid, Oid tablespaceOid);
+
/* Flag bits for reindex_relation(): */
#define REINDEX_REL_PROCESS_TOAST 0x01
#define REINDEX_REL_SUPPRESS_INDEX_USE 0x02
--
2.17.0
>From fbf62c6a92ca4c416635e4ffdb8f8a77b57616e1 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 v29 5/7] Allow CLUSTER and VACUUM FULL to change tablespace
---
doc/src/sgml/ref/cluster.sgml | 19 +++++++
doc/src/sgml/ref/vacuum.sgml | 20 +++++++
src/backend/commands/cluster.c | 67 +++++++++++++++++++++--
src/backend/commands/tablecmds.c | 5 +-
src/backend/commands/vacuum.c | 55 ++++++++++++++++++-
src/backend/parser/gram.y | 5 +-
src/backend/postmaster/autovacuum.c | 1 +
src/bin/psql/tab-complete.c | 9 ++-
src/include/commands/cluster.h | 3 +-
src/include/commands/vacuum.h | 2 +
src/test/regress/input/tablespace.source | 23 +++++++-
src/test/regress/output/tablespace.source | 37 ++++++++++++-
12 files changed, 228 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml
index efd8165e35..cbfc0582be 100644
--- a/doc/src/sgml/ref/cluster.sgml
+++ b/doc/src/sgml/ref/cluster.sgml
@@ -27,6 +27,7 @@ CLUSTER [VERBOSE] [ ( <replaceable class="parameter">option</replaceable> [, ...
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
+ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -95,6 +96,15 @@ CLUSTER [VERBOSE] [ ( <replaceable class="parameter">option</replaceable> [, ...
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>TABLESPACE</literal></term>
+ <listitem>
+ <para>
+ Specifies that the table will be rebuilt on a new tablespace.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">table_name</replaceable></term>
<listitem>
@@ -126,6 +136,15 @@ CLUSTER [VERBOSE] [ ( <replaceable class="parameter">option</replaceable> [, ...
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_tablespace</replaceable></term>
+ <listitem>
+ <para>
+ The tablespace where the table will be rebuilt.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</refsect1>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 21ab57d880..5261a7c727 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -35,6 +35,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
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>
@@ -255,6 +256,15 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</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>
@@ -299,6 +309,16 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_tablespace</replaceable></term>
+ <listitem>
+ <para>
+ The tablespace where the relation will be rebuilt.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect1>
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 8ababbeb14..b289a76d58 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,8 @@ typedef struct
} RelToCluster;
-static void rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose);
+static void rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose,
+ Oid NewTableSpaceOid);
static void copy_table_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex,
bool verbose, bool *pSwapToastByContent,
TransactionId *pFreezeXid, MultiXactId *pCutoffMulti);
@@ -104,6 +107,9 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
{
ListCell *lc;
int options = 0;
+ /* Name and Oid of tablespace to use for clustered relation. */
+ char *tablespaceName = NULL;
+ Oid tablespaceOid = InvalidOid;
/* Parse list of generic parameters not handled by the parser */
foreach(lc, stmt->params)
@@ -115,6 +121,8 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
options |= CLUOPT_VERBOSE;
else
options &= ~CLUOPT_VERBOSE;
+ else if (strcmp(opt->defname, "tablespace") == 0)
+ tablespaceName = defGetString(opt);
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -123,6 +131,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. */
@@ -192,7 +213,8 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
table_close(rel, NoLock);
/* Do the job. */
- cluster_rel(tableOid, indexOid, options);
+ cluster_rel(tableOid, indexOid, options,
+ tablespaceOid);
}
else
{
@@ -241,7 +263,8 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
PushActiveSnapshot(GetTransactionSnapshot());
/* Do the job. */
cluster_rel(rvtc->tableOid, rvtc->indexOid,
- options | CLUOPT_RECHECK);
+ options | CLUOPT_RECHECK,
+ tablespaceOid);
PopActiveSnapshot();
CommitTransactionCommand();
}
@@ -270,9 +293,12 @@ 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 where the relation will be rebuilt, or
+ * InvalidOid to use its current tablespace.
*/
void
-cluster_rel(Oid tableOid, Oid indexOid, int options)
+cluster_rel(Oid tableOid, Oid indexOid, int options, Oid tablespaceOid)
{
Relation OldHeap;
bool verbose = ((options & CLUOPT_VERBOSE) != 0);
@@ -372,6 +398,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.
@@ -422,7 +465,8 @@ 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, verbose,
+ tablespaceOid);
/* NB: rebuild_relation does table_close() on OldHeap */
@@ -571,7 +615,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, bool verbose, Oid NewTablespaceOid)
{
Oid tableOid = RelationGetRelid(OldHeap);
Oid tableSpace = OldHeap->rd_rel->reltablespace;
@@ -582,6 +626,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);
@@ -1026,6 +1074,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/tablecmds.c b/src/backend/commands/tablecmds.c
index e74f0dd80a..0461332e96 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -13160,8 +13160,9 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
if (RelationIsMapped(rel))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot move system relation \"%s\"",
- RelationGetRelationName(rel))));
+ errmsg("cannot change tablespace of mapped relation \"%s\"",
+ RelationGetRelationName(rel))));
+
/* Can't move a non-shared relation into pg_global */
if (newTableSpace == GLOBALTABLESPACE_OID)
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 1b6717f727..6a8116dfa3 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,10 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
bool disable_page_skipping = false;
ListCell *lc;
+ /* 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;
params.truncate = VACOPT_TERNARY_DEFAULT;
@@ -142,6 +149,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)
{
if (opt->arg == NULL)
@@ -202,6 +211,27 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("VACUUM FULL cannot be performed in parallel")));
+ /* 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.
*/
@@ -1702,8 +1732,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;
@@ -1839,6 +1870,23 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
return true;
}
+ /*
+ * We don't 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
@@ -1908,7 +1956,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, cluster_options);
+ cluster_rel(relid, InvalidOid, cluster_options,
+ tablespaceOid);
}
else
table_relation_vacuum(onerel, params, vac_strategy);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ea371b8d6b..488a4c44bc 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10462,8 +10462,9 @@ cluster_index_specification:
/*****************************************************************************
*
* QUERY:
- * VACUUM
- * ANALYZE
+ * VACUUM [FULL] [FREEZE] [VERBOSE] [ANALYZE] [ <table_and_columns> [, ...] ]
+ * VACUUM [(options)] [ <table_and_columns> [, ...] ]
+ * ANALYZE [VERBOSE] [ <table_and_columns> [, ...] ]
*
*****************************************************************************/
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 2cef56f115..e73e124335 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2916,6 +2916,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/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index ebaca25494..173848f92f 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2287,7 +2287,9 @@ psql_completion(const char *text, int start, int end)
* one word, so the above test is correct.
*/
if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
- COMPLETE_WITH("VERBOSE");
+ COMPLETE_WITH("TABLESPACE", "VERBOSE");
+ else if (TailMatches("TABLESPACE"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
}
/* COMMENT */
@@ -3734,9 +3736,12 @@ psql_completion(const char *text, int start, int end)
if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
COMPLETE_WITH("FULL", "FREEZE", "ANALYZE", "VERBOSE",
"DISABLE_PAGE_SKIPPING", "SKIP_LOCKED",
- "INDEX_CLEANUP", "TRUNCATE", "PARALLEL");
+ "INDEX_CLEANUP", "TRUNCATE", "PARALLEL",
+ "TABLESPACE");
else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE"))
COMPLETE_WITH("ON", "OFF");
+ else if (TailMatches("TABLESPACE"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
}
else if (HeadMatches("VACUUM") && TailMatches("("))
/* "VACUUM (" should be caught above, so assume we want columns */
diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h
index 674cdcd0cd..30300a8f74 100644
--- a/src/include/commands/cluster.h
+++ b/src/include/commands/cluster.h
@@ -20,7 +20,8 @@
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, int options,
+ Oid tablespaceOid);
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 a4cd721400..4b5ac7145d 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 to use for relations
+ * rebuilt by VACUUM FULL */
} VacuumParams;
/* GUC parameters */
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 5d8a22cffb..f4687f6bfb 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
@@ -47,11 +47,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 (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
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 1169f0318b..e0a5c48fe9 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
@@ -61,9 +61,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 (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 (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
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 46a2581c71b663020f9fef315bcde8ab4b6f695f Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Tue, 31 Mar 2020 20:35:41 -0500
Subject: [PATCH v29 6/7] Implement vacuum full/cluster (INDEX_TABLESPACE
<tablespace>)
---
doc/src/sgml/ref/cluster.sgml | 12 ++++-
doc/src/sgml/ref/vacuum.sgml | 12 ++++-
src/backend/commands/cluster.c | 64 ++++++++++++++---------
src/backend/commands/matview.c | 3 +-
src/backend/commands/tablecmds.c | 2 +-
src/backend/commands/vacuum.c | 46 +++++++---------
src/backend/postmaster/autovacuum.c | 1 +
src/include/commands/cluster.h | 6 ++-
src/include/commands/vacuum.h | 5 +-
src/test/regress/input/tablespace.source | 13 +++++
src/test/regress/output/tablespace.source | 20 +++++++
11 files changed, 123 insertions(+), 61 deletions(-)
diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml
index cbfc0582be..6781e3a025 100644
--- a/doc/src/sgml/ref/cluster.sgml
+++ b/doc/src/sgml/ref/cluster.sgml
@@ -28,6 +28,7 @@ CLUSTER [VERBOSE] [ ( <replaceable class="parameter">option</replaceable> [, ...
VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
+ INDEX_TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -105,6 +106,15 @@ CLUSTER [VERBOSE] [ ( <replaceable class="parameter">option</replaceable> [, ...
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>INDEX_TABLESPACE</literal></term>
+ <listitem>
+ <para>
+ Specifies that the table's indexes will be rebuilt on a new tablespace.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">table_name</replaceable></term>
<listitem>
@@ -141,7 +151,7 @@ CLUSTER [VERBOSE] [ ( <replaceable class="parameter">option</replaceable> [, ...
<term><replaceable class="parameter">new_tablespace</replaceable></term>
<listitem>
<para>
- The tablespace where the table will be rebuilt.
+ The tablespace where the table or its indexes will be rebuilt.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 5261a7c727..28cab119b6 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -36,6 +36,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
PARALLEL <replaceable class="parameter">integer</replaceable>
TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
+ INDEX_TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -265,6 +266,15 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>INDEX_TABLESPACE</literal></term>
+ <listitem>
+ <para>
+ Specifies that the relation's indexes will be rebuilt on a new tablespace.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">boolean</replaceable></term>
<listitem>
@@ -314,7 +324,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
<term><replaceable class="parameter">new_tablespace</replaceable></term>
<listitem>
<para>
- The tablespace where the relation will be rebuilt.
+ The tablespace where the relation or its indexes will be rebuilt.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index b289a76d58..0f9f09a15a 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -71,7 +71,7 @@ typedef struct
static void rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose,
- Oid NewTableSpaceOid);
+ Oid NewTableSpaceOid, Oid NewIdxTableSpaceOid);
static void copy_table_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex,
bool verbose, bool *pSwapToastByContent,
TransactionId *pFreezeXid, MultiXactId *pCutoffMulti);
@@ -107,9 +107,11 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
{
ListCell *lc;
int options = 0;
- /* Name and Oid of tablespace to use for clustered relation. */
- char *tablespaceName = NULL;
- Oid tablespaceOid = InvalidOid;
+ /* Name and Oid of tablespaces to use for clustered relations. */
+ char *tablespaceName = NULL,
+ *idxtablespaceName = NULL;
+ Oid tablespaceOid,
+ idxtablespaceOid;
/* Parse list of generic parameters not handled by the parser */
foreach(lc, stmt->params)
@@ -123,6 +125,8 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
options &= ~CLUOPT_VERBOSE;
else if (strcmp(opt->defname, "tablespace") == 0)
tablespaceName = defGetString(opt);
+ else if (strcmp(opt->defname, "index_tablespace") == 0)
+ idxtablespaceName = defGetString(opt);
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -131,18 +135,11 @@ 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)));
- }
+ /* Get tablespaces to use. */
+ tablespaceOid = tablespaceName ?
+ get_tablespace_oid(tablespaceName, false) : InvalidOid;
+ idxtablespaceOid = idxtablespaceName ?
+ get_tablespace_oid(idxtablespaceName, false) : InvalidOid;
if (stmt->relation != NULL)
{
@@ -214,7 +211,7 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
/* Do the job. */
cluster_rel(tableOid, indexOid, options,
- tablespaceOid);
+ tablespaceOid, idxtablespaceOid);
}
else
{
@@ -264,7 +261,7 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
/* Do the job. */
cluster_rel(rvtc->tableOid, rvtc->indexOid,
options | CLUOPT_RECHECK,
- tablespaceOid);
+ tablespaceOid, idxtablespaceOid);
PopActiveSnapshot();
CommitTransactionCommand();
}
@@ -294,11 +291,12 @@ cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
* 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 where the relation will be rebuilt, or
- * InvalidOid to use its current tablespace.
+ * "tablespaceOid" and "idxtablespaceOid" are the tablespaces where the relation
+ * and its indexes will be rebuilt, or InvalidOid to use their current
+ * tablespaces.
*/
void
-cluster_rel(Oid tableOid, Oid indexOid, int options, Oid tablespaceOid)
+cluster_rel(Oid tableOid, Oid indexOid, int options, Oid tablespaceOid, Oid idxtablespaceOid)
{
Relation OldHeap;
bool verbose = ((options & CLUOPT_VERBOSE) != 0);
@@ -466,7 +464,7 @@ cluster_rel(Oid tableOid, Oid indexOid, int options, Oid tablespaceOid)
/* rebuild_relation does all the dirty work */
rebuild_relation(OldHeap, indexOid, verbose,
- tablespaceOid);
+ tablespaceOid, idxtablespaceOid);
/* NB: rebuild_relation does table_close() on OldHeap */
@@ -615,10 +613,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, bool verbose, Oid NewTablespaceOid)
+rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose, Oid NewTablespaceOid, Oid NewIdxTablespaceOid)
{
Oid tableOid = RelationGetRelid(OldHeap);
Oid tableSpace = OldHeap->rd_rel->reltablespace;
+ Oid idxtableSpace;
Oid OIDNewHeap;
char relpersistence;
bool is_system_catalog;
@@ -628,7 +627,20 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose, Oid NewTablespace
/* Use new tablespace if passed. */
if (OidIsValid(NewTablespaceOid))
+ {
tableSpace = NewTablespaceOid;
+ /* It's not a shared catalog, so refuse to move it to shared tablespace */
+ if (tableSpace == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move non-shared relation to tablespace \"%s\"",
+ get_tablespace_name(tableSpace))));
+ }
+
+ if (OidIsValid(NewIdxTablespaceOid))
+ idxtableSpace = NewIdxTablespaceOid;
+ else
+ idxtableSpace = get_rel_tablespace(indexOid);
/* Mark the correct index as clustered */
if (OidIsValid(indexOid))
@@ -657,7 +669,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose, Oid NewTablespace
finish_heap_swap(tableOid, OIDNewHeap, is_system_catalog,
swap_toast_by_content, false, true,
frozenXid, cutoffMulti,
- relpersistence);
+ relpersistence, idxtableSpace);
}
@@ -1405,7 +1417,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];
@@ -1467,7 +1479,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, InvalidOid);
+ reindex_relation(OIDOldHeap, reindex_flags, 0, idxtableSpace);
/* 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 f80a9e96a9..68ea07cae5 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -844,7 +844,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 0461332e96..1abe4dce4f 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -5060,7 +5060,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 6a8116dfa3..c95c7be3b6 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -109,9 +109,9 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
bool disable_page_skipping = 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;
@@ -151,6 +151,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)
{
if (opt->arg == NULL)
@@ -211,26 +213,18 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("VACUUM FULL cannot be performed in parallel")));
- /* 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)));
+ if ((params.options & VACOPT_FULL) == 0 &&
+ (tablespacename || idxtablespacename))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("incompatible TABLESPACE option"),
+ errdetail("You can only use TABLESPACE with VACUUM FULL.")));
- }
- params.tablespace_oid = tablespaceOid;
+ /* Get tablespace Oids to use. */
+ params.tablespace_oid = tablespacename ?
+ get_tablespace_oid(tablespacename, false) : InvalidOid;
+ params.idxtablespace_oid = idxtablespacename ?
+ get_tablespace_oid(idxtablespacename, false) : InvalidOid;
/*
* Make sure VACOPT_ANALYZE is specified if any column lists are present.
@@ -1733,8 +1727,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;
@@ -1878,14 +1871,13 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
OidIsValid(params->tablespace_oid) &&
IsSystemRelation(onerel) && !allowSystemTableMods)
{
+ params->tablespace_oid = InvalidOid;
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
@@ -1957,7 +1949,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
cluster_rel(relid, InvalidOid, cluster_options,
- tablespaceOid);
+ params->tablespace_oid, params->idxtablespace_oid);
}
else
table_relation_vacuum(onerel, params, vac_strategy);
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index e73e124335..aca1db0861 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2917,6 +2917,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 30300a8f74..8e535d9511 100644
--- a/src/include/commands/cluster.h
+++ b/src/include/commands/cluster.h
@@ -21,7 +21,8 @@
extern void cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel);
extern void cluster_rel(Oid tableOid, Oid indexOid, int options,
- Oid tablespaceOid);
+ Oid tablespaceOid,
+ Oid indextablespaceOid);
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);
@@ -35,6 +36,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 4b5ac7145d..488d7540c7 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -229,8 +229,9 @@ typedef struct VacuumParams
* disabled.
*/
int nworkers;
- Oid tablespace_oid; /* tablespace to use for relations
- * rebuilt by VACUUM FULL */
+ /* tablespaces to use for relations rebuilt by VACUUM FULL */
+ Oid tablespace_oid;
+ Oid idxtablespace_oid;
} VacuumParams;
/* GUC parameters */
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index f4687f6bfb..3065e15e18 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -80,6 +80,19 @@ REINDEX (TABLESPACE pg_default) TABLE CONCURRENTLY regress_tblspace_test_tbl; --
SELECT relname FROM pg_class
WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+-- check CLUSTER with INDEX_TABLESPACE change to non-default location
+CLUSTER (INDEX_TABLESPACE regress_tblspace) regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx; -- ok
+-- check 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 INDEX_TABLESPACE change
+VACUUM (FULL, ANALYSE, FREEZE, INDEX_TABLESPACE pg_default) regress_tblspace_test_tbl; -- ok
+-- check 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;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index e0a5c48fe9..65c61ee373 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -114,6 +114,26 @@ WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspa
---------
(0 rows)
+-- check CLUSTER with INDEX_TABLESPACE change to non-default location
+CLUSTER (INDEX_TABLESPACE regress_tblspace) regress_tblspace_test_tbl USING regress_tblspace_test_tbl_idx; -- ok
+-- check 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)
+
+-- check VACUUM with INDEX_TABLESPACE change
+VACUUM (FULL, ANALYSE, FREEZE, INDEX_TABLESPACE pg_default) regress_tblspace_test_tbl; -- ok
+-- check 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
--
2.17.0
