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