From 1958cc2cebc7457633cc81b9a49f041ac2adc17f Mon Sep 17 00:00:00 2001 From: Alexey Kondratov Date: Wed, 27 Jan 2021 00:46:17 +0300 Subject: [PATCH v10 1/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 | 33 +++- src/backend/catalog/index.c | 45 +++++- src/backend/commands/indexcmds.c | 76 ++++++++++ src/bin/psql/tab-complete.c | 4 +- src/include/catalog/index.h | 9 +- src/test/regress/input/tablespace.source | 110 ++++++++++++++ src/test/regress/output/tablespace.source | 176 ++++++++++++++++++++++ 7 files changed, 443 insertions(+), 10 deletions(-) diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index 627b36300c..d84f8c74c8 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -27,6 +27,7 @@ REINDEX [ ( option [, ...] ) ] { IN CONCURRENTLY [ boolean ] VERBOSE [ boolean ] + TABLESPACE new_tablespace @@ -187,6 +188,21 @@ REINDEX [ ( option [, ...] ) ] { IN + + TABLESPACE + + + Specifies that indexes will be rebuilt on a new tablespace. + Cannot be used with "mapped" or (unless allow_system_table_mods) + system relations. If SCHEMA, + DATABASE or SYSTEM are specified, + then all "mapped" and system relations will be skipped and a single + WARNING will be generated. Indexes on TOAST tables + are reindexed, but not moved to the new tablespace. + + + + VERBOSE @@ -210,6 +226,14 @@ REINDEX [ ( option [, ...] ) ] { IN + + new_tablespace + + + The tablespace where indexes will be rebuilt. + + + @@ -292,7 +316,14 @@ REINDEX [ ( option [, ...] ) ] { IN with REINDEX INDEX or REINDEX TABLE, respectively. Each partition of the specified partitioned relation is reindexed in a separate transaction. Those commands cannot be used inside - a transaction block when working on a partitioned table or index. + a transaction block when working on a partitioned table or index. If + a REINDEX command fails when run on a partitioned + relation, and TABLESPACE was specified, then it may have + moved indexes on some partitions to the new tablespace. Re-running the command + will reindex all partitions and move previously-unprocessed indexes to the new + tablespace. Note that partitioned indexes are not moved to the + new_tablespace, only leaf + partitions holding data get reindexed and moved there. diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index b8cd35e995..e9af899ffc 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,12 @@ 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 tablespace to use for this index. */ 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 +1530,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, const char newInfo, indexColNames, indexRelation->rd_rel->relam, - indexRelation->rd_rel->reltablespace, + tablespaceOid, indexRelation->rd_indcollation, indclass->values, indcoloptions->values, @@ -3603,6 +3607,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,14 +3659,20 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, get_namespace_name(RelationGetNamespace(iRel)), RelationGetRelationName(iRel)); + if (set_tablespace) + set_tablespace = CheckRelationTableSpaceMove(iRel, + params->tablespaceOid); + /* - * Don't allow reindex on temp tables of other backends ... their local - * buffer manager is not going to cope. + * We don't support moving system relations into different tablespaces + * unless allow_system_table_mods=1. */ - if (RELATION_IS_OTHER_TEMP(iRel)) + if (set_tablespace && + !allowSystemTableMods && IsSystemRelation(iRel)) ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot reindex temporary tables of other sessions"))); + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied: \"%s\" is a system catalog", + RelationGetRelationName(iRel)))); /* * Don't allow reindex of an invalid index on TOAST table. This is a @@ -3680,6 +3691,22 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, */ CheckTableNotInUse(iRel, "REINDEX INDEX"); + /* Set new tablespace, if requested. */ + if (set_tablespace) + { + SetRelationTableSpace(iRel, params->tablespaceOid, InvalidOid); + + /* + * Schedule unlinking of the old index storage at transaction commit. + */ + 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. @@ -3964,10 +3991,14 @@ 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 table's indexes were moved to a new tablespace, the index + * on its toast table is not moved implicitly. */ ReindexParams newparams = *params; newparams.options &= ~(REINDEXOPT_MISSING_OK); + 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..c9c059bb58 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,22 @@ 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; + + /* Check permissions except when using database's default */ + if (OidIsValid(params.tablespaceOid) && + params.tablespaceOid != MyDatabaseTableSpace) + { + AclResult aclresult; + + aclresult = pg_tablespace_aclcheck(params.tablespaceOid, + GetUserId(), ACL_CREATE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, OBJECT_TABLESPACE, + get_tablespace_name(params.tablespaceOid)); + } + switch (stmt->kind) { case REINDEX_OBJECT_INDEX: @@ -2730,6 +2749,8 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, 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 +2877,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); @@ -3032,6 +3082,22 @@ ReindexMultipleInternal(List *relids, ReindexParams *params) continue; } + /* + * ExecReindex() does this check, but here we have to recheck + * it again to be sure that nothing changed between transactions. + */ + if (OidIsValid(params->tablespaceOid) && + params->tablespaceOid != MyDatabaseTableSpace) + { + AclResult aclresult; + + aclresult = pg_tablespace_aclcheck(params->tablespaceOid, + GetUserId(), ACL_CREATE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, OBJECT_TABLESPACE, + get_tablespace_name(params->tablespaceOid)); + } + relkind = get_rel_relkind(relid); relpersistence = get_rel_persistence(relid); @@ -3390,6 +3456,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 +3534,9 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params) /* Create new index definition based on given index */ newIndexId = index_concurrently_create_copy(heapRel, idx->indexId, + OidIsValid(params->tablespaceOid) ? + params->tablespaceOid : + indexRel->rd_rel->reltablespace, concurrentName); /* diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 17f7265038..a75647b1cc 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..8172bff617 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -29,10 +29,16 @@ typedef enum INDEX_DROP_SET_DEAD } IndexStateFlagsAction; -/* options for REINDEX */ +/* + * Options for REINDEX + * + * "tablespaceOid" is the tablespace where indexes will be rebuilt, + * or InvalidOid to keep each index on its current tablespace. + */ typedef struct ReindexParams { bits32 options; /* bitmask of REINDEXOPT_* */ + Oid tablespaceOid; /* tablespace to rebuild index */ } ReindexParams; /* flag bits for ReindexParams->flags */ @@ -92,6 +98,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..0d1d48721b 100644 --- a/src/test/regress/input/tablespace.source +++ b/src/test/regress/input/tablespace.source @@ -17,6 +17,101 @@ 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 (with TOAST) to test REINDEX with TABLESPACE change +CREATE TABLE regress_tblspace_test_tbl (num1 bigint, num2 double precision, t text); +INSERT INTO regress_tblspace_test_tbl (num1, num2, t) + SELECT round(random()*100), random(), 'text' + FROM generate_series(1, 10) s(i); +CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1); +CREATE TABLE regress_tblspace_test_pt(col1 int, col2 int) PARTITION BY HASH (col1); +CREATE TABLE regress_tblspace_test_pt_1 + PARTITION OF regress_tblspace_test_pt + FOR VALUES WITH (modulus 2, remainder 0) + PARTITION BY HASH (col2); +CREATE INDEX regress_tblspace_test_pt_idx_1 ON regress_tblspace_test_pt (col1); +CREATE INDEX regress_tblspace_test_pt_idx_2 ON regress_tblspace_test_pt (col2); + +-- first, check a no-op case +REINDEX (TABLESPACE pg_default) INDEX regress_tblspace_test_tbl_idx; +REINDEX (TABLESPACE pg_default) TABLE regress_tblspace_test_tbl; +REINDEX (TABLESPACE pg_default) INDEX regress_tblspace_test_pt_idx_2; +REINDEX (TABLESPACE pg_default) TABLE regress_tblspace_test_pt; + +-- 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'); + +-- 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'); + +-- partitioned indexes are not directly reindexed +REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_pt; -- ok +REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_pt_idx_1; -- ok +REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_pt_idx_2; -- ok +SELECT relname FROM pg_class +WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace'); + +-- indexes for new partitions should be created in the old tablespace +CREATE TABLE regress_tblspace_test_pt_2 + PARTITION OF regress_tblspace_test_pt + FOR VALUES WITH (modulus 2, remainder 1); +CREATE TABLE regress_tblspace_test_pt_1_1 + PARTITION OF regress_tblspace_test_pt_1 + FOR VALUES WITH (modulus 2, remainder 0); +SELECT relname FROM pg_class +WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace'); + +-- reindex partitioned table +REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_pt; -- ok +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; + +CREATE TEMP TABLE old_reindex_info AS SELECT relname, relfilenode FROM pg_class +WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_1'::regclass) WHERE isleaf = true); +INSERT INTO old_reindex_info SELECT relname, relfilenode FROM pg_class +WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_2'::regclass) WHERE isleaf = true); +INSERT INTO old_reindex_info SELECT relname, relfilenode FROM pg_class +WHERE relname = 'regress_tblspace_test_tbl_idx'; + +-- move indexes back to pg_default tablespace +REINDEX (TABLESPACE pg_default) TABLE CONCURRENTLY regress_tblspace_test_tbl; -- ok +REINDEX (TABLESPACE pg_default) TABLE regress_tblspace_test_pt; -- 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 TEMP TABLE new_reindex_info AS SELECT relname, relfilenode FROM pg_class +WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_1'::regclass) WHERE isleaf = true); +INSERT INTO new_reindex_info SELECT relname, relfilenode FROM pg_class +WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_2'::regclass) WHERE isleaf = true); +INSERT INTO new_reindex_info SELECT relname, relfilenode FROM pg_class +WHERE relname = 'regress_tblspace_test_tbl_idx'; + +SELECT * FROM new_reindex_info JOIN old_reindex_info ON new_reindex_info.relname = old_reindex_info.relname +WHERE new_reindex_info.relfilenode = old_reindex_info.relfilenode; + -- create a schema we can use CREATE SCHEMA testschema; @@ -96,6 +191,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); @@ -268,6 +371,10 @@ ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2; SET SESSION ROLE regress_tablespace_user2; CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail + +REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY regress_tblspace_test_tbl; -- fail +REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_pt_idx; -- fail + ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint; RESET ROLE; @@ -282,6 +389,9 @@ ALTER TABLE ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default -- Should succeed DROP TABLESPACE regress_tblspace_renamed; +DROP TABLE regress_tblspace_test_tbl; +DROP TABLE regress_tblspace_test_pt; + 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..5f0b372b43 100644 --- a/src/test/regress/output/tablespace.source +++ b/src/test/regress/output/tablespace.source @@ -20,6 +20,135 @@ 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 (with TOAST) to test REINDEX with TABLESPACE change +CREATE TABLE regress_tblspace_test_tbl (num1 bigint, num2 double precision, t text); +INSERT INTO regress_tblspace_test_tbl (num1, num2, t) + SELECT round(random()*100), random(), 'text' + FROM generate_series(1, 10) s(i); +CREATE INDEX regress_tblspace_test_tbl_idx ON regress_tblspace_test_tbl (num1); +CREATE TABLE regress_tblspace_test_pt(col1 int, col2 int) PARTITION BY HASH (col1); +CREATE TABLE regress_tblspace_test_pt_1 + PARTITION OF regress_tblspace_test_pt + FOR VALUES WITH (modulus 2, remainder 0) + PARTITION BY HASH (col2); +CREATE INDEX regress_tblspace_test_pt_idx_1 ON regress_tblspace_test_pt (col1); +CREATE INDEX regress_tblspace_test_pt_idx_2 ON regress_tblspace_test_pt (col2); +-- first, check a no-op case +REINDEX (TABLESPACE pg_default) INDEX regress_tblspace_test_tbl_idx; +REINDEX (TABLESPACE pg_default) TABLE regress_tblspace_test_tbl; +REINDEX (TABLESPACE pg_default) INDEX regress_tblspace_test_pt_idx_2; +REINDEX (TABLESPACE pg_default) TABLE regress_tblspace_test_pt; +-- 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) + +-- 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) + +-- partitioned indexes are not directly reindexed +REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_pt; -- ok +REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_pt_idx_1; -- ok +REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_pt_idx_2; -- ok +SELECT relname FROM pg_class +WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace'); + relname +--------- +(0 rows) + +-- indexes for new partitions should be created in the old tablespace +CREATE TABLE regress_tblspace_test_pt_2 + PARTITION OF regress_tblspace_test_pt + FOR VALUES WITH (modulus 2, remainder 1); +CREATE TABLE regress_tblspace_test_pt_1_1 + PARTITION OF regress_tblspace_test_pt_1 + FOR VALUES WITH (modulus 2, remainder 0); +SELECT relname FROM pg_class +WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace'); + relname +--------- +(0 rows) + +-- reindex partitioned table +REINDEX (TABLESPACE regress_tblspace) TABLE regress_tblspace_test_pt; -- ok +SELECT relname FROM pg_class +WHERE reltablespace=(SELECT oid FROM pg_tablespace WHERE spcname='regress_tblspace'); + relname +--------------------------------------- + regress_tblspace_test_pt_2_col1_idx + regress_tblspace_test_pt_2_col2_idx + regress_tblspace_test_pt_1_1_col1_idx + regress_tblspace_test_pt_1_1_col2_idx +(4 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: cannot move system relation "pg_authid_rolname_index" +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: only shared relations can be placed in pg_global tablespace +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_pt_1_1_col1_idx + regress_tblspace_test_pt_1_1_col2_idx + regress_tblspace_test_pt_2_col1_idx + regress_tblspace_test_pt_2_col2_idx + regress_tblspace_test_tbl_idx +(5 rows) + +CREATE TEMP TABLE old_reindex_info AS SELECT relname, relfilenode FROM pg_class +WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_1'::regclass) WHERE isleaf = true); +INSERT INTO old_reindex_info SELECT relname, relfilenode FROM pg_class +WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_2'::regclass) WHERE isleaf = true); +INSERT INTO old_reindex_info SELECT relname, relfilenode FROM pg_class +WHERE relname = 'regress_tblspace_test_tbl_idx'; +-- move indexes back to pg_default tablespace +REINDEX (TABLESPACE pg_default) TABLE CONCURRENTLY regress_tblspace_test_tbl; -- ok +REINDEX (TABLESPACE pg_default) TABLE regress_tblspace_test_pt; -- 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 TEMP TABLE new_reindex_info AS SELECT relname, relfilenode FROM pg_class +WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_1'::regclass) WHERE isleaf = true); +INSERT INTO new_reindex_info SELECT relname, relfilenode FROM pg_class +WHERE relname IN (SELECT relid::name FROM pg_partition_tree('regress_tblspace_test_pt_idx_2'::regclass) WHERE isleaf = true); +INSERT INTO new_reindex_info SELECT relname, relfilenode FROM pg_class +WHERE relname = 'regress_tblspace_test_tbl_idx'; +SELECT * FROM new_reindex_info JOIN old_reindex_info ON new_reindex_info.relname = old_reindex_info.relname +WHERE new_reindex_info.relfilenode = old_reindex_info.relfilenode; + relname | relfilenode | relname | relfilenode +---------+-------------+---------+------------- +(0 rows) + -- create a schema we can use CREATE SCHEMA testschema; -- try a table @@ -199,6 +328,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), 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) + +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 @@ -731,6 +901,10 @@ ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2; SET SESSION ROLE regress_tablespace_user2; CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail ERROR: permission denied for tablespace regress_tblspace +REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY regress_tblspace_test_tbl; -- fail +ERROR: permission denied for tablespace regress_tblspace +REINDEX (TABLESPACE regress_tblspace) INDEX regress_tblspace_test_pt_idx; -- fail +ERROR: permission denied for tablespace regress_tblspace ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint; RESET ROLE; ALTER TABLESPACE regress_tblspace RENAME TO regress_tblspace_renamed; @@ -741,6 +915,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 TABLE regress_tblspace_test_tbl; +DROP TABLE regress_tblspace_test_pt; DROP SCHEMA testschema CASCADE; NOTICE: drop cascades to 6 other objects DETAIL: drop cascades to table testschema.foo base-commit: 7c5d57caed4d8af705d0cc3131d0d8ed72b7a41d -- 2.20.1