From 28c12ae6f353abec9e4bcb141d287ab981298353 Mon Sep 17 00:00:00 2001 From: Julien Rouhaud Date: Thu, 25 Feb 2021 01:33:58 +0800 Subject: [PATCH v7 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 using a new pg_index_has_outdated_dependency(regclass) SQL function. Catversion (should be) bumped. Author: Julien Rouhaud Reviewed-by: Michael Paquier, Mark Dilger Discussion: https://postgr.es/m/20201203093143.GA64934%40nol --- doc/src/sgml/func.sgml | 27 ++++-- src/backend/catalog/index.c | 22 +++++ src/bin/scripts/reindexdb.c | 143 ++++++++++++++++++++++++----- src/bin/scripts/t/090_reindexdb.pl | 34 ++++++- src/include/catalog/pg_proc.dat | 4 + 5 files changed, 198 insertions(+), 32 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 9492a3c6b9..0eda6678ac 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -26448,12 +26448,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. @@ -26538,6 +26539,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/src/backend/catalog/index.c b/src/backend/catalog/index.c index 47e6a54149..6848e61df3 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1390,6 +1390,28 @@ do_check_index_has_outdated_collation(const ObjectAddress *otherObject, return false; } +/* + * SQL wrapper around index_has_outdated_dependency. + */ +Datum +pg_index_has_outdated_dependency(PG_FUNCTION_ARGS) +{ + Oid indexOid = PG_GETARG_OID(0); + Relation rel; + bool res; + + rel = try_index_open(indexOid, AccessShareLock); + + if (rel == NULL) + 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. diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c index fc0681538a..d252b1454f 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(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(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(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(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(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 */ @@ -485,7 +522,8 @@ reindex_one_database(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); @@ -510,7 +548,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 = ", "; @@ -537,6 +575,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, ") "); @@ -642,7 +686,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; @@ -661,16 +705,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 pg_catalog.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: @@ -688,7 +757,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" @@ -706,8 +786,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 pg_catalog.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; @@ -755,7 +847,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; @@ -779,7 +871,7 @@ reindex_all_databases(ConnParams *cparams, reindex_one_database(cparams, REINDEX_DATABASE, NULL, progname, echo, verbose, concurrently, - concurrentCons, tablespace); + concurrentCons, tablespace, outdated); } PQclear(result); @@ -798,6 +890,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 dependencies\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..91fd602041 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/index "t2_id_idx" has no outdated dependency/], + 'verbose reindexdb for outdated dependencies database wide reports all ignored indexes'); +$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'); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 93393fcfd4..911c12ee2c 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' }, -- 2.30.1