Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lHKUu-00064R-5V for pgsql-hackers@arkaria.postgresql.org; Wed, 03 Mar 2021 05:56:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lHKUt-0003Vj-3c for pgsql-hackers@arkaria.postgresql.org; Wed, 03 Mar 2021 05:56:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lHKUs-0003Vc-OQ for pgsql-hackers@lists.postgresql.org; Wed, 03 Mar 2021 05:56:34 +0000 Received: from mail-pj1-x102b.google.com ([2607:f8b0:4864:20::102b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lHKUo-0000uW-PH for pgsql-hackers@lists.postgresql.org; Wed, 03 Mar 2021 05:56:34 +0000 Received: by mail-pj1-x102b.google.com with SMTP id d13-20020a17090abf8db02900c0590648b1so2348086pjs.1 for ; Tue, 02 Mar 2021 21:56:30 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to; bh=Ekwe/1MeQNGVbUwXDEJDTlWRvzmWwSRerXnTha3dk0s=; b=BOygqfkQASIOtmNp98OOePL+L3NVRuvvFYyyr6mvuO6wPDYQfNmXoMbezZJ1xoMkFs ajF3wWTPxZKWKebb8NmjdIaiNOr9hkfmE9d7q4Hh/OAZm6YLdD8cOT4CZja3rX0e8M7G iPc/L9i4shB1YCFIwg65fgVtN+g6UrTuMLoboDa+mguILlNA1rZZ1usowLEnxGmXhgyz gps1fdo+kRQ+zyiOsBnfYIPZX29djNgKnGXBT+GeHKQiyuDdGj0g2U1ks6sa0G3EHcNA IMANAE1kxNRy+y3gxWn2XmHc/pbFJBFZqdXhrbnN5BdHk0PLhNNu4zb0pdfMquF1zaYF wZfw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to; bh=Ekwe/1MeQNGVbUwXDEJDTlWRvzmWwSRerXnTha3dk0s=; b=cWrD4h4LSM5hs5qQh6Jrz9N0CGLk0uJTGreTRyi507IVtjA90DKnOo9nRL8EzRMd+Q NZ8zB2lvVzYaUS2epOahbi436Gh+DK38UvXj8jCXZcqc8XTHM9g02d2v94qU/xJJsM9r Ezt6VhDQ6CzmkgIkj7O2u+7uNXADhOzt7EJaG5Vob3FQ7aiA/0jz91l+4iePLdQkfSK6 AiLkK6vOXfVC/caFfQDrx6HajkdBClHH8T3rv/Pbi2a+pGJdLqWCLPscn0fsVIXydGqg qOQ/GFgtLSMVUyXohbsZ0F9muekSulmQSE5dQT7JD08JfnhO3Rmg2MFoySMZrWemuuOg bzOA== X-Gm-Message-State: AOAM530eGcF4+l/vELx+a+gZKaia4d25DO8GrzuSboOqY8L2EZso23KB 3PpWx93il3Dvu+jvCxL1U0jAtW+ontM= X-Google-Smtp-Source: ABdhPJwUGiwdvlwzIu/WhQJSVBaprYrfe/AiRuWiM+r5UxqXUfecTJAgQ1JTkmPERuiOsqr8ajJaJg== X-Received: by 2002:a17:90a:430e:: with SMTP id q14mr8149227pjg.11.1614750988321; Tue, 02 Mar 2021 21:56:28 -0800 (PST) Received: from nol (2001-b011-0008-945f-8e19-0880-613b-abb9.dynamic-ip6.hinet.net. [2001:b011:8:945f:8e19:880:613b:abb9]) by smtp.gmail.com with ESMTPSA id i2sm5075300pjj.35.2021.03.02.21.56.26 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 02 Mar 2021 21:56:27 -0800 (PST) Date: Wed, 3 Mar 2021 13:56:59 +0800 From: Julien Rouhaud To: Magnus Hagander Cc: Michael Paquier , PostgreSQL Hackers Subject: Re: REINDEX backend filtering Message-ID: <20210303055659.wowfwt2nyxuynzqg@nol> References: <20201203093143.GA64934@nol> <20210302040152.qmtcrv7sgu2lz54v@nol> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="oao5se3im4osqowp" Content-Disposition: inline In-Reply-To: <20210302040152.qmtcrv7sgu2lz54v@nol> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --oao5se3im4osqowp Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Tue, Mar 02, 2021 at 12:01:55PM +0800, Julien Rouhaud wrote: > > So, long running reindex due to some gigantic and/or numerous indexes on a > single (or few) table is not something that we can solve, but inefficient > reindex due to wrong table size / to-be-reindexed-indexes-size correlation can > be addressed. > > I would still prefer to go to backend implementation, so that all client tools > can benefit from it by default. We could simply export the current > index_has_oudated_collation(oid) function in sql, and tweak pg_dump to order > tables by the cumulated size of such indexes as you mentioned below, would > that work for you? > > Also, given Thomas proposal in a nearby email this function would be renamed to > index_has_oudated_dependencies(oid) or something like that. Please find attached v5 which address all previous comments: - consistently use "outdated" - use REINDEX (OUTDATED) grammar (with a new unreserved OUTDATED keyword) - new --outdated option to reindexdb - expose a new "pg_index_has_outdated_dependency(regclass)" SQL function - use that function in reindexdb --outdated to sort tables by total indexes-to-be-processed size --oao5se3im4osqowp Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v5-0001-Add-a-new-OUTDATED-filtering-facility-for-REINDEX.patch" From 5703ce209d414dd7a6fba18f581eca4671364834 Mon Sep 17 00:00:00 2001 From: Julien Rouhaud Date: Thu, 3 Dec 2020 15:54:42 +0800 Subject: [PATCH v5 1/2] Add a new OUTDATED filtering facility for REINDEX command. OUTDATED is added a new unreserved keyword. When used, REINDEX will only process indexes that have an outdated dependency. For now, only dependency on collations are supported but we'll likely support other kind of dependency in the future. Also add a new pg_index_has_outdated_dependency(regclass) SQL function, so client code can filter such indexes if needed. This function will also be used in a following commit to teach reindexdb to use this new OUTDATED option and order the tables by the amount of work that will actually be done. Catversion (should be) bumped. Author: Julien Rouhaud Reviewed-by: Discussion: https://postgr.es/m/20201203093143.GA64934%40nol --- doc/src/sgml/func.sgml | 27 ++++-- doc/src/sgml/ref/reindex.sgml | 12 +++ src/backend/catalog/index.c | 107 ++++++++++++++++++++- src/backend/commands/indexcmds.c | 12 ++- src/backend/parser/gram.y | 4 +- src/backend/utils/cache/relcache.c | 40 ++++++++ src/bin/psql/tab-complete.c | 2 +- src/include/catalog/index.h | 3 + src/include/catalog/pg_proc.dat | 4 + src/include/parser/kwlist.h | 1 + src/include/utils/relcache.h | 1 + src/test/regress/expected/create_index.out | 10 ++ src/test/regress/sql/create_index.sql | 10 ++ 13 files changed, 221 insertions(+), 12 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index bf99f82149..2cf6e66234 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -26381,12 +26381,13 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size shows the functions - available for index maintenance tasks. (Note that these maintenance - tasks are normally done automatically by autovacuum; use of these - functions is only required in special cases.) - These functions cannot be executed during recovery. - Use of these functions is restricted to superusers and the owner - of the given index. + available for index maintenance tasks. (Note that the maintenance + tasks performing actions on indexes are normally done automatically by + autovacuum; use of these functions is only required in special cases.) + The functions performing actions on indexes cannot be executed during + recovery. + Use of the functions performing actions on indexes is restricted to + superusers and the owner of the given index. @@ -26471,6 +26472,20 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size option. + + + + + pg_index_has_outdated_dependency + + pg_index_has_outdated_dependency ( index regclass ) + boolean + + + Check if the specified index has any outdated dependency. For now only + dependency on collations are supported. + +
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index b22d39eba9..2d94d49cde 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: CONCURRENTLY [ boolean ] + OUTDATED [ boolean ] TABLESPACE new_tablespace VERBOSE [ boolean ] @@ -188,6 +189,17 @@ REINDEX [ ( option [, ...] ) ] { IN + + OUTDATED + + + This option can be used to filter the list of indexes to rebuild and only + process indexes that have outdated dependencies. Fow now, the only + handle dependency is for the collation provider version. + + + + TABLESPACE diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 4ef61b5efd..571feac5db 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -100,6 +100,12 @@ typedef struct Oid pendingReindexedIndexes[FLEXIBLE_ARRAY_MEMBER]; } SerializedReindexState; +typedef struct +{ + Oid relid; /* targetr index oid */ + bool outdated; /* depends on at least on deprected collation? */ +} IndexHasOutdatedColl; + /* non-export function prototypes */ static bool relationHasPrimaryKey(Relation rel); static TupleDesc ConstructTupleDescriptor(Relation heapRelation, @@ -1351,6 +1357,105 @@ index_check_collation_versions(Oid relid) list_free(context.warned_colls); } +/* + * Detect if an index depends on at least one outdated collation. + * This is a callback for visitDependenciesOf(). + */ +static bool +do_check_index_has_outdated_collation(const ObjectAddress *otherObject, + const char *version, + char **new_version, + void *data) +{ + IndexHasOutdatedColl *context = data; + char *current_version; + + /* We only care about dependencies on collations. */ + if (otherObject->classId != CollationRelationId) + return false; + + /* Fast exit if we already found a outdated collation version. */ + if (context->outdated) + return false; + + /* Ask the provider for the current version. Give up if unsupported. */ + current_version = get_collation_version_for_oid(otherObject->objectId, + false); + if (!current_version) + return false; + + if (!version || strcmp(version, current_version) != 0) + context->outdated = true; + + return false; +} + +Datum +pg_index_has_outdated_dependency(PG_FUNCTION_ARGS) +{ + Oid indexOid = PG_GETARG_OID(0); + Relation rel; + bool isIndex; + bool res; + + rel = try_relation_open(indexOid, AccessShareLock); + + if (rel == NULL) + PG_RETURN_NULL(); + + isIndex = rel->rd_rel->relkind == RELKIND_INDEX; + + if (!isIndex) + { + relation_close(rel, AccessShareLock); + PG_RETURN_NULL(); + } + + res = index_has_outdated_dependency(indexOid); + + relation_close(rel, AccessShareLock); + + PG_RETURN_BOOL(res); +} + +/* + * Check whether the given index has a dependency with an outdated + * collation version. + * Caller must hold a suitable lock and make sure that the given Oid belongs to + * an index. + */ +bool +index_has_outdated_collation(Oid indexOid) +{ + ObjectAddress object; + IndexHasOutdatedColl context; + + object.classId = RelationRelationId; + object.objectId = indexOid; + object.objectSubId = 0; + + context.relid = indexOid; + context.outdated = false; + + visitDependenciesOf(&object, &do_check_index_has_outdated_collation, + &context); + + return context.outdated; +} + +/* + * Check whether the given index has a dependency with an outdated + * refobjversion. + * Caller must hold a suitable lock and make sure that the given Oid belongs to + * an index. + * For now, only dependency on collations are supported. + */ +bool +index_has_outdated_dependency(Oid indexOid) +{ + return index_has_outdated_collation(indexOid); +} + /* * Update the version for collations. A callback for visitDependenciesOf(). */ @@ -3991,7 +4096,7 @@ reindex_relation(Oid relid, int flags, ReindexParams *params) * relcache to get this with a sequential scan if ignoring system * indexes.) */ - indexIds = RelationGetIndexList(rel); + indexIds = RelationGetIndexListFiltered(rel, params->options); if (flags & REINDEX_REL_SUPPRESS_INDEX_USE) { diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 8bc652ecd3..0e1bcad101 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -2484,6 +2484,7 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel) bool concurrently = false; bool verbose = false; char *tablespacename = NULL; + bool outdated_filter = false; /* Parse option list */ foreach(lc, stmt->params) @@ -2496,6 +2497,8 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel) concurrently = defGetBoolean(opt); else if (strcmp(opt->defname, "tablespace") == 0) tablespacename = defGetString(opt); + else if (strcmp(opt->defname, "outdated") == 0) + outdated_filter = defGetBoolean(opt); else ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), @@ -2510,7 +2513,8 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel) params.options = (verbose ? REINDEXOPT_VERBOSE : 0) | - (concurrently ? REINDEXOPT_CONCURRENTLY : 0); + (concurrently ? REINDEXOPT_CONCURRENTLY : 0) | + (outdated_filter ? REINDEXOPT_OUTDATED : 0); /* * Assign the tablespace OID to move indexes to, with InvalidOid to do @@ -3307,7 +3311,8 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params) RelationGetRelationName(heapRelation)))); /* Add all the valid indexes of relation to list */ - foreach(lc, RelationGetIndexList(heapRelation)) + foreach(lc, RelationGetIndexListFiltered(heapRelation, + params->options)) { Oid cellOid = lfirst_oid(lc); Relation indexRelation = index_open(cellOid, @@ -3359,7 +3364,8 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params) MemoryContextSwitchTo(oldcontext); - foreach(lc2, RelationGetIndexList(toastRelation)) + foreach(lc2, RelationGetIndexListFiltered(toastRelation, + params->options)) { Oid cellOid = lfirst_oid(lc2); Relation indexRelation = index_open(cellOid, diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 652be0b96d..a67cfa867c 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -674,7 +674,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); NULLS_P NUMERIC OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR - ORDER ORDINALITY OTHERS OUT_P OUTER_P + ORDER ORDINALITY OTHERS OUT_P OUTDATED OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY @@ -15430,6 +15430,7 @@ unreserved_keyword: | OPTIONS | ORDINALITY | OTHERS + | OUTDATED | OVER | OVERRIDING | OWNED @@ -16002,6 +16003,7 @@ bare_label_keyword: | ORDINALITY | OTHERS | OUT_P + | OUTDATED | OUTER_P | OVERLAY | OVERRIDING diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 7ef510cd01..06545ee550 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -4620,6 +4620,46 @@ RelationGetIndexList(Relation relation) return result; } +/* + * RelationGetIndexListFiltered -- get a filtered list of indexes on this + * relation. + * + * Calls RelationGetIndexList and only keep indexes that have an outdated + * dependency. For now, only collation version dependency is supported. + */ +List * +RelationGetIndexListFiltered(Relation relation, bits32 options) +{ + List *result, + *full_list; + ListCell *lc; + + full_list = RelationGetIndexList(relation); + + /* Fast exit if no filtering was asked, or if the list if empty. */ + if (((options & REINDEXOPT_OUTDATED) == 0) || full_list == NIL) + return full_list; + + result = NIL; + foreach(lc, full_list) + { + Oid indexOid = lfirst_oid(lc); + + /* + * Check for outdated collation version dependency. + */ + if (index_has_outdated_collation(indexOid)) + { + result = lappend_oid(result, indexOid); + continue; + } + + /* Didn't find any outdated dependency, index will be ignored. */ + } + + return result; +} + /* * RelationGetStatExtList * get a list of OIDs of statistics objects on this relation diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 9f0208ac49..fac3e0476a 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -3674,7 +3674,7 @@ 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", "TABLESPACE", "VERBOSE"); + COMPLETE_WITH("CONCURRENTLY", "OUTDATED'", "TABLESPACE", "VERBOSE"); else if (TailMatches("TABLESPACE")) COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces); } diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index e22d506436..298c0c633c 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -42,6 +42,7 @@ typedef struct ReindexParams #define REINDEXOPT_REPORT_PROGRESS 0x02 /* report pgstat progress */ #define REINDEXOPT_MISSING_OK 0x04 /* skip missing relations */ #define REINDEXOPT_CONCURRENTLY 0x08 /* concurrent mode */ +#define REINDEXOPT_OUTDATED 0x10/* outdated collation only */ /* state info for validate_index bulkdelete callback */ typedef struct ValidateIndexState @@ -137,6 +138,8 @@ extern void FormIndexDatum(IndexInfo *indexInfo, bool *isnull); extern void index_check_collation_versions(Oid relid); +extern bool index_has_outdated_collation(Oid indexOid); +extern bool index_has_outdated_dependency(Oid indexOid); extern void index_update_collation_versions(Oid relid, Oid coll); extern void index_build(Relation heapRelation, diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 3d3974f467..4874d33996 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -949,6 +949,10 @@ proname => 'pg_indexam_has_property', provolatile => 's', prorettype => 'bool', proargtypes => 'oid text', prosrc => 'pg_indexam_has_property' }, +{ oid => '8102', descr => 'test property of an index', + proname => 'pg_index_has_outdated_dependency', provolatile => 's', + prorettype => 'bool', proargtypes => 'regclass', + prosrc => 'pg_index_has_outdated_dependency' }, { oid => '637', descr => 'test property of an index', proname => 'pg_index_has_property', provolatile => 's', prorettype => 'bool', proargtypes => 'regclass text', prosrc => 'pg_index_has_property' }, diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 28083aaac9..e6c725d9a6 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -295,6 +295,7 @@ PG_KEYWORD("order", ORDER, RESERVED_KEYWORD, AS_LABEL) PG_KEYWORD("ordinality", ORDINALITY, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("others", OTHERS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("out", OUT_P, COL_NAME_KEYWORD, BARE_LABEL) +PG_KEYWORD("outdated", OUTDATED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("outer", OUTER_P, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("over", OVER, UNRESERVED_KEYWORD, AS_LABEL) PG_KEYWORD("overlaps", OVERLAPS, TYPE_FUNC_NAME_KEYWORD, AS_LABEL) diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h index 2fcdf79323..a7a2272abd 100644 --- a/src/include/utils/relcache.h +++ b/src/include/utils/relcache.h @@ -45,6 +45,7 @@ extern void RelationClose(Relation relation); */ extern List *RelationGetFKeyList(Relation relation); extern List *RelationGetIndexList(Relation relation); +extern List *RelationGetIndexListFiltered(Relation relation, bits32 options); extern List *RelationGetStatExtList(Relation relation); extern Oid RelationGetPrimaryKeyIndex(Relation relation); extern Oid RelationGetReplicaIndex(Relation relation); diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 830fdddf24..5f7c49c650 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -2018,6 +2018,16 @@ INFO: index "reindex_verbose_pkey" was reindexed \set VERBOSITY default DROP TABLE reindex_verbose; -- +-- REINDEX (OUTDATED) +-- +CREATE TABLE reindex_coll(id integer primary key); +\set VERBOSITY terse \\ -- suppress machine-dependent details +-- no suitable index should be found +REINDEX (OUTDATED) TABLE reindex_coll; +NOTICE: table "reindex_coll" has no indexes to reindex +\set VERBOSITY default +DROP TABLE reindex_coll; +-- -- REINDEX CONCURRENTLY -- CREATE TABLE concur_reindex_tab (c1 int); diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index 8bc76f7c6f..808cacee5d 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -790,6 +790,16 @@ REINDEX (VERBOSE) TABLE reindex_verbose; \set VERBOSITY default DROP TABLE reindex_verbose; +-- +-- REINDEX (OUTDATED) +-- +CREATE TABLE reindex_coll(id integer primary key); +\set VERBOSITY terse \\ -- suppress machine-dependent details +-- no suitable index should be found +REINDEX (OUTDATED) TABLE reindex_coll; +\set VERBOSITY default +DROP TABLE reindex_coll; + -- -- REINDEX CONCURRENTLY -- -- 2.30.1 --oao5se3im4osqowp Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v5-0002-Add-a-outdated-option-to-reindexdb.patch" From 2f1388251a84badbc888fddfacb7c5357fcec873 Mon Sep 17 00:00:00 2001 From: Julien Rouhaud Date: Thu, 25 Feb 2021 01:33:58 +0800 Subject: [PATCH v5 2/2] Add a --outdated option to reindexdb This uses the new OUTDATED option for REINDEX. If user asks for multiple job, the list of tables to process will be sorted by the total size of underlying indexes that have outdated dependency. Author: Julien Rouhaud Reviewed-by: Discussion: https://postgr.es/m/20201203093143.GA64934%40nol --- src/bin/scripts/reindexdb.c | 143 ++++++++++++++++++++++++----- src/bin/scripts/t/090_reindexdb.pl | 34 ++++++- 2 files changed, 151 insertions(+), 26 deletions(-) diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c index cf28176243..369d164e65 100644 --- a/src/bin/scripts/reindexdb.c +++ b/src/bin/scripts/reindexdb.c @@ -35,20 +35,23 @@ typedef enum ReindexType static SimpleStringList *get_parallel_object_list(PGconn *conn, ReindexType type, SimpleStringList *user_list, + bool outdated, bool echo); static void reindex_one_database(const ConnParams *cparams, ReindexType type, SimpleStringList *user_list, const char *progname, bool echo, bool verbose, bool concurrently, - int concurrentCons, const char *tablespace); + int concurrentCons, const char *tablespace, + bool outdated); static void reindex_all_databases(ConnParams *cparams, const char *progname, bool echo, bool quiet, bool verbose, bool concurrently, - int concurrentCons, const char *tablespace); + int concurrentCons, const char *tablespace, + bool outdated); static void run_reindex_command(PGconn *conn, ReindexType type, const char *name, bool echo, bool verbose, bool concurrently, bool async, - const char *tablespace); + const char *tablespace, bool outdated); static void help(const char *progname); @@ -74,6 +77,7 @@ main(int argc, char *argv[]) {"concurrently", no_argument, NULL, 1}, {"maintenance-db", required_argument, NULL, 2}, {"tablespace", required_argument, NULL, 3}, + {"outdated", no_argument, NULL, 4}, {NULL, 0, NULL, 0} }; @@ -95,6 +99,7 @@ main(int argc, char *argv[]) bool quiet = false; bool verbose = false; bool concurrently = false; + bool outdated = false; SimpleStringList indexes = {NULL, NULL}; SimpleStringList tables = {NULL, NULL}; SimpleStringList schemas = {NULL, NULL}; @@ -170,6 +175,9 @@ main(int argc, char *argv[]) case 3: tablespace = pg_strdup(optarg); break; + case 4: + outdated = true; + break; default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); @@ -234,7 +242,8 @@ main(int argc, char *argv[]) cparams.dbname = maintenance_db; reindex_all_databases(&cparams, progname, echo, quiet, verbose, - concurrently, concurrentCons, tablespace); + concurrently, concurrentCons, tablespace, + outdated); } else if (syscatalog) { @@ -253,12 +262,17 @@ main(int argc, char *argv[]) pg_log_error("cannot reindex specific index(es) and system catalogs at the same time"); exit(1); } - if (concurrentCons > 1) { pg_log_error("cannot use multiple jobs to reindex system catalogs"); exit(1); } + if (outdated) + { + pg_log_error("cannot filter indexes having outdated dependencies " + "and reindex system catalogs at the same time"); + exit(1); + } if (dbname == NULL) { @@ -274,7 +288,7 @@ main(int argc, char *argv[]) reindex_one_database(&cparams, REINDEX_SYSTEM, NULL, progname, echo, verbose, - concurrently, 1, tablespace); + concurrently, 1, tablespace, outdated); } else { @@ -304,17 +318,20 @@ main(int argc, char *argv[]) if (schemas.head != NULL) reindex_one_database(&cparams, REINDEX_SCHEMA, &schemas, progname, echo, verbose, - concurrently, concurrentCons, tablespace); + concurrently, concurrentCons, tablespace, + outdated); if (indexes.head != NULL) reindex_one_database(&cparams, REINDEX_INDEX, &indexes, progname, echo, verbose, - concurrently, 1, tablespace); + concurrently, 1, tablespace, + outdated); if (tables.head != NULL) reindex_one_database(&cparams, REINDEX_TABLE, &tables, progname, echo, verbose, - concurrently, concurrentCons, tablespace); + concurrently, concurrentCons, tablespace, + outdated); /* * reindex database only if neither index nor table nor schema is @@ -323,7 +340,8 @@ main(int argc, char *argv[]) if (indexes.head == NULL && tables.head == NULL && schemas.head == NULL) reindex_one_database(&cparams, REINDEX_DATABASE, NULL, progname, echo, verbose, - concurrently, concurrentCons, tablespace); + concurrently, concurrentCons, tablespace, + outdated); } exit(0); @@ -334,7 +352,7 @@ reindex_one_database(const ConnParams *cparams, ReindexType type, SimpleStringList *user_list, const char *progname, bool echo, bool verbose, bool concurrently, int concurrentCons, - const char *tablespace) + const char *tablespace, bool outdated) { PGconn *conn; SimpleStringListCell *cell; @@ -363,6 +381,14 @@ reindex_one_database(const ConnParams *cparams, ReindexType type, exit(1); } + if (outdated && PQserverVersion(conn) < 140000) + { + PQfinish(conn); + pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s", + "outdated", "14"); + exit(1); + } + if (!parallel) { switch (process_type) @@ -399,14 +425,24 @@ reindex_one_database(const ConnParams *cparams, ReindexType type, */ if (concurrently) pg_log_warning("cannot reindex system catalogs concurrently, skipping all"); + else if (outdated) + { + /* + * The only supported kind of object that can be outdated + * is collation. No system catalog has any index that can + * depend on an outdated collation, so skip system + * catalogs. + */ + } else run_reindex_command(conn, REINDEX_SYSTEM, PQdb(conn), echo, verbose, concurrently, false, - tablespace); + tablespace, outdated); /* Build a list of relations from the database */ process_list = get_parallel_object_list(conn, process_type, - user_list, echo); + user_list, outdated, + echo); process_type = REINDEX_TABLE; /* Bail out if nothing to process */ @@ -419,7 +455,8 @@ reindex_one_database(const ConnParams *cparams, ReindexType type, /* Build a list of relations from all the schemas */ process_list = get_parallel_object_list(conn, process_type, - user_list, echo); + user_list, outdated, + echo); process_type = REINDEX_TABLE; /* Bail out if nothing to process */ @@ -484,7 +521,8 @@ reindex_one_database(const ConnParams *cparams, ReindexType type, ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); run_reindex_command(free_slot->connection, process_type, objname, - echo, verbose, concurrently, true, tablespace); + echo, verbose, concurrently, true, tablespace, + outdated); cell = cell->next; } while (cell != NULL); @@ -509,7 +547,7 @@ finish: static void run_reindex_command(PGconn *conn, ReindexType type, const char *name, bool echo, bool verbose, bool concurrently, bool async, - const char *tablespace) + const char *tablespace, bool outdated) { const char *paren = "("; const char *comma = ", "; @@ -536,6 +574,12 @@ run_reindex_command(PGconn *conn, ReindexType type, const char *name, sep = comma; } + if (outdated) + { + appendPQExpBuffer(&sql, "%sOUTDATED", sep); + sep = comma; + } + if (sep != paren) appendPQExpBufferStr(&sql, ") "); @@ -641,7 +685,7 @@ run_reindex_command(PGconn *conn, ReindexType type, const char *name, */ static SimpleStringList * get_parallel_object_list(PGconn *conn, ReindexType type, - SimpleStringList *user_list, bool echo) + SimpleStringList *user_list, bool outdated, bool echo) { PQExpBufferData catalog_query; PQExpBufferData buf; @@ -660,16 +704,41 @@ get_parallel_object_list(PGconn *conn, ReindexType type, { case REINDEX_DATABASE: Assert(user_list == NULL); + appendPQExpBufferStr(&catalog_query, "SELECT c.relname, ns.nspname\n" " FROM pg_catalog.pg_class c\n" " JOIN pg_catalog.pg_namespace ns" - " ON c.relnamespace = ns.oid\n" + " ON c.relnamespace = ns.oid\n"); + + if (outdated) + { + appendPQExpBufferStr(&catalog_query, + " JOIN pg_catalog.pg_index i" + " ON c.oid = i.indrelid\n" + " JOIN pg_catalog.pg_class ci" + " ON i.indexrelid = ci.oid\n"); + } + + appendPQExpBufferStr(&catalog_query, " WHERE ns.nspname != 'pg_catalog'\n" " AND c.relkind IN (" CppAsString2(RELKIND_RELATION) ", " - CppAsString2(RELKIND_MATVIEW) ")\n" - " ORDER BY c.relpages DESC;"); + CppAsString2(RELKIND_MATVIEW) ")\n"); + + if (outdated) + { + appendPQExpBufferStr(&catalog_query, + " GROUP BY c.relname, ns.nspname\n" + " ORDER BY sum(ci.relpages)" + " FILTER (WHERE pg_catalog.pg_index_has_outdated_dependency(ci.oid)) DESC;"); + } + else + { + appendPQExpBufferStr(&catalog_query, + " ORDER BY c.relpages DESC;"); + } + break; case REINDEX_SCHEMA: @@ -687,7 +756,18 @@ get_parallel_object_list(PGconn *conn, ReindexType type, "SELECT c.relname, ns.nspname\n" " FROM pg_catalog.pg_class c\n" " JOIN pg_catalog.pg_namespace ns" - " ON c.relnamespace = ns.oid\n" + " ON c.relnamespace = ns.oid\n"); + + if (outdated) + { + appendPQExpBufferStr(&catalog_query, + " JOIN pg_catalog.pg_index i" + " ON c.oid = i.indrelid\n" + " JOIN pg_catalog.pg_class ci" + " ON i.indexrelid = ci.oid\n"); + } + + appendPQExpBufferStr(&catalog_query, " WHERE c.relkind IN (" CppAsString2(RELKIND_RELATION) ", " CppAsString2(RELKIND_MATVIEW) ")\n" @@ -705,8 +785,20 @@ get_parallel_object_list(PGconn *conn, ReindexType type, appendStringLiteralConn(&catalog_query, nspname, conn); } - appendPQExpBufferStr(&catalog_query, ")\n" - " ORDER BY c.relpages DESC;"); + appendPQExpBufferStr(&catalog_query, ")\n"); + + if (outdated) + { + appendPQExpBufferStr(&catalog_query, + " GROUP BY c.relname, ns.nspname\n" + " ORDER BY sum(ci.relpages)" + " FILTER (WHERE pg_catalog.pg_index_has_outdated_dependency(ci.oid)) DESC;"); + } + else + { + appendPQExpBufferStr(&catalog_query, + " ORDER BY c.relpages DESC;"); + } } break; @@ -754,7 +846,7 @@ static void reindex_all_databases(ConnParams *cparams, const char *progname, bool echo, bool quiet, bool verbose, bool concurrently, int concurrentCons, - const char *tablespace) + const char *tablespace, bool outdated) { PGconn *conn; PGresult *result; @@ -778,7 +870,7 @@ reindex_all_databases(ConnParams *cparams, reindex_one_database(cparams, REINDEX_DATABASE, NULL, progname, echo, verbose, concurrently, - concurrentCons, tablespace); + concurrentCons, tablespace, outdated); } PQclear(result); @@ -797,6 +889,7 @@ help(const char *progname) printf(_(" -e, --echo show the commands being sent to the server\n")); printf(_(" -i, --index=INDEX recreate specific index(es) only\n")); printf(_(" -j, --jobs=NUM use this many concurrent connections to reindex\n")); + printf(_(" --outdated only process indexes having outdated depencies\n")); printf(_(" -q, --quiet don't write any messages\n")); printf(_(" -s, --system reindex system catalogs\n")); printf(_(" -S, --schema=SCHEMA reindex specific schema(s) only\n")); diff --git a/src/bin/scripts/t/090_reindexdb.pl b/src/bin/scripts/t/090_reindexdb.pl index 159b637230..b60cac6081 100644 --- a/src/bin/scripts/t/090_reindexdb.pl +++ b/src/bin/scripts/t/090_reindexdb.pl @@ -3,7 +3,7 @@ use warnings; use PostgresNode; use TestLib; -use Test::More tests => 58; +use Test::More tests => 70; program_help_ok('reindexdb'); program_version_ok('reindexdb'); @@ -174,6 +174,9 @@ $node->command_fails( $node->command_fails( [ 'reindexdb', '-j', '2', '-i', 'i1', 'postgres' ], 'parallel reindexdb cannot process indexes'); +$node->command_fails( + [ 'reindexdb', '-s', '--outdated' ], + 'cannot reindex system catalog and filter indexes having outdated dependencies'); $node->issues_sql_like( [ 'reindexdb', '-j', '2', 'postgres' ], qr/statement:\ REINDEX SYSTEM postgres; @@ -196,3 +199,32 @@ $node->command_checks_all( qr/^reindexdb: warning: cannot reindex system catalogs concurrently, skipping all/s ], 'parallel reindexdb for system with --concurrently skips catalogs'); + +# Temporarily downgrade client-min-message to get the no-op report +$ENV{PGOPTIONS} = '--client-min-messages=NOTICE'; +$node->command_checks_all( + [ 'reindexdb', '--outdated', '-v', '-t', 's1.t1', 'postgres' ], + 0, + [qr/^$/], + [qr/table "t1" has no indexes to reindex/], + 'verbose reindexdb for outdated dependencies on a specific table reports no-op tables'); + +$node->command_checks_all( + [ 'reindexdb', '--outdated', '-v', '-d', 'postgres' ], + 0, + [qr/^$/], + [qr/^$/], + 'verbose reindexdb for outdated dependencies database wide silently ignore all tables'); +$node->command_checks_all( + [ 'reindexdb', '--outdated', '-v', '-j', '2', '-d', 'postgres' ], + 0, + [qr/^$/], + [qr/table "t1" has no indexes to reindex/], + 'parallel verbose reindexdb for outdated dependencies database wide reports no-op tables'); + +# Switch back to WARNING client-min-message +$ENV{PGOPTIONS} = '--client-min-messages=WARNING'; +$node->issues_sql_like( + [ 'reindexdb', '--outdated', '-t', 's1.t1', 'postgres' ], + qr/.*statement: REINDEX \(OUTDATED\) TABLE s1\.t1;/, + 'reindexdb for outdated dependencies specify the OUTDATED keyword'); -- 2.30.1 --oao5se3im4osqowp--