agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Alexey Kondratov <a.kondratov@postgrespro.ru>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: Masahiko Sawada <masahiko.sawada@2ndquadrant.com>
Cc: Steve Singer <steve@ssinger.info>
Cc: pgsql-hackers@lists.postgresql.org, Robert Haas <robertmhaas@gmail.com>
Cc: Alexander Korotkov <a.korotkov@postgrespro.ru>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Jose Luis Tallon <jltallon@adv-solutions.net>
Subject: Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly
Date: Wed, 20 Jan 2021 21:10:14 +0300
Message-ID: <03f88f70618ce73e75837b6125a143f7@postgrespro.ru> (raw)
In-Reply-To: <7f51f09d2dd1c92981f898e0a7b2f187@postgrespro.ru>
References: <20210120155450.GA9584@alvherre.pgsql>
<7f51f09d2dd1c92981f898e0a7b2f187@postgrespro.ru>
On 2021-01-20 21:08, Alexey Kondratov wrote:
> On 2021-01-20 18:54, Alvaro Herrera wrote:
>> On 2021-Jan-20, Alvaro Herrera wrote:
>>
>>> On 2021-Jan-20, Michael Paquier wrote:
>>>
>>> > +/*
>>> > + * This is mostly duplicating ATExecSetTableSpaceNoStorage,
>>> > + * which should maybe be factored out to a library function.
>>> > + */
>>> > Wouldn't it be better to do first the refactoring of 0002 and then
>>> > 0001 so as REINDEX can use the new routine, instead of putting that
>>> > into a comment?
>>>
>>> I think merging 0001 and 0002 into a single commit is a reasonable
>>> approach.
>>
>> ... except it doesn't make a lot of sense to have set_rel_tablespace
>> in
>> either indexcmds.c or index.c. I think tablecmds.c is a better place
>> for it. (I would have thought catalog/storage.c, but that one's not
>> the
>> right abstraction level it seems.)
>>
>
> I did a refactoring of ATExecSetTableSpaceNoStorage() in the 0001. New
> function SetRelTablesapce() is placed into the tablecmds.c. Following
> 0002 gets use of it. Is it close to what you and Michael suggested?
>
Ugh, forgot to attach the patches. Here they are.
--
Alexey
Attachments:
[text/x-diff] v2-0002-Allow-REINDEX-to-change-tablespace.patch (22.7K, ../03f88f70618ce73e75837b6125a143f7@postgrespro.ru/2-v2-0002-Allow-REINDEX-to-change-tablespace.patch)
download | inline diff:
From 2c3876f99bc8ebdd07c532619992e7ec3093e50a Mon Sep 17 00:00:00 2001
From: Alexey Kondratov <kondratov.aleksey@gmail.com>
Date: Mon, 23 Mar 2020 21:10:29 +0300
Subject: [PATCH v2 2/2] Allow REINDEX to change tablespace
REINDEX already does full relation rewrite, this patch adds a
possibility to specify a new tablespace where new relfilenode
will be created.
---
doc/src/sgml/ref/reindex.sgml | 22 +++++
src/backend/catalog/index.c | 72 ++++++++++++++-
src/backend/commands/indexcmds.c | 68 ++++++++++++++-
src/bin/psql/tab-complete.c | 4 +-
src/include/catalog/index.h | 2 +
src/test/regress/input/tablespace.source | 53 +++++++++++
src/test/regress/output/tablespace.source | 102 ++++++++++++++++++++++
7 files changed, 318 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 627b36300c..4f84060c4d 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -27,6 +27,7 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
CONCURRENTLY [ <replaceable class="parameter">boolean</replaceable> ]
VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
+ TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -187,6 +188,19 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>TABLESPACE</literal></term>
+ <listitem>
+ <para>
+ This specifies that indexes will be rebuilt on a new tablespace.
+ Cannot be used with "mapped" relations. If <literal>SCHEMA</literal>,
+ <literal>DATABASE</literal> or <literal>SYSTEM</literal> is specified, then
+ all unsuitable relations will be skipped and a single <literal>WARNING</literal>
+ will be generated.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>VERBOSE</literal></term>
<listitem>
@@ -210,6 +224,14 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">new_tablespace</replaceable></term>
+ <listitem>
+ <para>
+ The tablespace where indexes will be rebuilt.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</refsect1>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index b8cd35e995..ed98b17483 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -57,6 +57,7 @@
#include "commands/event_trigger.h"
#include "commands/progress.h"
#include "commands/tablecmds.h"
+#include "commands/tablespace.h"
#include "commands/trigger.h"
#include "executor/executor.h"
#include "miscadmin.h"
@@ -1394,9 +1395,13 @@ index_update_collation_versions(Oid relid, Oid coll)
* 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,
@@ -1526,7 +1531,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,
@@ -3591,6 +3597,8 @@ 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,
@@ -3603,6 +3611,7 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
volatile bool skipped_constraint = false;
PGRUsage ru0;
bool progress = ((params->options & REINDEXOPT_REPORT_PROGRESS) != 0);
+ bool set_tablespace = OidIsValid(params->tablespaceOid);
pg_rusage_init(&ru0);
@@ -3654,6 +3663,35 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
get_namespace_name(RelationGetNamespace(iRel)),
RelationGetRelationName(iRel));
+ /*
+ * We don't support moving system relations into different tablespaces
+ * unless allow_system_table_mods=1.
+ */
+ if (set_tablespace &&
+ !allowSystemTableMods && IsSystemRelation(iRel))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied: \"%s\" is a system catalog",
+ RelationGetRelationName(iRel))));
+
+ /*
+ * We cannot support moving mapped relations into different tablespaces.
+ * (In particular this eliminates all shared catalogs.)
+ */
+ if (set_tablespace && RelationIsMapped(iRel))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot change tablespace of mapped relation \"%s\"",
+ RelationGetRelationName(iRel))));
+
+ /* It's not a shared catalog, so refuse to move it to shared tablespace */
+ if (params->tablespaceOid == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move non-shared relation to tablespace \"%s\"",
+ get_tablespace_name(params->tablespaceOid))));
+
+
/*
* Don't allow reindex on temp tables of other backends ... their local
* buffer manager is not going to cope.
@@ -3680,6 +3718,28 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
*/
CheckTableNotInUse(iRel, "REINDEX INDEX");
+ if (params->tablespaceOid == MyDatabaseTableSpace)
+ params->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 && SetRelTablespace(indexId, params->tablespaceOid))
+ {
+ /*
+ * 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);
+
+ RelationAssumeNewRelfilenode(iRel);
+
+ /* Make sure the reltablespace change is visible */
+ CommandCounterIncrement();
+ }
+
/*
* All predicate locks on the index are about to be made invalid. Promote
* them to relation locks on the heap.
@@ -3814,6 +3874,9 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
* reindex_relation - This routine is used to recreate all indexes
* of a relation (and optionally its toast relation too, if any).
*
+ * "tablespaceOid" is the tablespace where the relation's indexes will be
+ * rebuilt, or InvalidOid to keep each index on its current tablespace.
+ *
* "flags" is a bitmask that can include any combination of these bits:
*
* REINDEX_REL_PROCESS_TOAST: if true, process the toast table too (if any).
@@ -3964,10 +4027,15 @@ reindex_relation(Oid relid, int flags, ReindexParams *params)
/*
* Note that this should fail if the toast relation is missing, so
* reset REINDEXOPT_MISSING_OK.
+ *
+ * Even if a table's indexes were moved to a new tablespace, the index
+ * on its toast table is not normally moved.
*/
ReindexParams newparams = *params;
newparams.options &= ~(REINDEXOPT_MISSING_OK);
+ if (!allowSystemTableMods)
+ newparams.tablespaceOid = InvalidOid;
result |= reindex_relation(toast_relid, flags, &newparams);
}
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index f9f3ff3b62..2703cece6c 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -2474,6 +2474,7 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
ListCell *lc;
bool concurrently = false;
bool verbose = false;
+ char *tablespace = NULL;
/* Parse option list */
foreach(lc, stmt->params)
@@ -2484,6 +2485,8 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
verbose = defGetBoolean(opt);
else if (strcmp(opt->defname, "concurrently") == 0)
concurrently = defGetBoolean(opt);
+ else if (strcmp(opt->defname, "tablespace") == 0)
+ tablespace = defGetString(opt);
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -2500,6 +2503,9 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
(verbose ? REINDEXOPT_VERBOSE : 0) |
(concurrently ? REINDEXOPT_CONCURRENTLY : 0);
+ params.tablespaceOid = tablespace != NULL ?
+ get_tablespace_oid(tablespace, false) : InvalidOid;
+
switch (stmt->kind)
{
case REINDEX_OBJECT_INDEX:
@@ -2729,7 +2735,10 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
MemoryContext old;
List *relids = NIL;
int num_keys;
+
bool concurrent_warning = false;
+ bool tablespace_warning = false;
+ bool mapped_warning = false;
AssertArg(objectName);
Assert(objectKind == REINDEX_OBJECT_SCHEMA ||
@@ -2856,6 +2865,35 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
continue;
}
+ if (OidIsValid(params->tablespaceOid) &&
+ IsSystemClass(relid, classtuple))
+ {
+ if (!allowSystemTableMods)
+ {
+ /* Skip all system relations, if not allowSystemTableMods */
+ if (!tablespace_warning)
+ ereport(WARNING,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("cannot change tablespace of indexes on system relations, skipping all")));
+ tablespace_warning = true;
+ continue;
+ }
+ else if (!OidIsValid(classtuple->relfilenode))
+ {
+ /*
+ * Skip all mapped relations if TABLESPACE is specified.
+ * OidIsValid(relfilenode) checks that, similar to
+ * RelationIsMapped().
+ */
+ if (!mapped_warning)
+ ereport(WARNING,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot change tablespace of indexes on mapped relations, skipping all")));
+ mapped_warning = true;
+ continue;
+ }
+ }
+
/* Save the list of relation OIDs in private context */
old = MemoryContextSwitchTo(private_context);
@@ -2969,9 +3007,27 @@ ReindexPartitions(Oid relid, ReindexParams *params, bool isTopLevel)
MemoryContext old_context;
/*
- * This discards partitioned tables, partitioned indexes and foreign
- * tables.
+ * Foreign tables and partitioned relations are not themselves
+ * reindexed - leaf partitions are processed directly. But any
+ * tablespace change is recorded in the catalog for partitioned
+ * relations.
*/
+ if (partkind == RELKIND_PARTITIONED_INDEX)
+ (void) SetRelTablespace(partoid, params->tablespaceOid);
+ else if (partkind == RELKIND_PARTITIONED_TABLE)
+ {
+ Relation rel = table_open(partoid, ShareLock);
+ List *indexIds = RelationGetIndexList(rel);
+ ListCell *lc;
+
+ table_close(rel, NoLock);
+ foreach (lc, indexIds)
+ {
+ Oid indexid = lfirst_oid(lc);
+ (void) SetRelTablespace(indexid, params->tablespaceOid);
+ }
+ }
+
if (!RELKIND_HAS_STORAGE(partkind))
continue;
@@ -3390,6 +3446,13 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
return false;
}
+ /* It's not a shared catalog, so refuse to move it to shared tablespace */
+ if (params->tablespaceOid == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move non-shared relation to tablespace \"%s\"",
+ get_tablespace_name(params->tablespaceOid))));
+
Assert(heapRelationIds != NIL);
/*-----
@@ -3461,6 +3524,7 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
/* Create new index definition based on given index */
newIndexId = index_concurrently_create_copy(heapRel,
idx->indexId,
+ params->tablespaceOid,
concurrentName);
/*
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 6abcbea963..ac4b40561b 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3641,7 +3641,9 @@ psql_completion(const char *text, int start, int end)
* one word, so the above test is correct.
*/
if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
- COMPLETE_WITH("CONCURRENTLY", "VERBOSE");
+ COMPLETE_WITH("CONCURRENTLY", "TABLESPACE", "VERBOSE");
+ else if (TailMatches("TABLESPACE"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
}
/* SECURITY LABEL */
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 266f8950dc..56e403ac61 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -33,6 +33,7 @@ typedef enum
typedef struct ReindexParams
{
bits32 options; /* bitmask of REINDEXOPT_* */
+ Oid tablespaceOid; /* tablespace to rebuild index */
} ReindexParams;
/* flag bits for ReindexParams->flags */
@@ -92,6 +93,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,
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 1a181016d7..6e94ffe617 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -17,6 +17,48 @@ ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- f
ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
+-- create table to test REINDEX with TABLESPACE change
+CREATE TABLE regress_tblspace_test_tbl (num1 bigint, num2 double precision, num3 double precision);
+INSERT INTO regress_tblspace_test_tbl (num1, num2, num3)
+ SELECT round(random()*100), random(), random()*42
+ FROM generate_series(1, 20000) s(i);
+CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1);
+
+-- check that REINDEX with TABLESPACE change is transactional
+BEGIN;
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_tbl_idx;
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl;
+ROLLBACK;
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+
+-- first, let us reindex and move the entire database, after that return everything back
+REINDEX (TABLESPACE regress_tblspace) DATABASE regression; -- ok with warning
+REINDEX (TABLESPACE pg_default) DATABASE regression; -- ok with warning
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+
+-- check REINDEX with TABLESPACE change
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_tbl_idx; -- ok
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl; -- ok
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_authid; -- fail
+REINDEX (TABLESPACE regress_tblspace) SYSTEM CONCURRENTLY postgres; -- fail
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_am; -- fail
+REINDEX (TABLESPACE pg_global) INDEX regress_tblspace_test_tbl_idx; -- fail
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_am; -- fail
+
+-- check that all relations moved to new tablespace
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+ORDER BY relname;
+
+-- move indexes back to pg_default tablespace
+REINDEX (TABLESPACE pg_default) TABLE CONCURRENTLY regress_tblspace_test_tbl; -- ok
+
+-- check that all relations moved back to pg_default
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+
-- create a schema we can use
CREATE SCHEMA testschema;
@@ -96,6 +138,14 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
\d testschema.part_a_idx
\d+ testschema.part_a_idx
+-- REINDEX partitioned indexes to new tablespace
+REINDEX (TABLESPACE pg_default) TABLE testschema.part;
+\d testschema.part
+\d testschema.part1
+REINDEX (CONCURRENTLY, TABLESPACE regress_tblspace) INDEX testschema.part_a_idx;
+\d testschema.part
+\d testschema.part1
+
-- partitioned rels cannot specify the default tablespace. These fail:
CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
CREATE TABLE testschema.dflt (a int PRIMARY KEY USING INDEX TABLESPACE pg_default) PARTITION BY LIST (a);
@@ -282,6 +332,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 94c5f023c6..0927d1c7ad 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -20,6 +20,65 @@ ERROR: unrecognized parameter "some_nonexistent_parameter"
ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
ERROR: RESET must not include values for parameters
ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
+-- create table to test REINDEX with TABLESPACE change
+CREATE TABLE regress_tblspace_test_tbl (num1 bigint, num2 double precision, num3 double precision);
+INSERT INTO regress_tblspace_test_tbl (num1, num2, num3)
+ SELECT round(random()*100), random(), random()*42
+ FROM generate_series(1, 20000) s(i);
+CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1);
+-- check that REINDEX with TABLESPACE change is transactional
+BEGIN;
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_tbl_idx;
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl;
+ROLLBACK;
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+ relname
+---------
+(0 rows)
+
+-- first, let us reindex and move the entire database, after that return everything back
+REINDEX (TABLESPACE regress_tblspace) DATABASE regression; -- ok with warning
+WARNING: cannot change tablespace of indexes on system relations, skipping all
+REINDEX (TABLESPACE pg_default) DATABASE regression; -- ok with warning
+WARNING: cannot change tablespace of indexes on system relations, skipping all
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+ relname
+---------
+(0 rows)
+
+-- check REINDEX with TABLESPACE change
+REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_tbl_idx; -- ok
+REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_tbl; -- ok
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_authid; -- fail
+ERROR: permission denied: "pg_authid_rolname_index" is a system catalog
+REINDEX (TABLESPACE regress_tblspace) SYSTEM CONCURRENTLY postgres; -- fail
+ERROR: cannot reindex system catalogs concurrently
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_am; -- fail
+ERROR: cannot reindex system catalogs concurrently
+REINDEX (TABLESPACE pg_global) INDEX regress_tblspace_test_tbl_idx; -- fail
+ERROR: cannot move non-shared relation to tablespace "pg_global"
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_am; -- fail
+ERROR: permission denied: "pg_am_name_index" is a system catalog
+-- check that all relations moved to new tablespace
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace')
+ORDER BY relname;
+ relname
+-------------------------------
+ regress_tblspace_test_tbl_idx
+(1 row)
+
+-- move indexes back to pg_default tablespace
+REINDEX (TABLESPACE pg_default) TABLE CONCURRENTLY regress_tblspace_test_tbl; -- ok
+-- check that all relations moved back to pg_default
+SELECT relname FROM pg_class
+WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace');
+ relname
+---------
+(0 rows)
+
-- create a schema we can use
CREATE SCHEMA testschema;
-- try a table
@@ -199,6 +258,47 @@ Partitions: testschema.part1_a_idx,
testschema.part2_a_idx
Tablespace: "regress_tblspace"
+-- REINDEX partitioned indexes to new tablespace
+REINDEX (TABLESPACE pg_default) TABLE testschema.part;
+\d testschema.part
+ Partitioned table "testschema.part"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: LIST (a)
+Indexes:
+ "part_a_idx" btree (a)
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d testschema.part1
+ Table "testschema.part1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition of: testschema.part FOR VALUES IN (1)
+Indexes:
+ "part1_a_idx" btree (a)
+
+REINDEX (CONCURRENTLY, TABLESPACE regress_tblspace) INDEX testschema.part_a_idx;
+\d testschema.part
+ Partitioned table "testschema.part"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: LIST (a)
+Indexes:
+ "part_a_idx" btree (a), tablespace "regress_tblspace"
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d testschema.part1
+ Table "testschema.part1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition of: testschema.part FOR VALUES IN (1)
+Indexes:
+ "part1_a_idx" btree (a), tablespace "regress_tblspace"
+
-- partitioned rels cannot specify the default tablespace. These fail:
CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
ERROR: cannot specify default tablespace for partitioned relations
@@ -741,6 +841,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.20.1
[text/x-diff] v2-0001-Ectract-common-part-from-ATExecSetTableSpaceNoSto.patch (4.9K, ../03f88f70618ce73e75837b6125a143f7@postgrespro.ru/3-v2-0001-Ectract-common-part-from-ATExecSetTableSpaceNoSto.patch)
download | inline diff:
From 142664d5fa29d3f93f40fc7856bae7ab62bad27b Mon Sep 17 00:00:00 2001
From: Alexey Kondratov <kondratov.aleksey@gmail.com>
Date: Wed, 20 Jan 2021 20:21:12 +0300
Subject: [PATCH v2 1/2] Ectract common part from ATExecSetTableSpaceNoStorage
for a future usage
---
src/backend/commands/tablecmds.c | 97 +++++++++++++++++++-------------
src/include/commands/tablecmds.h | 2 +
2 files changed, 59 insertions(+), 40 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 8687e9a97c..94567eb985 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -13291,6 +13291,59 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
list_free(reltoastidxids);
}
+/*
+ * SetRelTablespace - modify relation tablespace in the pg_class entry.
+ *
+ * 'reloid' is an Oid of relation to be modified.
+ * 'tablespaceOid' is an Oid of new tablespace.
+ *
+ * Catalog modification is done only if tablespaceOid is different from
+ * the currently set. Returned bool value is indicating whether any changes
+ * were made or not.
+ */
+bool
+SetRelTablespace(Oid reloid, Oid tablespaceOid)
+{
+ Relation pg_class;
+ HeapTuple tuple;
+ Form_pg_class rd_rel;
+ bool changed = false;
+ Oid oldTablespaceOid;
+
+ /* Get a modifiable copy of the relation's pg_class row. */
+ pg_class = table_open(RelationRelationId, RowExclusiveLock);
+
+ tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(reloid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u", reloid);
+ rd_rel = (Form_pg_class) GETSTRUCT(tuple);
+
+ /* No work if no change in tablespace. */
+ oldTablespaceOid = rd_rel->reltablespace;
+ if (tablespaceOid != oldTablespaceOid ||
+ (tablespaceOid == MyDatabaseTableSpace && OidIsValid(oldTablespaceOid)))
+ {
+ /* Update the pg_class row. */
+ rd_rel->reltablespace = (tablespaceOid == MyDatabaseTableSpace) ?
+ InvalidOid : tablespaceOid;
+ CatalogTupleUpdate(pg_class, &tuple->t_self, tuple);
+
+ changed = true;
+ }
+
+ if (changed)
+ /* Record dependency on tablespace */
+ changeDependencyOnTablespace(RelationRelationId,
+ reloid, rd_rel->reltablespace);
+
+ InvokeObjectPostAlterHook(RelationRelationId, reloid, 0);
+
+ heap_freetuple(tuple);
+ table_close(pg_class, RowExclusiveLock);
+
+ return changed;
+}
+
/*
* Special handling of ALTER TABLE SET TABLESPACE for relations with no
* storage that have an interest in preserving tablespace.
@@ -13301,11 +13354,7 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
static void
ATExecSetTableSpaceNoStorage(Relation rel, Oid newTableSpace)
{
- HeapTuple tuple;
- Oid oldTableSpace;
- Relation pg_class;
- Form_pg_class rd_rel;
- Oid reloid = RelationGetRelid(rel);
+ Oid reloid = RelationGetRelid(rel);
/*
* Shouldn't be called on relations having storage; these are processed in
@@ -13319,41 +13368,9 @@ ATExecSetTableSpaceNoStorage(Relation rel, Oid newTableSpace)
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("only shared relations can be placed in pg_global tablespace")));
- /*
- * No work if no change in tablespace.
- */
- oldTableSpace = rel->rd_rel->reltablespace;
- if (newTableSpace == oldTableSpace ||
- (newTableSpace == MyDatabaseTableSpace && oldTableSpace == 0))
- {
- InvokeObjectPostAlterHook(RelationRelationId, reloid, 0);
- return;
- }
-
- /* Get a modifiable copy of the relation's pg_class row */
- pg_class = table_open(RelationRelationId, RowExclusiveLock);
-
- tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(reloid));
- if (!HeapTupleIsValid(tuple))
- elog(ERROR, "cache lookup failed for relation %u", reloid);
- rd_rel = (Form_pg_class) GETSTRUCT(tuple);
-
- /* update the pg_class row */
- rd_rel->reltablespace = (newTableSpace == MyDatabaseTableSpace) ? InvalidOid : newTableSpace;
- CatalogTupleUpdate(pg_class, &tuple->t_self, tuple);
-
- /* Record dependency on tablespace */
- changeDependencyOnTablespace(RelationRelationId,
- reloid, rd_rel->reltablespace);
-
- InvokeObjectPostAlterHook(RelationRelationId, reloid, 0);
-
- heap_freetuple(tuple);
-
- table_close(pg_class, RowExclusiveLock);
-
- /* Make sure the reltablespace change is visible */
- CommandCounterIncrement();
+ if (SetRelTablespace(reloid, newTableSpace))
+ /* Make sure the reltablespace change is visible */
+ CommandCounterIncrement();
}
/*
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index 08c463d3c4..35553fd41a 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -61,6 +61,8 @@ extern void ExecuteTruncateGuts(List *explicit_rels, List *relids, List *relids_
extern void SetRelationHasSubclass(Oid relationId, bool relhassubclass);
+extern bool SetRelTablespace(Oid reloid, Oid tablespaceOid);
+
extern ObjectAddress renameatt(RenameStmt *stmt);
extern ObjectAddress RenameConstraint(RenameStmt *stmt);
base-commit: c2dc1a79767a0f947e1145f82eb65dfe4360d25f
--
2.20.1
view thread (154+ messages) latest in thread
Message-ID: <03f88f70618ce73e75837b6125a143f7@postgrespro.ru>
Permalink: ../03f88f70618ce73e75837b6125a143f7@postgrespro.ru/
Also on: postgresql.org/message-id/03f88f70618ce73e75837b6125a143f7@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, alvherre@alvh.no-ip.org, michael@paquier.xyz, pryzby@telsasoft.com, peter.eisentraut@enterprisedb.com, masahiko.sawada@2ndquadrant.com, steve@ssinger.info, robertmhaas@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: <03f88f70618ce73e75837b6125a143f7@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