From 0408f3fa1834c54a0f45949ecf0616b03dbdda28 Mon Sep 17 00:00:00 2001 From: Alexey Kondratov Date: Mon, 23 Mar 2020 21:10:29 +0300 Subject: [PATCH v25 3/6] 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 | 100 ++++++++++++++++++++-- src/backend/commands/cluster.c | 2 +- src/backend/commands/indexcmds.c | 84 +++++++++++++++--- src/backend/commands/tablecmds.c | 2 +- src/backend/tcop/utility.c | 13 +-- src/bin/psql/tab-complete.c | 4 +- src/include/catalog/index.h | 5 +- src/include/commands/defrem.h | 6 +- src/test/regress/input/tablespace.source | 45 ++++++++++ src/test/regress/output/tablespace.source | 61 +++++++++++++ 11 files changed, 314 insertions(+), 30 deletions(-) diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index a32e192a87..b77bfa1cda 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -26,6 +26,7 @@ REINDEX [ ( option [, ...] ) ] { IN where option can be one of: VERBOSE [ boolean ] + TABLESPACE new_tablespace @@ -160,6 +161,19 @@ REINDEX [ ( option [, ...] ) ] { IN + + TABLESPACE + + + This specifies that indexes will be rebuilt on a new tablespace. + Cannot be used with "mapped" relations. If SCHEMA, + DATABASE or SYSTEM is specified, then + all unsuitable relations will be skipped and a single WARNING + will be generated. + + + + VERBOSE @@ -196,6 +210,14 @@ REINDEX [ ( option [, ...] ) ] { IN + + new_tablespace + + + The tablespace where indexes will be rebuilt. + + + diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 1d662e9af4..1fa5353b5b 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -56,6 +56,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" @@ -1218,9 +1219,13 @@ index_create(Relation heapRelation, * Create concurrently an index based on the definition of the one provided by * caller. The index is inserted into catalogs and needs to be built later * on. This is called during concurrent reindex processing. + * + * "tablespaceOid" is the new tablespace to use for this index. If + * InvalidOid, use the tablespace in-use instead. */ Oid -index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, const char *newName) +index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, + Oid tablespaceOid, const char *newName) { Relation indexRelation; IndexInfo *oldInfo, @@ -1350,7 +1355,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, @@ -3418,10 +3424,12 @@ IndexGetRelation(Oid indexId, bool missing_ok) /* * reindex_index - This routine is used to recreate a single index + * + * See comments of reindex_relation() for details about "tablespaceOid". */ void reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, - int options) + int options, Oid tablespaceOid) { Relation iRel, heapRelation; @@ -3430,6 +3438,7 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, volatile bool skipped_constraint = false; PGRUsage ru0; bool progress = (options & REINDEXOPT_REPORT_PROGRESS) != 0; + bool set_tablespace = OidIsValid(tablespaceOid); pg_rusage_init(&ru0); @@ -3480,6 +3489,35 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, elog(ERROR, "unsupported relation kind for index \"%s\"", RelationGetRelationName(iRel)); + /* + * We don't support moving system relations into different tablespaces + * unless allow_system_table_mods=1. + */ + if (set_tablespace && + !allowSystemTableMods && IsSystemRelation(iRel)) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied: \"%s\" is a system catalog", + RelationGetRelationName(iRel)))); + + /* + * We cannot support moving mapped relations into different tablespaces. + * (In particular this eliminates all shared catalogs.) + */ + if (set_tablespace && RelationIsMapped(iRel)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot change tablespace of mapped relation \"%s\"", + RelationGetRelationName(iRel)))); + + /* It's not a shared catalog, so refuse to move it to shared tablespace */ + if (tablespaceOid == GLOBALTABLESPACE_OID) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot move non-shared relation to tablespace \"%s\"", + get_tablespace_name(tablespaceOid)))); + + /* * Don't allow reindex on temp tables of other backends ... their local * buffer manager is not going to cope. @@ -3506,6 +3544,49 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence, */ CheckTableNotInUse(iRel, "REINDEX INDEX"); + if (tablespaceOid == MyDatabaseTableSpace) + tablespaceOid = InvalidOid; + + /* + * Set the new tablespace for the relation. Do that only in the + * case where the reindex caller wishes to enforce a new tablespace. + */ + if (set_tablespace && + tablespaceOid != iRel->rd_rel->reltablespace) + { + Relation pg_class; + Form_pg_class rd_rel; + HeapTuple tuple; + + /* First get a modifiable copy of the relation's pg_class row */ + pg_class = table_open(RelationRelationId, RowExclusiveLock); + + tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(indexId)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for relation %u", indexId); + rd_rel = (Form_pg_class) GETSTRUCT(tuple); + + /* + * Mark the relation as ready to be dropped at transaction commit, + * before making visible the new tablespace change so as this won't + * miss things. + */ + RelationDropStorage(iRel); + + /* Update the pg_class row */ + rd_rel->reltablespace = tablespaceOid; + CatalogTupleUpdate(pg_class, &tuple->t_self, tuple); + + heap_freetuple(tuple); + + table_close(pg_class, RowExclusiveLock); + + 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. @@ -3637,6 +3718,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). @@ -3669,7 +3753,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, int flags, int options, Oid tablespaceOid) { Relation rel; Oid toast_relid; @@ -3765,7 +3849,7 @@ reindex_relation(Oid relid, int flags, int options) } reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS), - persistence, options); + persistence, options, tablespaceOid); CommandCounterIncrement(); @@ -3794,9 +3878,13 @@ reindex_relation(Oid relid, int flags, int options) /* * Note that this should fail if the toast relation is missing, so * reset REINDEXOPT_MISSING_OK. + * + * Even if table was moved to new tablespace, normally toast cannot move. */ + Oid toasttablespaceOid = allowSystemTableMods ? tablespaceOid : InvalidOid; result |= reindex_relation(toast_relid, flags, - options & ~(REINDEXOPT_MISSING_OK)); + options & ~(REINDEXOPT_MISSING_OK), + toasttablespaceOid); } return result; diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index 4a3678831d..9419cc4dcf 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -1418,7 +1418,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, reindex_flags, 0, InvalidOid); /* Report that we are now doing clean up */ pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 209bbda65b..36ef930353 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, int options, Oid tablespaceOid); static void ReindexPartitionedIndex(Relation parentIdx); static void update_relispartition(Oid relationId, bool newval); static bool CompareOpclassOptions(Datum *opts1, Datum *opts2, int natts); @@ -2420,11 +2420,12 @@ ChooseIndexColumnNames(List *indexElems) * Recreate a specific index. */ void -ReindexIndex(ReindexStmt *stmt, int options) +ReindexIndex(ReindexStmt *stmt, int options, char *tablespace) { RangeVar *indexRelation = stmt->relation; struct ReindexIndexCallbackState state; Oid indOid; + Oid tablespaceOid = InvalidOid; Relation irel; char persistence; bool concurrent = (options & REINDEXOPT_CONCURRENT); @@ -2460,13 +2461,19 @@ ReindexIndex(ReindexStmt *stmt, int options) } persistence = irel->rd_rel->relpersistence; + + /* Define new tablespaceOid if requested */ + if (tablespace) + tablespaceOid = get_tablespace_oid(tablespace, false); + index_close(irel, NoLock); if (concurrent && persistence != RELPERSISTENCE_TEMP) - ReindexRelationConcurrently(indOid, options); + ReindexRelationConcurrently(indOid, options, tablespaceOid); else reindex_index(indOid, false, persistence, - options | REINDEXOPT_REPORT_PROGRESS); + options | REINDEXOPT_REPORT_PROGRESS, + tablespaceOid); } /* @@ -2544,12 +2551,13 @@ RangeVarCallbackForReindexIndex(const RangeVar *relation, * Recreate all indexes of a table (and of its toast table, if any) */ Oid -ReindexTable(ReindexStmt *stmt, int options) +ReindexTable(ReindexStmt *stmt, int options, char *tablespace) { RangeVar *relation = stmt->relation; Oid heapOid; bool result; bool concurrent = (options & REINDEXOPT_CONCURRENT); + Oid tablespaceOid = InvalidOid; /* * The lock level used here should match reindex_relation(). @@ -2564,9 +2572,13 @@ ReindexTable(ReindexStmt *stmt, int options) 0, RangeVarCallbackOwnsTable, NULL); + /* Define new tablespaceOid if requested */ + if (tablespace) + tablespaceOid = get_tablespace_oid(tablespace, false); + if (concurrent && get_rel_persistence(heapOid) != RELPERSISTENCE_TEMP) { - result = ReindexRelationConcurrently(heapOid, options); + result = ReindexRelationConcurrently(heapOid, options, tablespaceOid); if (!result) ereport(NOTICE, @@ -2578,7 +2590,8 @@ ReindexTable(ReindexStmt *stmt, int options) result = reindex_relation(heapOid, REINDEX_REL_PROCESS_TOAST | REINDEX_REL_CHECK_CONSTRAINTS, - options | REINDEXOPT_REPORT_PROGRESS); + options | REINDEXOPT_REPORT_PROGRESS, + tablespaceOid); if (!result) ereport(NOTICE, (errmsg("table \"%s\" has no indexes to reindex", @@ -2597,11 +2610,12 @@ ReindexTable(ReindexStmt *stmt, int options) * That means this must not be called within a user transaction block! */ void -ReindexMultipleTables(ReindexStmt *stmt, int options) +ReindexMultipleTables(ReindexStmt *stmt, int options, char *tablespace) { const char *objectName = stmt->name; ReindexObjectType objectKind = stmt->kind; Oid objectOid; + Oid tablespaceOid = InvalidOid; Relation relationRelation; TableScanDesc scan; ScanKeyData scan_keys[1]; @@ -2613,6 +2627,8 @@ ReindexMultipleTables(ReindexStmt *stmt, int options) int num_keys; bool concurrent_warning = false; bool concurrent = (options & REINDEXOPT_CONCURRENT); + bool tablespace_warning = false; + bool mapped_warning = false; AssertArg(objectName); Assert(objectKind == REINDEX_OBJECT_SCHEMA || @@ -2651,6 +2667,10 @@ ReindexMultipleTables(ReindexStmt *stmt, int options) objectName); } + /* Define new tablespaceOid if requested */ + if (tablespace) + tablespaceOid = get_tablespace_oid(tablespace, 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 @@ -2741,6 +2761,35 @@ ReindexMultipleTables(ReindexStmt *stmt, int options) continue; } + if (OidIsValid(tablespaceOid) && + IsSystemClass(relid, classtuple)) + { + if (!allowSystemTableMods) + { + /* Skip all system relations, if not allowSystemTableMods */ + if (!tablespace_warning) + ereport(WARNING, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("cannot change tablespace of indexes on system relations, skipping all"))); + tablespace_warning = true; + continue; + } + else if (!OidIsValid(classtuple->relfilenode)) + { + /* + * Skip all mapped relations 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); @@ -2784,7 +2833,8 @@ ReindexMultipleTables(ReindexStmt *stmt, int options) { (void) ReindexRelationConcurrently(relid, options | - REINDEXOPT_MISSING_OK); + REINDEXOPT_MISSING_OK, + tablespaceOid); /* ReindexRelationConcurrently() does the verbose output */ } else @@ -2796,7 +2846,8 @@ ReindexMultipleTables(ReindexStmt *stmt, int options) REINDEX_REL_CHECK_CONSTRAINTS, options | REINDEXOPT_REPORT_PROGRESS | - REINDEXOPT_MISSING_OK); + REINDEXOPT_MISSING_OK, + tablespaceOid); if (result && (options & REINDEXOPT_VERBOSE)) ereport(INFO, @@ -2826,6 +2877,9 @@ ReindexMultipleTables(ReindexStmt *stmt, int options) * itself will be rebuilt. If 'relationOid' belongs to a partitioned table * then we issue a warning to mention these are not yet supported. * + * 'tablespaceOid' is the tablespace where the relation's indexes will be + * rebuilt. + * * The locks taken on parent tables and involved indexes are kept until the * transaction is committed, at which point a session lock is taken on each * relation. Both of these protect against concurrent schema changes. @@ -2840,7 +2894,7 @@ ReindexMultipleTables(ReindexStmt *stmt, int options) * anyway, and a non-concurrent reindex is more efficient. */ static bool -ReindexRelationConcurrently(Oid relationOid, int options) +ReindexRelationConcurrently(Oid relationOid, int options, Oid tablespaceOid) { List *heapRelationIds = NIL; List *indexIds = NIL; @@ -3091,6 +3145,13 @@ ReindexRelationConcurrently(Oid relationOid, int options) return false; } + /* It's not a shared catalog, so refuse to move it to shared tablespace */ + if (tablespaceOid == GLOBALTABLESPACE_OID) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot move non-shared relation to tablespace \"%s\"", + get_tablespace_name(tablespaceOid)))); + Assert(heapRelationIds != NIL); /*----- @@ -3155,6 +3216,7 @@ ReindexRelationConcurrently(Oid relationOid, int options) /* Create new index definition based on given index */ newIndexId = index_concurrently_create_copy(heapRel, indexId, + tablespaceOid, concurrentName); /* diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 3e57c7f9e1..708f0d55cd 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -1886,7 +1886,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, REINDEX_REL_PROCESS_TOAST, 0, InvalidOid); } pgstat_count_truncate(rel); diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 2f66e198f7..331c3355d1 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -528,7 +528,7 @@ ProcessUtility(PlannedStmt *pstmt, /* Parse params not parsed by the grammar */ static -void parse_reindex_params(ParseState *pstate, ReindexStmt *stmt, int *options) +void parse_reindex_params(ParseState *pstate, ReindexStmt *stmt, int *options, char **tablespace) { ListCell *lc; foreach(lc, stmt->params) @@ -547,6 +547,8 @@ void parse_reindex_params(ParseState *pstate, ReindexStmt *stmt, int *options) *options |= REINDEXOPT_CONCURRENT; else *options &= ~REINDEXOPT_CONCURRENT; + else if (strcmp(opt->defname, "tablespace") == 0) + *tablespace = defGetString(opt); else ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), @@ -949,8 +951,9 @@ standard_ProcessUtility(PlannedStmt *pstmt, { ReindexStmt *stmt = (ReindexStmt *) parsetree; int options = 0; + char *tablespace = NULL; - parse_reindex_params(pstate, stmt, &options); + parse_reindex_params(pstate, stmt, &options, &tablespace); if (options & REINDEXOPT_CONCURRENT) PreventInTransactionBlock(isTopLevel, "REINDEX CONCURRENTLY"); @@ -958,10 +961,10 @@ standard_ProcessUtility(PlannedStmt *pstmt, switch (stmt->kind) { case REINDEX_OBJECT_INDEX: - ReindexIndex(stmt, options); + ReindexIndex(stmt, options, tablespace); break; case REINDEX_OBJECT_TABLE: - ReindexTable(stmt, options); + ReindexTable(stmt, options, tablespace); break; case REINDEX_OBJECT_SCHEMA: case REINDEX_OBJECT_SYSTEM: @@ -977,7 +980,7 @@ standard_ProcessUtility(PlannedStmt *pstmt, (stmt->kind == REINDEX_OBJECT_SCHEMA) ? "REINDEX SCHEMA" : (stmt->kind == REINDEX_OBJECT_SYSTEM) ? "REINDEX SYSTEM" : "REINDEX DATABASE"); - ReindexMultipleTables(stmt, options); + ReindexMultipleTables(stmt, options, tablespace); break; default: elog(ERROR, "unrecognized object type: %d", diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 245919aead..5d2cd88de9 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -3482,7 +3482,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 f58e8675f3..6a667df355 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, @@ -134,7 +135,7 @@ extern void index_set_state_flags(Oid indexId, IndexStateFlagsAction action); extern Oid IndexGetRelation(Oid indexId, bool missing_ok); extern void reindex_index(Oid indexId, bool skip_constraint_checks, - char relpersistence, int options); + char relpersistence, int options, Oid tablespaceOid); /* Flag bits for reindex_relation(): */ #define REINDEX_REL_PROCESS_TOAST 0x01 @@ -143,7 +144,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, int flags, int options, Oid tablespaceOid); extern bool ReindexIsProcessingHeap(Oid heapOid); extern bool ReindexIsProcessingIndex(Oid indexOid); diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index fedbbc17e0..58bd029be2 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -34,9 +34,9 @@ extern ObjectAddress DefineIndex(Oid relationId, bool check_not_in_use, bool skip_build, bool quiet); -extern void ReindexIndex(ReindexStmt *stmt, int options); -extern Oid ReindexTable(ReindexStmt *stmt, int options); -extern void ReindexMultipleTables(ReindexStmt *stmt, int options); +extern void ReindexIndex(ReindexStmt *stmt, int options, char *tablespace); +extern Oid ReindexTable(ReindexStmt *stmt, int options, char *tablespace); +extern void ReindexMultipleTables(ReindexStmt *stmt, int options, char *tablespace); 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/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source index a5f61a35dc..230cf46833 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; @@ -279,6 +321,9 @@ ALTER TABLE ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default -- Should succeed DROP TABLESPACE regress_tblspace_renamed; +DROP INDEX regress_tblspace_test_tbl_idx; +DROP TABLE regress_tblspace_test_tbl; + DROP SCHEMA testschema CASCADE; DROP ROLE regress_tablespace_user1; diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source index 162b591b31..ec5742df98 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 @@ -736,6 +795,8 @@ ALTER TABLE ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default NOTICE: no matching relations in tablespace "regress_tblspace_renamed" found -- Should succeed DROP TABLESPACE regress_tblspace_renamed; +DROP INDEX regress_tblspace_test_tbl_idx; +DROP TABLE regress_tblspace_test_tbl; DROP SCHEMA testschema CASCADE; NOTICE: drop cascades to 6 other objects DETAIL: drop cascades to table testschema.foo -- 2.17.0