agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Alexey Kondratov <a.kondratov@postgrespro.ru>
To: Surafel Temesgen <surafel3000@gmail.com>
Cc: Alexander Korotkov <a.korotkov@postgrespro.ru>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly
Date: Wed, 18 Sep 2019 15:46:20 +0300
Message-ID: <34493046-cbda-3262-2ff9-4189bccfb862@postgrespro.ru> (raw)
In-Reply-To: <CALAY4q-0oU4ORb07C4BxvGXgetBswqS1V5e2JP1wTV5q+O7-tA@mail.gmail.com>
References: <f3255cfc-a395-133a-17d1-5fca873e7b05@postgrespro.ru>
<20181227132417.xe3oagawina7775b@alvherre.pgsql>
<CAD21AoCOsHnu2wLOaHgcZ4yJp7kaQkWfyhLzD4tjh6k4k4nhAA@mail.gmail.com>
<CAPpHfdunJ4o5w9ndtOeAEt_h_H+XoZpgopK+NCsA30xeZTE8kw@mail.gmail.com>
<6b2a5c4de19f111ef24b63428033bb67@postgrespro.ru>
<CALAY4q-0oU4ORb07C4BxvGXgetBswqS1V5e2JP1wTV5q+O7-tA@mail.gmail.com>
Hi Surafel,
Thank you for looking at the patch!
On 17.09.2019 14:04, Surafel Temesgen wrote:
> * There are NOWAIT option in alter index, is there a reason not to
> have similar option here?
Currently in Postgres SET TABLESPACE always comes with [ NOWAIT ]
option, so I hope it worth adding this option here for convenience.
Added in the new version.
> * SET TABLESPACE command is not documented
Actually, new_tablespace parameter was documented, but I've added a more
detailed section for SET TABLESPACE too.
> * There are multiple checking for whether the relation is temporary
> tables of other sessions, one in check_relation_is_movable and other
> independently
Yes, and there is a comment section in the code describing why. There is
a repeatable bunch of checks for verification whether relation movable
or not, so I put it into a separated function --
check_relation_is_movable. However, if we want to do only REINDEX, then
some of them are excess, so the only one RELATION_IS_OTHER_TEMP is used.
Thus, RELATION_IS_OTHER_TEMP is never executed twice, just different
code paths.
> *+ char *tablespacename;
>
> calling it new_tablespacename will make it consistent with other places
>
OK, changed, although I don't think it is important, since this is the
only one tablespace variable there.
> *The patch did't applied cleanly
> http://cfbot.cputube.org/patch_24_2269.log
>
Patch is rebased and attached with all the fixes described above.
Regards
--
Alexey Kondratov
Postgres Professional https://www.postgrespro.com
Russian Postgres Company
Attachments:
[text/x-patch] v2-0001-Allow-REINDEX-and-REINDEX-CONCURRENTLY-to-SET-TAB.patch (32.2K, ../34493046-cbda-3262-2ff9-4189bccfb862@postgrespro.ru/2-v2-0001-Allow-REINDEX-and-REINDEX-CONCURRENTLY-to-SET-TAB.patch)
download | inline diff:
From 7a19b1fd945502ad55f1fa9e61c3014d8715e404 Mon Sep 17 00:00:00 2001
From: Alexey Kondratov <kondratov.aleksey@gmail.com>
Date: Wed, 18 Sep 2019 15:22:04 +0300
Subject: [PATCH v2] Allow REINDEX and REINDEX CONCURRENTLY to SET TABLESPACE
---
doc/src/sgml/ref/reindex.sgml | 25 +++++
src/backend/catalog/index.c | 109 ++++++++++++++++++----
src/backend/commands/cluster.c | 2 +-
src/backend/commands/indexcmds.c | 38 +++++---
src/backend/commands/tablecmds.c | 59 +++++++-----
src/backend/parser/gram.y | 29 ++++--
src/backend/tcop/utility.c | 22 ++++-
src/include/catalog/index.h | 7 +-
src/include/commands/defrem.h | 6 +-
src/include/commands/tablecmds.h | 2 +
src/include/nodes/parsenodes.h | 2 +
src/test/regress/input/tablespace.source | 32 +++++++
src/test/regress/output/tablespace.source | 44 +++++++++
13 files changed, 308 insertions(+), 69 deletions(-)
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 10881ab03a..192243e58f 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -22,6 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable>
+REINDEX [ ( VERBOSE ) ] { INDEX | TABLE } <replaceable class="parameter">name</replaceable> [ SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> [NOWAIT] ]
</synopsis>
</refsynopsisdiv>
@@ -165,6 +166,30 @@ REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURR
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>SET TABLESPACE</literal></term>
+ <listitem>
+ <para>
+ This specifies a tablespace, where all rebuilt indexes will be created.
+ Can be used only with <literal>REINDEX INDEX</literal> and
+ <literal>REINDEX TABLE</literal>, since the system indexes are not
+ movable, but <literal>SCHEMA</literal>, <literal>DATABASE</literal> or
+ <literal>SYSTEM</literal> very likely will has one. If the
+ <literal>NOWAIT</literal> option is specified then the command will fail
+ if it is unable to acquire all of the locks required immediately.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_tablespace</replaceable></term>
+ <listitem>
+ <para>
+ The name of the specific tablespace to store rebuilt indexes.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>VERBOSE</literal></term>
<listitem>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 54288a498c..715abfdf65 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1194,7 +1194,8 @@ index_create(Relation heapRelation,
* on. This is called during concurrent reindex processing.
*/
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,
@@ -1324,7 +1325,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, const char
newInfo,
indexColNames,
indexRelation->rd_rel->relam,
- indexRelation->rd_rel->reltablespace,
+ tablespaceOid ? tablespaceOid : indexRelation->rd_rel->reltablespace,
indexRelation->rd_indcollation,
indclass->values,
indcoloptions->values,
@@ -3297,16 +3298,22 @@ IndexGetRelation(Oid indexId, bool missing_ok)
* reindex_index - This routine is used to recreate a single index
*/
void
-reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
+reindex_index(Oid indexId, Oid tablespaceOid, bool skip_constraint_checks, char persistence,
int options)
{
Relation iRel,
- heapRelation;
- Oid heapId;
+ heapRelation,
+ pg_class;
+ Oid heapId,
+ newIndexRelfilenodeOid = InvalidOid;
IndexInfo *indexInfo;
volatile bool skipped_constraint = false;
PGRUsage ru0;
bool progress = (options & REINDEXOPT_REPORT_PROGRESS) != 0;
+ RelFileNode newrnode;
+ SMgrRelation dstrel;
+ HeapTuple tuple;
+ Form_pg_class rd_rel;
pg_rusage_init(&ru0);
@@ -3345,21 +3352,89 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
elog(ERROR, "unsupported relation kind for index \"%s\"",
RelationGetRelationName(iRel));
- /*
- * Don't allow reindex on temp tables of other backends ... their local
- * buffer manager is not going to cope.
- */
- if (RELATION_IS_OTHER_TEMP(iRel))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot reindex temporary tables of other sessions")));
-
/*
* Also check for active uses of the index in the current transaction; we
* don't want to reindex underneath an open indexscan.
*/
CheckTableNotInUse(iRel, "REINDEX INDEX");
+ if (OidIsValid(tablespaceOid))
+ {
+ /* Check that relocation is possible during reindex. */
+ check_relation_is_movable(iRel, tablespaceOid);
+
+ pg_class = heap_open(RelationRelationId, RowExclusiveLock);
+
+ tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(indexId));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u", indexId);
+ rd_rel = (Form_pg_class) GETSTRUCT(tuple);
+
+ /* Use binary-upgrade override for pg_class.oid/relfilenode? */
+ if (IsBinaryUpgrade)
+ {
+ if (!OidIsValid(binary_upgrade_next_index_pg_class_oid))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("pg_class index OID value not set when in binary upgrade mode")));
+
+ newIndexRelfilenodeOid = binary_upgrade_next_index_pg_class_oid;
+ binary_upgrade_next_index_pg_class_oid = InvalidOid;
+ }
+ else
+ {
+ newIndexRelfilenodeOid =
+ GetNewRelFileNode(tablespaceOid, pg_class, heapRelation->rd_rel->relpersistence);
+ }
+
+ /* Open old and new relation */
+ newrnode = iRel->rd_node;
+ newrnode.relNode = newIndexRelfilenodeOid;
+ newrnode.spcNode = tablespaceOid;
+ dstrel = smgropen(newrnode, iRel->rd_backend);
+
+ RelationOpenSmgr(iRel);
+
+ /*
+ * Create and copy all forks of the relation, and schedule unlinking of
+ * old physical files.
+ *
+ * NOTE: any conflict in relfilenode value will be caught in
+ * RelationCreateStorage().
+ */
+ RelationCreateStorage(newrnode, iRel->rd_rel->relpersistence);
+
+ /* Drop old relation, and close new one */
+ RelationDropStorage(iRel);
+ smgrclose(dstrel);
+
+ /* Update the pg_class row */
+ rd_rel->reltablespace = tablespaceOid;
+ rd_rel->relfilenode = newIndexRelfilenodeOid;
+ CatalogTupleUpdate(pg_class, &tuple->t_self, tuple);
+
+ InvokeObjectPostAlterHook(RelationRelationId, RelationGetRelid(iRel), 0);
+
+ heap_freetuple(tuple);
+
+ heap_close(pg_class, RowExclusiveLock);
+
+ /* Make the updated catalog row versions visible */
+ CommandCounterIncrement();
+ }
+ else
+ {
+ /*
+ * Don't allow reindex on temp tables of other backends ... their local
+ * buffer manager is not going to cope. Check only if TABLESPACE is not
+ * passed, since the same validation exists in the check_relation_is_movable.
+ */
+ if (RELATION_IS_OTHER_TEMP(iRel))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot reindex temporary tables of other sessions")));
+ }
+
/*
* All predicate locks on the index are about to be made invalid. Promote
* them to relation locks on the heap.
@@ -3532,7 +3607,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;
@@ -3606,7 +3681,7 @@ reindex_relation(Oid relid, int flags, int options)
{
Oid indexOid = lfirst_oid(indexId);
- reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
+ reindex_index(indexOid, tablespaceOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
persistence, options);
CommandCounterIncrement();
@@ -3641,7 +3716,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 a23128d7a0..0f68f899da 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -1407,7 +1407,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 d7c1e1c93f..416a88aa2d 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);
@@ -2312,10 +2312,11 @@ ChooseIndexColumnNames(List *indexElems)
* Recreate a specific index.
*/
void
-ReindexIndex(RangeVar *indexRelation, int options, bool concurrent)
+ReindexIndex(RangeVar *indexRelation, char *newTableSpaceName, int options, bool concurrent)
{
struct ReindexIndexCallbackState state;
Oid indOid;
+ Oid tableSpaceOid = InvalidOid;
Relation irel;
char persistence;
@@ -2328,7 +2329,7 @@ ReindexIndex(RangeVar *indexRelation, int options, bool concurrent)
state.locked_table_oid = InvalidOid;
indOid = RangeVarGetRelidExtended(indexRelation,
concurrent ? ShareUpdateExclusiveLock : AccessExclusiveLock,
- 0,
+ options & REINDEXOPT_NOWAIT ? RVR_NOWAIT : 0,
RangeVarCallbackForReindexIndex,
&state);
@@ -2345,12 +2346,16 @@ ReindexIndex(RangeVar *indexRelation, int options, bool concurrent)
}
persistence = irel->rd_rel->relpersistence;
+
+ if (newTableSpaceName)
+ tableSpaceOid = get_tablespace_oid(newTableSpaceName, false);
+
index_close(irel, NoLock);
if (concurrent)
- ReindexRelationConcurrently(indOid, options);
+ ReindexRelationConcurrently(indOid, tableSpaceOid, options);
else
- reindex_index(indOid, false, persistence, options);
+ reindex_index(indOid, tableSpaceOid, false, persistence, options);
}
/*
@@ -2428,20 +2433,24 @@ 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(RangeVar *relation, char *newTableSpaceName, int options, bool concurrent)
{
Oid heapOid;
bool result;
+ Oid tableSpaceOid = InvalidOid;
/* The lock level used here should match reindex_relation(). */
heapOid = RangeVarGetRelidExtended(relation,
concurrent ? ShareUpdateExclusiveLock : ShareLock,
- 0,
+ options & REINDEXOPT_NOWAIT ? RVR_NOWAIT : 0,
RangeVarCallbackOwnsTable, NULL);
+ if (newTableSpaceName)
+ tableSpaceOid = get_tablespace_oid(newTableSpaceName, false);
+
if (concurrent)
{
- result = ReindexRelationConcurrently(heapOid, options);
+ result = ReindexRelationConcurrently(heapOid, tableSpaceOid, options);
if (!result)
ereport(NOTICE,
@@ -2451,6 +2460,7 @@ ReindexTable(RangeVar *relation, int options, bool concurrent)
else
{
result = reindex_relation(heapOid,
+ tableSpaceOid,
REINDEX_REL_PROCESS_TOAST |
REINDEX_REL_CHECK_CONSTRAINTS,
options | REINDEXOPT_REPORT_PROGRESS);
@@ -2472,10 +2482,11 @@ 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,
+ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, char *newTableSpaceName,
int options, bool concurrent)
{
Oid objectOid;
+ Oid tableSpaceOid = InvalidOid;
Relation relationRelation;
TableScanDesc scan;
ScanKeyData scan_keys[1];
@@ -2524,6 +2535,9 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
objectName);
}
+ if (newTableSpaceName)
+ tableSpaceOid = get_tablespace_oid(newTableSpaceName, false);
+
/*
* Create a memory context that will survive forced transaction commits we
* do below. Since it is a child of PortalContext, it will go away
@@ -2647,7 +2661,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
if (concurrent)
{
- (void) ReindexRelationConcurrently(relid, options);
+ (void) ReindexRelationConcurrently(relid, tableSpaceOid, options);
/* ReindexRelationConcurrently() does the verbose output */
}
else
@@ -2655,6 +2669,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
bool result;
result = reindex_relation(relid,
+ tableSpaceOid,
REINDEX_REL_PROCESS_TOAST |
REINDEX_REL_CHECK_CONSTRAINTS,
options | REINDEXOPT_REPORT_PROGRESS);
@@ -2695,7 +2710,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
* indexes, when relevant), otherwise returns false.
*/
static bool
-ReindexRelationConcurrently(Oid relationOid, int options)
+ReindexRelationConcurrently(Oid relationOid, Oid tablespaceOid, int options)
{
List *heapRelationIds = NIL;
List *indexIds = NIL;
@@ -2955,6 +2970,7 @@ ReindexRelationConcurrently(Oid relationOid, int options)
/* Create new index definition based on given index */
newIndexId = index_concurrently_create_copy(heapRel,
indexId,
+ tablespaceOid,
concurrentName);
/* Now open the relation of the new index, a lock is also needed on it */
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 05593f3316..0f9b0d4108 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1817,7 +1817,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);
@@ -12337,30 +12337,7 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
return;
}
- /*
- * We cannot support moving mapped relations into different tablespaces.
- * (In particular this eliminates all shared catalogs.)
- */
- if (RelationIsMapped(rel))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot move system relation \"%s\"",
- RelationGetRelationName(rel))));
-
- /* Can't move a non-shared relation into pg_global */
- if (newTableSpace == GLOBALTABLESPACE_OID)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("only shared relations can be placed in pg_global tablespace")));
-
- /*
- * Don't allow moving temp tables of other backends ... their local buffer
- * manager is not going to cope.
- */
- if (RELATION_IS_OTHER_TEMP(rel))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot move temporary tables of other sessions")));
+ check_relation_is_movable(rel, newTableSpace);
reltoastrelid = rel->rd_rel->reltoastrelid;
/* Fetch the list of indexes on toast relation if necessary */
@@ -12957,6 +12934,38 @@ CreateInheritance(Relation child_rel, Relation parent_rel)
table_close(catalogRelation, RowExclusiveLock);
}
+/*
+ * Validate that relation can be moved to the specified tablespace.
+ */
+extern void
+check_relation_is_movable(Relation rel, Oid tablespaceOid)
+{
+ /*
+ * We cannot support moving mapped relations into different tablespaces.
+ * (In particular this eliminates all shared catalogs.)
+ */
+ if (RelationIsMapped(rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move system relation \"%s\"",
+ RelationGetRelationName(rel))));
+
+ /* Can't move a non-shared relation into pg_global */
+ if (tablespaceOid == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("only shared relations can be placed in pg_global tablespace")));
+
+ /*
+ * Don't allow moving temp tables of other backends ... their local buffer
+ * manager is not going to cope.
+ */
+ if (RELATION_IS_OTHER_TEMP(rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move temporary tables of other sessions")));
+}
+
/*
* Obtain the source-text form of the constraint expression for a check
* constraint, given its pg_constraint tuple
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 3f67aaf30e..01b2fac200 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -549,7 +549,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> constraints_set_list
%type <boolean> constraints_set_mode
-%type <str> OptTableSpace OptConsTableSpace
+%type <str> OptTableSpace OptConsTableSpace opt_set_tablespace_name
%type <rolespec> OptTableSpaceOwner
%type <ival> opt_check_option
@@ -3937,6 +3937,11 @@ OptTableSpace: TABLESPACE name { $$ = $2; }
| /*EMPTY*/ { $$ = NULL; }
;
+opt_set_tablespace_name:
+ SET TABLESPACE name { $$ = $3; }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
+
OptConsTableSpace: USING INDEX TABLESPACE name { $$ = $4; }
| /*EMPTY*/ { $$ = NULL; }
;
@@ -8356,31 +8361,37 @@ DropTransformStmt: DROP TRANSFORM opt_if_exists FOR Typename LANGUAGE name opt_d
*
* QUERY:
*
- * REINDEX [ (options) ] type [CONCURRENTLY] <name>
+ * REINDEX [ (options) ] type [CONCURRENTLY] <name> [ SET TABLESPACE <tablespace_name> ]
*****************************************************************************/
ReindexStmt:
- REINDEX reindex_target_type opt_concurrently qualified_name
+ REINDEX reindex_target_type opt_concurrently qualified_name opt_set_tablespace_name opt_nowait
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $2;
n->concurrent = $3;
n->relation = $4;
+ n->tablespacename = $5;
n->name = NULL;
n->options = 0;
+ if ($6)
+ n->options |= REINDEXOPT_NOWAIT;
$$ = (Node *)n;
}
- | REINDEX reindex_target_multitable opt_concurrently name
+ | REINDEX reindex_target_multitable opt_concurrently name opt_set_tablespace_name opt_nowait
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $2;
n->concurrent = $3;
n->name = $4;
+ n->tablespacename = $5;
n->relation = NULL;
n->options = 0;
+ if ($6)
+ n->options |= REINDEXOPT_NOWAIT;
$$ = (Node *)n;
}
- | REINDEX '(' reindex_option_list ')' reindex_target_type opt_concurrently qualified_name
+ | REINDEX '(' reindex_option_list ')' reindex_target_type opt_concurrently qualified_name opt_set_tablespace_name opt_nowait
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $5;
@@ -8388,9 +8399,12 @@ ReindexStmt:
n->relation = $7;
n->name = NULL;
n->options = $3;
+ n->tablespacename = $8;
+ if ($9)
+ n->options |= REINDEXOPT_NOWAIT;
$$ = (Node *)n;
}
- | REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name
+ | REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name opt_set_tablespace_name opt_nowait
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $5;
@@ -8398,6 +8412,9 @@ ReindexStmt:
n->name = $7;
n->relation = NULL;
n->options = $3;
+ n->tablespacename = $8;
+ if ($9)
+ n->options |= REINDEXOPT_NOWAIT;
$$ = (Node *)n;
}
;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index c6faa6619d..88d6b80263 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -778,21 +778,37 @@ standard_ProcessUtility(PlannedStmt *pstmt,
PreventInTransactionBlock(isTopLevel,
"REINDEX CONCURRENTLY");
+ if (stmt->options & REINDEXOPT_NOWAIT && stmt->tablespacename == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("incompatible NOWAIT option"),
+ errdetail("You can only use NOWAIT with SET TABLESPACE.")));
+
/* we choose to allow this during "read only" transactions */
PreventCommandDuringRecovery("REINDEX");
/* forbidden in parallel mode due to CommandIsReadOnly */
switch (stmt->kind)
{
case REINDEX_OBJECT_INDEX:
- ReindexIndex(stmt->relation, stmt->options, stmt->concurrent);
+ ReindexIndex(stmt->relation, stmt->tablespacename, stmt->options, stmt->concurrent);
break;
case REINDEX_OBJECT_TABLE:
- ReindexTable(stmt->relation, stmt->options, stmt->concurrent);
+ ReindexTable(stmt->relation, stmt->tablespacename, stmt->options, stmt->concurrent);
break;
case REINDEX_OBJECT_SCHEMA:
case REINDEX_OBJECT_SYSTEM:
case REINDEX_OBJECT_DATABASE:
+ /*
+ * We cannot move system relations to a new tablespace and
+ * the entire schema/database very likely will has one,
+ * so simply reject such cases.
+ */
+ if (stmt->tablespacename)
+ ereport(ERROR,
+ (errmsg("incompatible SET TABLESPACE option"),
+ errdetail("You can only use SET TABLESPACE with REINDEX { INDEX | TABLE }.")));
+
/*
* This cannot run inside a user transaction block; if
* we were inside a transaction, then its commit- and
@@ -803,7 +819,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->name, stmt->kind, stmt->tablespacename, stmt->options, stmt->concurrent);
break;
default:
elog(ERROR, "unrecognized object type: %d",
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 1113d25b2d..ef6103a174 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,
@@ -129,8 +130,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
@@ -139,7 +140,7 @@ extern void reindex_index(Oid indexId, bool skip_constraint_checks,
#define REINDEX_REL_FORCE_INDEXES_UNLOGGED 0x08
#define REINDEX_REL_FORCE_INDEXES_PERMANENT 0x10
-extern bool reindex_relation(Oid relid, int flags, int options);
+extern bool reindex_relation(Oid relid, Oid tablespaceOid, int flags, int options);
extern bool ReindexIsProcessingHeap(Oid heapOid);
extern bool ReindexIsProcessingIndex(Oid indexOid);
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index 1dc6dc2ca0..3687872869 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -34,10 +34,10 @@ extern ObjectAddress DefineIndex(Oid relationId,
bool check_not_in_use,
bool skip_build,
bool quiet);
-extern void ReindexIndex(RangeVar *indexRelation, int options, bool concurrent);
-extern Oid ReindexTable(RangeVar *relation, int options, bool concurrent);
+extern void ReindexIndex(RangeVar *indexRelation, char *newTableSpaceName, int options, bool concurrent);
+extern Oid ReindexTable(RangeVar *relation, char *newTableSpaceName, int options, bool concurrent);
extern void ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
- int options, bool concurrent);
+ char *newTableSpaceName, int options, bool concurrent);
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/commands/tablecmds.h b/src/include/commands/tablecmds.h
index 9c25a805f2..4824bd6166 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -52,6 +52,8 @@ extern void AlterRelationNamespaceInternal(Relation classRel, Oid relOid,
extern void CheckTableNotInUse(Relation rel, const char *stmt);
+extern void check_relation_is_movable(Relation rel, Oid tablespaceOid);
+
extern void ExecuteTruncate(TruncateStmt *stmt);
extern void ExecuteTruncateGuts(List *explicit_rels, List *relids, List *relids_logged,
DropBehavior behavior, bool restart_seqs);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index d93a79a554..2fef8b249d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3314,6 +3314,7 @@ typedef struct ConstraintsSetStmt
/* Reindex options */
#define REINDEXOPT_VERBOSE (1 << 0) /* print progress info */
#define REINDEXOPT_REPORT_PROGRESS (1 << 1) /* report pgstat progress */
+#define REINDEXOPT_NOWAIT (1 << 2) /* error if relation cannot be locked */
typedef enum ReindexObjectType
{
@@ -3333,6 +3334,7 @@ typedef struct ReindexStmt
const char *name; /* name of database to reindex */
int options; /* Reindex options flags */
bool concurrent; /* reindex concurrently? */
+ char *tablespacename;
} ReindexStmt;
/* ----------------------
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index a5f61a35dc..0b9df9f0d8 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -17,6 +17,35 @@ 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 REINDEX with TABLESPACE change
+REINDEX INDEX regress_tblspace_test_tbl_idx SET TABLESPACE regress_tblspace; -- ok
+REINDEX TABLE regress_tblspace_test_tbl SET TABLESPACE regress_tblspace; -- ok
+REINDEX TABLE pg_authid NOWAIT; -- fail
+REINDEX TABLE pg_authid SET TABLESPACE regress_tblspace; -- fail
+REINDEX SCHEMA pg_catalog SET TABLESPACE regress_tblspace; -- fail
+REINDEX DATABASE postgres SET TABLESPACE regress_tblspace; -- fail
+REINDEX SYSTEM postgres SET TABLESPACE regress_tblspace; -- fail
+
+-- check that all relations moved to new tablespace
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+AND relname IN ('regress_tblspace_test_tbl_idx');
+
+-- move back to pg_default tablespace
+REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl SET TABLESPACE pg_default NOWAIT; -- ok
+
+-- check that all relations moved back to pg_default
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+AND relname IN ('regress_tblspace_test_tbl_idx');
+
-- create a schema we can use
CREATE SCHEMA testschema;
@@ -279,6 +308,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..4b5a1ae2fb 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -20,6 +20,48 @@ 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 REINDEX with TABLESPACE change
+REINDEX INDEX regress_tblspace_test_tbl_idx SET TABLESPACE regress_tblspace; -- ok
+REINDEX TABLE regress_tblspace_test_tbl SET TABLESPACE regress_tblspace; -- ok
+REINDEX TABLE pg_authid NOWAIT; -- fail
+ERROR: incompatible NOWAIT option
+DETAIL: You can only use NOWAIT with SET TABLESPACE.
+REINDEX TABLE pg_authid SET TABLESPACE regress_tblspace; -- fail
+ERROR: cannot move system relation "pg_authid_rolname_index"
+REINDEX SCHEMA pg_catalog SET TABLESPACE regress_tblspace; -- fail
+ERROR: incompatible SET TABLESPACE option
+DETAIL: You can only use SET TABLESPACE with REINDEX { INDEX | TABLE }.
+REINDEX DATABASE postgres SET TABLESPACE regress_tblspace; -- fail
+ERROR: incompatible SET TABLESPACE option
+DETAIL: You can only use SET TABLESPACE with REINDEX { INDEX | TABLE }.
+REINDEX SYSTEM postgres SET TABLESPACE regress_tblspace; -- fail
+ERROR: incompatible SET TABLESPACE option
+DETAIL: You can only use SET TABLESPACE with REINDEX { INDEX | TABLE }.
+-- check that all relations moved to new tablespace
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+AND relname IN ('regress_tblspace_test_tbl_idx');
+ relname
+-------------------------------
+ regress_tblspace_test_tbl_idx
+(1 row)
+
+-- move back to pg_default tablespace
+REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl SET TABLESPACE pg_default NOWAIT; -- ok
+-- check that all relations moved back to pg_default
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+AND relname IN ('regress_tblspace_test_tbl_idx');
+ relname
+---------
+(0 rows)
+
-- create a schema we can use
CREATE SCHEMA testschema;
-- try a table
@@ -736,6 +778,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
base-commit: 48770492c3b796b251112fa9b74534f087c9f471
--
2.17.1
view thread (154+ messages) latest in thread
Message-ID: <34493046-cbda-3262-2ff9-4189bccfb862@postgrespro.ru>
Permalink: ../34493046-cbda-3262-2ff9-4189bccfb862@postgrespro.ru/
Also on: postgresql.org/message-id/34493046-cbda-3262-2ff9-4189bccfb862@postgrespro.ru
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: a.kondratov@postgrespro.ru, surafel3000@gmail.com, a.korotkov@postgrespro.ru, sawada.mshk@gmail.com, alvherre@2ndquadrant.com, robertmhaas@gmail.com, michael@paquier.xyz
Subject: Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly
In-Reply-To: <34493046-cbda-3262-2ff9-4189bccfb862@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox