From: Alexey Kondratov <a.kondratov@postgrespro.ru>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Surafel Temesgen <surafel3000@gmail.com>
Cc: Alexander Korotkov <a.korotkov@postgrespro.ru>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: Jose Luis Tallon <jltallon@adv-solutions.net>
Subject: Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly
Date: Tue, 24 Sep 2019 16:02:39 +0300
Message-ID: <fe8c0444-ebfc-ce5d-8843-7f93c0f53d7f@postgrespro.ru> (raw)
In-Reply-To: <20190920163808.GA18848@alvherre.pgsql>
References: <20190920163808.GA18848@alvherre.pgsql>
On 20.09.2019 19:38, Alvaro Herrera wrote:
> On 2019-Sep-19, Robert Haas wrote:
>
>> So, earlier in this thread, I suggested making this part of ALTER
>> TABLE, and several people seemed to like that idea. Did we have a
>> reason for dropping that approach?
> Hmm, my own reading of that was to add tablespace changing abilities to
> ALTER TABLE *in addition* to this patch, not instead of it.
That was my understanding too.
On 20.09.2019 11:26, Jose Luis Tallon wrote:
> On 20/9/19 4:06, Michael Paquier wrote:
>> Personally, I don't find this idea very attractive as ALTER TABLE is
>> already complicated enough with all the subqueries we already support
>> in the command, all the logic we need to maintain to make combinations
>> of those subqueries in a minimum number of steps, and also the number
>> of bugs we have seen because of the amount of complication present.
>
> Yes, but please keep the other options: At it is, cluster, vacuum full
> and reindex already rewrite the table in full; Being able to write the
> result to a different tablespace than the original object was stored
> in enables a whole world of very interesting possibilities....
> including a quick way out of a "so little disk space available that
> vacuum won't work properly" situation --- which I'm sure MANY users
> will appreciate, including me
Yes, sure, that was my main motivation. The first message in the thread
contains a patch, which adds SET TABLESPACE support to all of CLUSTER,
VACUUM FULL and REINDEX. However, there came up an idea to integrate
CLUSTER/VACUUM FULL with ALTER TABLE and do their work + all the ALTER
TABLE stuff in a single table rewrite. I've dig a little bit into this
and ended up with some architectural questions and concerns [1]. So I
decided to start with a simple REINDEX patch.
Anyway, I've followed Michael's advice and split the last patch into two:
1) Adds all the main functionality, but with simplified 'REINDEX INDEX [
CONCURRENTLY ] ... [ TABLESPACE ... ]' grammar;
2) Adds a more sophisticated syntax with '[ SET TABLESPACE ... [ NOWAIT
] ]'.
Patch 1 contains all the docs and tests and may be applied/committed
separately or together with 2, which is fully optional.
Recent merge conflicts and reindex_index validations order are also
fixed in the attached version.
[1]
https://www.postgresql.org/message-id/6b2a5c4de19f111ef24b63428033bb67%40postgrespro.ru
Regards
--
Alexey Kondratov
Postgres Professional https://www.postgrespro.com
Russian Postgres Company
Attachments:
[text/x-patch] v3-0001-Allow-REINDEX-and-REINDEX-CONCURRENTLY-to-change-.patch (30.1K, ../fe8c0444-ebfc-ce5d-8843-7f93c0f53d7f@postgrespro.ru/2-v3-0001-Allow-REINDEX-and-REINDEX-CONCURRENTLY-to-change-.patch)
download | inline diff:
From 4f06996f1e86dee389cb0f901cb83dba77c2abd8 Mon Sep 17 00:00:00 2001
From: Alexey Kondratov <kondratov.aleksey@gmail.com>
Date: Tue, 24 Sep 2019 12:29:57 +0300
Subject: [PATCH v3 1/2] Allow REINDEX and REINDEX CONCURRENTLY to change
TABLESPACE
---
doc/src/sgml/ref/reindex.sgml | 23 ++++++
src/backend/catalog/index.c | 99 ++++++++++++++++++++---
src/backend/commands/cluster.c | 2 +-
src/backend/commands/indexcmds.c | 34 +++++---
src/backend/commands/tablecmds.c | 59 ++++++++------
src/backend/parser/gram.y | 21 +++--
src/backend/tcop/utility.c | 16 +++-
src/include/catalog/index.h | 7 +-
src/include/commands/defrem.h | 6 +-
src/include/commands/tablecmds.h | 2 +
src/include/nodes/parsenodes.h | 1 +
src/test/regress/input/tablespace.source | 31 +++++++
src/test/regress/output/tablespace.source | 41 ++++++++++
13 files changed, 279 insertions(+), 63 deletions(-)
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgmlindex 10881ab03a..96c9363ad9 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 } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable> [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ]
</synopsis>
</refsynopsisdiv>
@@ -165,6 +166,28 @@ REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURR
</listitem>
</varlistentry>
+ <varlistentry>+ <term><literal>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. + </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.cindex 098732cc4a..b2fed5dc75 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -1239,7 +1239,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,
@@ -1369,7 +1370,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,
@@ -3342,16 +3343,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);
@@ -3390,11 +3397,15 @@ 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))+ if (OidIsValid(tablespaceOid))+ /* Check that relocation is possible during reindex. */+ check_relation_is_movable(iRel, tablespaceOid);+ else if (RELATION_IS_OTHER_TEMP(iRel))+ /*+ * 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.+ */
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot reindex temporary tables of other sessions")));
@@ -3405,6 +3416,68 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
*/
CheckTableNotInUse(iRel, "REINDEX INDEX");
+ if (OidIsValid(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();+ }+
/*
* All predicate locks on the index are about to be made invalid. Promote
* them to relation locks on the heap.
@@ -3577,7 +3650,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;
@@ -3651,7 +3724,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();
@@ -3686,7 +3759,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.cindex 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.cindex 70f9b6729a..7f00dac426 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;
@@ -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,+ reindex_index(indOid, tableSpaceOid, false, persistence,
options | REINDEXOPT_REPORT_PROGRESS);
}
@@ -2429,10 +2434,11 @@ 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,
@@ -2440,9 +2446,12 @@ ReindexTable(RangeVar *relation, int options, bool concurrent)
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,
@@ -2452,6 +2461,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);
@@ -2473,10 +2483,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];
@@ -2525,6 +2536,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
@@ -2648,7 +2662,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
if (concurrent)
{
- (void) ReindexRelationConcurrently(relid, options);+ (void) ReindexRelationConcurrently(relid, tableSpaceOid, options);
/* ReindexRelationConcurrently() does the verbose output */
}
else
@@ -2656,6 +2670,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);
@@ -2696,7 +2711,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;
@@ -2956,6 +2971,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.cindex 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.yindex 3f67aaf30e..cba361f1bc 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_tablespace_name
%type <rolespec> OptTableSpaceOwner
%type <ival> opt_check_option
@@ -3937,6 +3937,11 @@ OptTableSpace: TABLESPACE name { $$ = $2; }
| /*EMPTY*/ { $$ = NULL; }
;
+opt_tablespace_name:+ TABLESPACE name { $$ = $2; }+ | /*EMPTY*/ { $$ = NULL; }+ ;+
OptConsTableSpace: USING INDEX TABLESPACE name { $$ = $4; }
| /*EMPTY*/ { $$ = NULL; }
;
@@ -8356,31 +8361,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 opt_tablespace_name
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $2;
n->concurrent = $3;
n->relation = $4;
+ n->tablespacename = $5;
n->name = NULL;
n->options = 0;
$$ = (Node *)n;
}
- | REINDEX reindex_target_multitable opt_concurrently name+ | REINDEX reindex_target_multitable opt_concurrently name opt_tablespace_name
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $2;
n->concurrent = $3;
n->name = $4;
+ n->tablespacename = $5;
n->relation = NULL;
n->options = 0;
$$ = (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_tablespace_name
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $5;
@@ -8388,9 +8395,10 @@ ReindexStmt:
n->relation = $7;
n->name = NULL;
n->options = $3;
+ n->tablespacename = $8;
$$ = (Node *)n;
}
- | REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name+ | REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name opt_tablespace_name
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $5;
@@ -8398,6 +8406,7 @@ ReindexStmt:
n->name = $7;
n->relation = NULL;
n->options = $3;
+ n->tablespacename = $8;
$$ = (Node *)n;
}
;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.cindex c6faa6619d..fed40471e0 100644--- a/src/backend/tcop/utility.c+++ b/src/backend/tcop/utility.c@@ -784,15 +784,25 @@ standard_ProcessUtility(PlannedStmt *pstmt,
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 TABLESPACE option"),+ errdetail("You can only use 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 +813,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.hindex 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.hindex 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.hindex 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.hindex d93a79a554..ed2c385050 100644--- a/src/include/nodes/parsenodes.h+++ b/src/include/nodes/parsenodes.h@@ -3333,6 +3333,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.sourceindex a5f61a35dc..d2dacd85d9 100644--- a/src/test/regress/input/tablespace.source+++ b/src/test/regress/input/tablespace.source@@ -17,6 +17,34 @@ 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 TABLESPACE regress_tblspace; -- ok+REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; -- ok+REINDEX TABLE pg_authid TABLESPACE regress_tblspace; -- fail+REINDEX SCHEMA pg_catalog TABLESPACE regress_tblspace; -- fail+REINDEX DATABASE postgres TABLESPACE regress_tblspace; -- fail+REINDEX SYSTEM postgres 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 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')+AND relname IN ('regress_tblspace_test_tbl_idx');+
-- create a schema we can use
CREATE SCHEMA testschema;
@@ -279,6 +307,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.sourceindex 162b591b31..fb2f33c0b1 100644--- a/src/test/regress/output/tablespace.source+++ b/src/test/regress/output/tablespace.source@@ -20,6 +20,45 @@ 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 TABLESPACE regress_tblspace; -- ok+REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; -- ok+REINDEX TABLE pg_authid TABLESPACE regress_tblspace; -- fail+ERROR: cannot move system relation "pg_authid_rolname_index"+REINDEX SCHEMA pg_catalog TABLESPACE regress_tblspace; -- fail+ERROR: incompatible TABLESPACE option+DETAIL: You can only use TABLESPACE with REINDEX { INDEX | TABLE }.+REINDEX DATABASE postgres TABLESPACE regress_tblspace; -- fail+ERROR: incompatible TABLESPACE option+DETAIL: You can only use TABLESPACE with REINDEX { INDEX | TABLE }.+REINDEX SYSTEM postgres TABLESPACE regress_tblspace; -- fail+ERROR: incompatible TABLESPACE option+DETAIL: You can only use 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 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')+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 +775,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: 6d05086c0a79e50d8e91ed953626ec7280cd2481
--
2.17.1
[text/x-patch] v3-0002-Use-SET-TABLESPACE-syntax-with-NOWAIT-option.patch (12.8K, ../fe8c0444-ebfc-ce5d-8843-7f93c0f53d7f@postgrespro.ru/3-v3-0002-Use-SET-TABLESPACE-syntax-with-NOWAIT-option.patch)
download | inline diff:
From c9c072748b54e6dbde8bde5d932af52c229141f5 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 v3 2/2] Use SET TABLESPACE syntax with NOWAIT option
---
doc/src/sgml/ref/reindex.sgml | 8 ++++---
src/backend/commands/indexcmds.c | 4 ++--
src/backend/parser/gram.y | 26 +++++++++++++-------
src/backend/tcop/utility.c | 10 ++++++--
src/include/nodes/parsenodes.h | 1 +
src/test/regress/input/tablespace.source | 15 ++++++------
src/test/regress/output/tablespace.source | 29 +++++++++++++----------
7 files changed, 57 insertions(+), 36 deletions(-)
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgmlindex 96c9363ad9..4f0f21b7f9 100644--- a/doc/src/sgml/ref/reindex.sgml+++ b/doc/src/sgml/ref/reindex.sgml@@ -22,7 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable>
-REINDEX [ ( VERBOSE ) ] { INDEX | TABLE } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable> [ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> ]+REINDEX [ ( VERBOSE ) ] { INDEX | TABLE } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable> [ SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> [NOWAIT] ]
</synopsis>
</refsynopsisdiv>
@@ -167,14 +167,16 @@ REINDEX [ ( VERBOSE ) ] { INDEX | TABLE } [ CONCURRENTLY ] <replaceable class="p
</varlistentry>
<varlistentry>
- <term><literal>TABLESPACE</literal></term>+ <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. + <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>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.cindex 7f00dac426..6eab0d335d 100644--- a/src/backend/commands/indexcmds.c+++ b/src/backend/commands/indexcmds.c@@ -2329,7 +2329,7 @@ ReindexIndex(RangeVar *indexRelation, char *newTableSpaceName, int options, bool
state.locked_table_oid = InvalidOid;
indOid = RangeVarGetRelidExtended(indexRelation,
concurrent ? ShareUpdateExclusiveLock : AccessExclusiveLock,
- 0,+ options & REINDEXOPT_NOWAIT ? RVR_NOWAIT : 0,
RangeVarCallbackForReindexIndex,
&state);
@@ -2443,7 +2443,7 @@ ReindexTable(RangeVar *relation, char *newTableSpaceName, int options, bool conc
/* 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)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.yindex cba361f1bc..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 opt_tablespace_name+%type <str> OptTableSpace OptConsTableSpace opt_set_tablespace_name
%type <rolespec> OptTableSpaceOwner
%type <ival> opt_check_option
@@ -3937,9 +3937,9 @@ OptTableSpace: TABLESPACE name { $$ = $2; }
| /*EMPTY*/ { $$ = NULL; }
;
-opt_tablespace_name:- TABLESPACE name { $$ = $2; }- | /*EMPTY*/ { $$ = NULL; }+opt_set_tablespace_name:+ SET TABLESPACE name { $$ = $3; }+ | /*EMPTY*/ { $$ = NULL; }
;
OptConsTableSpace: USING INDEX TABLESPACE name { $$ = $4; }
@@ -8361,11 +8361,11 @@ 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> [ SET TABLESPACE <tablespace_name> ]
*****************************************************************************/
ReindexStmt:
- REINDEX reindex_target_type opt_concurrently qualified_name opt_tablespace_name+ REINDEX reindex_target_type opt_concurrently qualified_name opt_set_tablespace_name opt_nowait
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $2;
@@ -8374,9 +8374,11 @@ ReindexStmt:
n->tablespacename = $5;
n->name = NULL;
n->options = 0;
+ if ($6)+ n->options |= REINDEXOPT_NOWAIT;
$$ = (Node *)n;
}
- | REINDEX reindex_target_multitable opt_concurrently name opt_tablespace_name+ | REINDEX reindex_target_multitable opt_concurrently name opt_set_tablespace_name opt_nowait
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $2;
@@ -8385,9 +8387,11 @@ ReindexStmt:
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 opt_tablespace_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;
@@ -8396,9 +8400,11 @@ ReindexStmt:
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 opt_tablespace_name+ | REINDEX '(' reindex_option_list ')' reindex_target_multitable opt_concurrently name opt_set_tablespace_name opt_nowait
{
ReindexStmt *n = makeNode(ReindexStmt);
n->kind = $5;
@@ -8407,6 +8413,8 @@ ReindexStmt:
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.cindex fed40471e0..88d6b80263 100644--- a/src/backend/tcop/utility.c+++ b/src/backend/tcop/utility.c@@ -778,6 +778,12 @@ 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 */
@@ -800,8 +806,8 @@ standard_ProcessUtility(PlannedStmt *pstmt,
*/
if (stmt->tablespacename)
ereport(ERROR,
- (errmsg("incompatible TABLESPACE option"),- errdetail("You can only use TABLESPACE with REINDEX { INDEX | TABLE }.")));+ (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
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.hindex ed2c385050..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
{
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.sourceindex d2dacd85d9..0b9df9f0d8 100644--- a/src/test/regress/input/tablespace.source+++ b/src/test/regress/input/tablespace.source@@ -25,12 +25,13 @@ INSERT INTO regress_tblspace_test_tbl (num1, num2, num3)
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 TABLESPACE regress_tblspace; -- ok-REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; -- ok-REINDEX TABLE pg_authid TABLESPACE regress_tblspace; -- fail-REINDEX SCHEMA pg_catalog TABLESPACE regress_tblspace; -- fail-REINDEX DATABASE postgres TABLESPACE regress_tblspace; -- fail-REINDEX SYSTEM postgres TABLESPACE regress_tblspace; -- fail+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
@@ -38,7 +39,7 @@ WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspa
AND relname IN ('regress_tblspace_test_tbl_idx');
-- move back to pg_default tablespace
-REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl TABLESPACE pg_default; -- ok+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
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.sourceindex fb2f33c0b1..4b5a1ae2fb 100644--- a/src/test/regress/output/tablespace.source+++ b/src/test/regress/output/tablespace.source@@ -27,19 +27,22 @@ INSERT INTO regress_tblspace_test_tbl (num1, num2, num3)
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 TABLESPACE regress_tblspace; -- ok-REINDEX TABLE regress_tblspace_test_tbl TABLESPACE regress_tblspace; -- ok-REINDEX TABLE pg_authid TABLESPACE regress_tblspace; -- fail+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 TABLESPACE regress_tblspace; -- fail-ERROR: incompatible TABLESPACE option-DETAIL: You can only use TABLESPACE with REINDEX { INDEX | TABLE }.-REINDEX DATABASE postgres TABLESPACE regress_tblspace; -- fail-ERROR: incompatible TABLESPACE option-DETAIL: You can only use TABLESPACE with REINDEX { INDEX | TABLE }.-REINDEX SYSTEM postgres TABLESPACE regress_tblspace; -- fail-ERROR: incompatible TABLESPACE option-DETAIL: You can only use TABLESPACE with REINDEX { INDEX | TABLE }.+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')
@@ -50,7 +53,7 @@ AND relname IN ('regress_tblspace_test_tbl_idx');
(1 row)
-- move back to pg_default tablespace
-REINDEX TABLE CONCURRENTLY regress_tblspace_test_tbl TABLESPACE pg_default; -- ok+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')
--
2.17.1
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, alvherre@2ndquadrant.com, michael@paquier.xyz, robertmhaas@gmail.com, surafel3000@gmail.com, a.korotkov@postgrespro.ru, sawada.mshk@gmail.com, jltallon@adv-solutions.net
Subject: Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly
In-Reply-To: <fe8c0444-ebfc-ce5d-8843-7f93c0f53d7f@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox