From 7acd35b700d53cee585dd5ef7501f9c079a1649a Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Mon, 9 Mar 2020 15:34:41 -0500 Subject: [PATCH v12 2/3] Specially handle toast relations during REINDEX. Is this fine ? It says "cannot reindex system catalogs concurrently" (once), and hits the pg_toast tables for information_schema. Should it skip toast indexes (like it said) ? Or should it REINDEX them on the same tablespace? template1=# REINDEX DATABASE CONCURRENTLY template1 TABLESPACE pg_default; 2020-03-09 15:33:51.792 CDT [6464] WARNING: cannot reindex system catalogs concurrently, skipping all WARNING: cannot reindex system catalogs concurrently, skipping all 2020-03-09 15:33:51.794 CDT [6464] WARNING: skipping tablespace change of "pg_toast_12558_index" 2020-03-09 15:33:51.794 CDT [6464] DETAIL: Cannot move system relation, only REINDEX CONCURRENTLY is performed. WARNING: skipping tablespace change of "pg_toast_12558_index" DETAIL: Cannot move system relation, only REINDEX CONCURRENTLY is performed. 2020-03-09 15:33:51.924 CDT [6464] WARNING: skipping tablespace change of "pg_toast_12543_index" 2020-03-09 15:33:51.924 CDT [6464] DETAIL: Cannot move system relation, only REINDEX CONCURRENTLY is performed. WARNING: skipping tablespace change of "pg_toast_12543_index" DETAIL: Cannot move system relation, only REINDEX CONCURRENTLY is performed. 2020-03-09 15:33:51.982 CDT [6464] WARNING: skipping tablespace change of "pg_toast_12548_index" 2020-03-09 15:33:51.982 CDT [6464] DETAIL: Cannot move system relation, only REINDEX CONCURRENTLY is performed. WARNING: skipping tablespace change of "pg_toast_12548_index" DETAIL: Cannot move system relation, only REINDEX CONCURRENTLY is performed. 2020-03-09 15:33:52.048 CDT [6464] WARNING: skipping tablespace change of "pg_toast_12553_index" 2020-03-09 15:33:52.048 CDT [6464] DETAIL: Cannot move system relation, only REINDEX CONCURRENTLY is performed. WARNING: skipping tablespace change of "pg_toast_12553_index" DETAIL: Cannot move system relation, only REINDEX CONCURRENTLY is performed. REINDEX --- src/backend/catalog/index.c | 20 ++++++++++++++++---- src/backend/commands/indexcmds.c | 20 ++++++++++++-------- 2 files changed, 28 insertions(+), 12 deletions(-) diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index b76290e183..8f0353cca6 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -3474,11 +3474,23 @@ reindex_index(Oid indexId, Oid tablespaceOid, bool skip_constraint_checks, RelationGetRelationName(iRel)); /* - * We don't support moving system relations into different tablespaces, - * unless allow_system_table_mods=1. + * Skip tablespace change of all indexes on TOAST tables, unless + * allow_system_table_mods=1, but error out on system catalog. */ - if (set_tablespace && - !allowSystemTableMods && IsSystemRelation(iRel)) + if (set_tablespace && IsToastRelation(iRel)) + { + if (!allowSystemTableMods) + { + ereport(WARNING, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("skipping tablespace change of \"%s\"", + RelationGetRelationName(iRel)), + errdetail("Cannot move system relation, only REINDEX is performed."))); + set_tablespace = false; + } + } + else if (set_tablespace && + IsCatalogRelationOid(indexId) && !allowSystemTableMods) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied: \"%s\" is a system catalog", diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 59fb7dea2e..afc98cdbbb 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -3061,14 +3061,17 @@ ReindexRelationConcurrently(Oid relationOid, Oid tablespaceOid, int options) /* * We don't support moving system relations into different tablespaces, - * unless allow_system_table_mods=1. + * unless allow_system_table_mods=1. Reindexing concurrently is not + * allowed for system catalog, so we only check for indexes on + * TOAST tables here. */ - if (OidIsValid(tablespaceOid) && - !allowSystemTableMods && IsSystemRelation(indexRel)) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied: \"%s\" is a system catalog", - RelationGetRelationName(indexRel)))); + if (OidIsValid(tablespaceOid) + && IsToastRelation(indexRel) && !allowSystemTableMods) + ereport(WARNING, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("skipping tablespace change of \"%s\"", + RelationGetRelationName(indexRel)), + errdetail("Cannot move system relation, only REINDEX CONCURRENTLY is performed."))); /* * We cannot support moving mapped relations into different tablespaces. @@ -3099,7 +3102,8 @@ ReindexRelationConcurrently(Oid relationOid, Oid tablespaceOid, int options) /* Create new index definition based on given index */ newIndexId = index_concurrently_create_copy(heapRel, indexId, - tablespaceOid, + IsToastRelation(indexRel) && !allowSystemTableMods ? + InvalidOid : tablespaceOid, concurrentName); /* -- 2.19.1