From 53c4cd841bdcd854075e6e532665752ec9eb4628 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Wed, 28 Jun 2023 15:36:28 -0700 Subject: [PATCH v2 3/3] reindexdb: allow specifying objects to process in all databases --- src/bin/scripts/reindexdb.c | 118 +++++++++++++++---------- src/bin/scripts/t/090_reindexdb.pl | 17 ++++ src/bin/scripts/t/091_reindexdb_all.pl | 20 +++++ 3 files changed, 108 insertions(+), 47 deletions(-) diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c index 002c41f221..a1e97714f6 100644 --- a/src/bin/scripts/reindexdb.c +++ b/src/bin/scripts/reindexdb.c @@ -46,7 +46,10 @@ static void reindex_one_database(ConnParams *cparams, ReindexType type, 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 syscatalog, SimpleStringList *schemas, + SimpleStringList *tables, + SimpleStringList *indexes); static void run_reindex_command(PGconn *conn, ReindexType type, const char *name, bool echo, bool verbose, bool concurrently, bool async, @@ -203,25 +206,7 @@ main(int argc, char *argv[]) setup_cancel_handler(NULL); - if (alldb) - { - if (dbname) - pg_fatal("cannot reindex all databases and a specific one at the same time"); - if (syscatalog) - pg_fatal("cannot reindex all databases and system catalogs at the same time"); - if (schemas.head != NULL) - pg_fatal("cannot reindex specific schema(s) in all databases"); - if (tables.head != NULL) - pg_fatal("cannot reindex specific table(s) in all databases"); - if (indexes.head != NULL) - pg_fatal("cannot reindex specific index(es) in all databases"); - - cparams.dbname = maintenance_db; - - reindex_all_databases(&cparams, progname, echo, quiet, verbose, - concurrently, concurrentCons, tablespace); - } - else if (syscatalog) + if (syscatalog) { if (schemas.head != NULL) pg_fatal("cannot reindex specific schema(s) and system catalogs at the same time"); @@ -229,36 +214,41 @@ main(int argc, char *argv[]) pg_fatal("cannot reindex specific table(s) and system catalogs at the same time"); if (indexes.head != NULL) pg_fatal("cannot reindex specific index(es) and system catalogs at the same time"); + } - if (concurrentCons > 1) - pg_fatal("cannot use multiple jobs to reindex system catalogs"); - - if (dbname == NULL) - { - if (getenv("PGDATABASE")) - dbname = getenv("PGDATABASE"); - else if (getenv("PGUSER")) - dbname = getenv("PGUSER"); - else - dbname = get_user_name_or_exit(progname); - } - - cparams.dbname = dbname; + if (schemas.head != NULL && tables.head != NULL) + pg_fatal("cannot reindex specific schema(s) and specific table(s) at the same time"); + if (schemas.head != NULL && indexes.head != NULL) + pg_fatal("cannot reindex specific schema(s) and specific index(es) at the same time"); + if (tables.head != NULL && indexes.head != NULL) + pg_fatal("cannot reindex specific table(s) and specific index(es) at the same time"); - reindex_one_database(&cparams, REINDEX_SYSTEM, NULL, - progname, echo, verbose, - concurrently, 1, tablespace); - } - else + if (concurrentCons > 1) { /* * Index-level REINDEX is not supported with multiple jobs as we * cannot control the concurrent processing of multiple indexes * depending on the same relation. */ - if (concurrentCons > 1 && indexes.head != NULL) + if (indexes.head != NULL) pg_fatal("cannot use multiple jobs to reindex indexes"); + if (syscatalog) + pg_fatal("cannot use multiple jobs to reindex system catalogs"); + } + if (alldb) + { + if (dbname) + pg_fatal("cannot reindex all databases and a specific one at the same time"); + + cparams.dbname = maintenance_db; + + reindex_all_databases(&cparams, progname, echo, quiet, verbose, + concurrently, concurrentCons, tablespace, + syscatalog, &schemas, &tables, &indexes); + } + else + { if (dbname == NULL) { if (getenv("PGDATABASE")) @@ -271,6 +261,11 @@ main(int argc, char *argv[]) cparams.dbname = dbname; + if (syscatalog) + reindex_one_database(&cparams, REINDEX_SYSTEM, NULL, + progname, echo, verbose, + concurrently, 1, tablespace); + if (schemas.head != NULL) reindex_one_database(&cparams, REINDEX_SCHEMA, &schemas, progname, echo, verbose, @@ -287,10 +282,11 @@ main(int argc, char *argv[]) concurrently, concurrentCons, tablespace); /* - * reindex database only if neither index nor table nor schema is - * specified + * reindex database only if neither index nor table nor schema nor + * system catalogs is specified */ - if (indexes.head == NULL && tables.head == NULL && schemas.head == NULL) + if (!syscatalog && indexes.head == NULL && + tables.head == NULL && schemas.head == NULL) reindex_one_database(&cparams, REINDEX_DATABASE, NULL, progname, echo, verbose, concurrently, concurrentCons, tablespace); @@ -711,7 +707,9 @@ 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 syscatalog, + SimpleStringList *schemas, SimpleStringList *tables, + SimpleStringList *indexes) { PGconn *conn; PGresult *result; @@ -735,9 +733,35 @@ reindex_all_databases(ConnParams *cparams, cparams->override_dbname = dbname; - reindex_one_database(cparams, REINDEX_DATABASE, NULL, - progname, echo, verbose, concurrently, - concurrentCons, tablespace); + if (syscatalog) + reindex_one_database(cparams, REINDEX_SYSTEM, NULL, + progname, echo, verbose, + concurrently, 1, tablespace); + + if (schemas->head != NULL) + reindex_one_database(cparams, REINDEX_SCHEMA, schemas, + progname, echo, verbose, + concurrently, concurrentCons, tablespace); + + if (indexes->head != NULL) + reindex_one_database(cparams, REINDEX_INDEX, indexes, + progname, echo, verbose, + concurrently, 1, tablespace); + + if (tables->head != NULL) + reindex_one_database(cparams, REINDEX_TABLE, tables, + progname, echo, verbose, + concurrently, concurrentCons, tablespace); + + /* + * reindex database only if neither index nor table nor schema nor + * system catalogs is specified + */ + if (!syscatalog && indexes->head == NULL && + tables->head == NULL && schemas->head == NULL) + reindex_one_database(cparams, REINDEX_DATABASE, NULL, + progname, echo, verbose, + concurrently, concurrentCons, tablespace); } PQclear(result); diff --git a/src/bin/scripts/t/090_reindexdb.pl b/src/bin/scripts/t/090_reindexdb.pl index b663d0e741..6e318ec085 100644 --- a/src/bin/scripts/t/090_reindexdb.pl +++ b/src/bin/scripts/t/090_reindexdb.pl @@ -262,4 +262,21 @@ $node->command_ok( [ 'reindexdb', '-j', '2', '--concurrently', '-d', 'postgres' ], 'parallel reindexdb on database, concurrently'); +# invalid combinations of objects +$node->command_fails( + [ 'reindexdb', '-s', '-t', 'test1' ], + 'specify both --system and --table'); +$node->command_fails( + [ 'reindexdb', '-s', '-i', 'test1x' ], + 'specify both --system and --index'); +$node->command_fails( + [ 'reindexdb', '-s', '-S', 'pg_catalog' ], + 'specify both --system and --schema'); +$node->command_fails([ 'reindexdb', '-t', 'test1', '-i', 'test1x' ], + 'specify both --table and --index'); +$node->command_fails([ 'reindexdb', '-t', 'test1', '-S', 'pg_catalog' ], + 'specify both --table and --schema'); +$node->command_fails([ 'reindexdb', '-i', 'test1x', '-S', 'pg_catalog' ], + 'specify both --index and --schema'); + done_testing(); diff --git a/src/bin/scripts/t/091_reindexdb_all.pl b/src/bin/scripts/t/091_reindexdb_all.pl index 7f3e081ceb..753946b11f 100644 --- a/src/bin/scripts/t/091_reindexdb_all.pl +++ b/src/bin/scripts/t/091_reindexdb_all.pl @@ -13,10 +13,30 @@ $node->start; $ENV{PGOPTIONS} = '--client-min-messages=WARNING'; +$node->safe_psql('postgres', + 'CREATE TABLE test1 (a int); CREATE INDEX test1x ON test1 (a);'); +$node->safe_psql('template1', + 'CREATE TABLE test1 (a int); CREATE INDEX test1x ON test1 (a);'); $node->issues_sql_like( [ 'reindexdb', '-a' ], qr/statement: REINDEX.*statement: REINDEX/s, 'reindex all databases'); +$node->issues_sql_like( + [ 'reindexdb', '-a', '-s' ], + qr/statement: REINDEX SYSTEM postgres/s, + 'reindex system catalogs in all databases'); +$node->issues_sql_like( + [ 'reindexdb', '-a', '-S', 'public' ], + qr/statement: REINDEX SCHEMA public/s, + 'reindex schema in all databases'); +$node->issues_sql_like( + [ 'reindexdb', '-a', '-i', 'test1x' ], + qr/statement: REINDEX INDEX public\.test1x/s, + 'reindex index in all databases'); +$node->issues_sql_like( + [ 'reindexdb', '-a', '-t', 'test1' ], + qr/statement: REINDEX TABLE public\.test1/s, + 'reindex table in all databases'); $node->safe_psql( 'postgres', q( -- 2.25.1