Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1riiJT-001Cyg-Dg for pgsql-hackers@arkaria.postgresql.org; Fri, 08 Mar 2024 22:03:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1riiJR-00GSV7-M1 for pgsql-hackers@arkaria.postgresql.org; Fri, 08 Mar 2024 22:03:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1riiJR-00GSUz-4f for pgsql-hackers@lists.postgresql.org; Fri, 08 Mar 2024 22:03:33 +0000 Received: from mail-il1-x12c.google.com ([2607:f8b0:4864:20::12c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1riiJI-003Xrf-Co for pgsql-hackers@postgresql.org; Fri, 08 Mar 2024 22:03:32 +0000 Received: by mail-il1-x12c.google.com with SMTP id e9e14a558f8ab-3662e03ac1fso5086395ab.1 for ; Fri, 08 Mar 2024 14:03:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1709935405; x=1710540205; darn=postgresql.org; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:from:to:cc:subject:date:message-id:reply-to; bh=jM4iz4JuA+5Gy78wuYZUB3t0BVGeH5Cl2KUuqX9lxQw=; b=Mywhj+A5IFFRwu7bcv4p6V0PD+enb049dcho5Xczp2aBAGiufYTsIKwXxp7be9Hycf 13rZHW/6Huazqc+uE2Ka2UIks/MmJwzWhZk4PZLAjsfn3PGfL5OepxnV6q0J1dIHQBBq Qm3Kp3s0jbJgam2QlxHgDm0R/25yD7HYkzH4t9DY89OBLtpDaiYe1N0VwizRtd8LAQWV cCbanvW3N186l9RWC/v8Uy4lQl3rM8wQrK0jBheoO2J21K33uZttl27ldzx/8sK/+mbC Rdbuc7pPM27E5bgHlNr9aOXBjp9mdzWJM2jtMw6MFX3/RYujvCzZzp5lF/gGpA75jxGa hNLQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1709935405; x=1710540205; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=jM4iz4JuA+5Gy78wuYZUB3t0BVGeH5Cl2KUuqX9lxQw=; b=WJKXL0DthNEnW3Pm+RHzaszSq39LzSTQ5n5KMYptPb/7kej0j6QvHzRotSx4twQtIz F8G8agVmuuvZEhN6AkQjIyU2E87wGeKSIl49ePLru0hen+L0yPcxQddKZ1A4Ta4/NsGc T1Au7iTIw0XcwJcQwBBIFADJjm5vjISHVoTeaCaPNYxnyElMblQVQdNUGXfnVyL1BMHk 0E5MqyKTXgRVoGFt+UHASZn91hirvjzuVFkPqv8T0Rk434GIpZBkxl7WwpmcPpr0s4CY NS84zHMRdyrOAtSDxHiXP855iCDHOETNZDRKqWceFO4qThG4KLNzReW48wzIi3GnLQml pHTQ== X-Forwarded-Encrypted: i=1; AJvYcCUhdkEdDcR4RCPL5ujyUvVvlRc9fcagUrvxGmfRtAuqBZI+5g7tXtmHgY8zbvqhaScf96WnbhvNWizvVWltJmEpjA6gyGsblA9p3/Dg X-Gm-Message-State: AOJu0YzLQvOCqXTW7JwmmFbpLwePCw8x2nR/je+KfAfCA5CjElf0Ky5o PUySFk0TKe/6qMNEGOlryqSzpN3tNBgVX150bj+1/F+qVV9jlP/m X-Google-Smtp-Source: AGHT+IGYiDTm2ShwZm8/D9WFHiYF0no5T3Jvpw04za9c2AcCOnEIuQwZaVv5Eu8fMDEUDq7Yc83kjQ== X-Received: by 2002:a05:6e02:180d:b0:365:105d:e0b5 with SMTP id a13-20020a056e02180d00b00365105de0b5mr266229ilv.8.1709935404502; Fri, 08 Mar 2024 14:03:24 -0800 (PST) Received: from nathanxps13 (162-195-168-172.lightspeed.stlsmo.sbcglobal.net. [162.195.168.172]) by smtp.gmail.com with ESMTPSA id f19-20020a056e0212b300b00366048767c2sm53049ilr.72.2024.03.08.14.03.23 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 08 Mar 2024 14:03:24 -0800 (PST) Date: Fri, 8 Mar 2024 16:03:22 -0600 From: Nathan Bossart To: Dean Rasheed Cc: Kyotaro Horiguchi , pgsql-hackers@postgresql.org Subject: Re: vacuumdb/clusterdb/reindexdb: allow specifying objects to process in all databases Message-ID: <20240308220322.GA1060542@nathanxps13> References: <20230628232402.GA1954626@nathanxps13> <20230629.141626.886053266048932185.horikyota.ntt@gmail.com> <20230629205638.GA2054380@nathanxps13> <20230630.120517.1837918148802895337.horikyota.ntt@gmail.com> <20230630051332.GB2640352@nathanxps13> <20231023202542.GA815077@nathanxps13> <20240305022225.GA3375165@nathanxps13> <20240306222251.GA386879@nathanxps13> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="UugvWAfsgieZRqgk" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --UugvWAfsgieZRqgk Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Fri, Mar 08, 2024 at 09:33:19AM +0000, Dean Rasheed wrote: > If I'm nitpicking, "[--verbose | -v]" in the clusterdb synopsis should > be replaced with "[option...]", like the other commands, because there > are other general-purpose options like --quiet and --echo. Good catch. I fixed that in v4. We could probably back-patch this particular change, but since it's been this way for a while, I don't think it's terribly important to do so. > I think this is good to go. Thanks. In v4, I've added a first draft of the commit messages, and I am planning to commit this early next week. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com --UugvWAfsgieZRqgk Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v4-0001-vacuumdb-Allow-specifying-objects-to-process-in-a.patch" From 460da2161265b042079727c1178eff92b3d537b6 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Fri, 8 Mar 2024 14:35:07 -0600 Subject: [PATCH v4 1/3] vacuumdb: Allow specifying objects to process in all databases. Presently, vacuumdb's --table, --schema, and --exclude-schema options cannot be used together with --all, i.e., you cannot specify tables or schemas to process in all databases. This commit removes this unnecessary restriction, thus enabling potentially useful command like "vacuumdb --all --schema pg_catalog". Reviewed-by: Kyotaro Horiguchi, Dean Rasheed Discussion: https://postgr.es/m/20230628232402.GA1954626%40nathanxps13 --- doc/src/sgml/ref/vacuumdb.sgml | 60 ++++++++++++++++++------------- src/bin/scripts/t/100_vacuumdb.pl | 24 ++++++------- src/bin/scripts/vacuumdb.c | 19 +++------- 3 files changed, 52 insertions(+), 51 deletions(-) diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml index 09356ea4fa..66fccb30a2 100644 --- a/doc/src/sgml/ref/vacuumdb.sgml +++ b/doc/src/sgml/ref/vacuumdb.sgml @@ -36,7 +36,13 @@ PostgreSQL documentation - dbname + + + dbname + + + + @@ -47,40 +53,44 @@ PostgreSQL documentation - - - - - - - schema - - - - - - - - - - schema - - + + + schema - dbname + + + dbname + + + + vacuumdb connection-option option - - - - + + + + + + + + schema + + + + + + dbname + + + + diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl index 0601fde205..1d8558c780 100644 --- a/src/bin/scripts/t/100_vacuumdb.pl +++ b/src/bin/scripts/t/100_vacuumdb.pl @@ -184,18 +184,18 @@ $node->command_fails_like( [ 'vacuumdb', '-n', 'pg_catalog', '-N', '"Foo"', 'postgres' ], qr/cannot vacuum all tables in schema\(s\) and exclude schema\(s\) at the same time/, 'cannot use options -n and -N at the same time'); -$node->command_fails_like( - [ 'vacuumdb', '-a', '-N', '"Foo"' ], - qr/cannot exclude specific schema\(s\) in all databases/, - 'cannot use options -a and -N at the same time'); -$node->command_fails_like( - [ 'vacuumdb', '-a', '-n', '"Foo"' ], - qr/cannot vacuum specific schema\(s\) in all databases/, - 'cannot use options -a and -n at the same time'); -$node->command_fails_like( - [ 'vacuumdb', '-a', '-t', '"Foo".bar' ], - qr/cannot vacuum specific table\(s\) in all databases/, - 'cannot use options -a and -t at the same time'); +$node->issues_sql_like( + [ 'vacuumdb', '-a', '-N', 'pg_catalog' ], + qr/(?:(?!VACUUM \(SKIP_DATABASE_STATS\) pg_catalog.pg_class).)*/, + 'vacuumdb -a -N'); +$node->issues_sql_like( + [ 'vacuumdb', '-a', '-n', 'pg_catalog' ], + qr/VACUUM \(SKIP_DATABASE_STATS\) pg_catalog.pg_class/, + 'vacuumdb -a -n'); +$node->issues_sql_like( + [ 'vacuumdb', '-a', '-t', 'pg_class' ], + qr/VACUUM \(SKIP_DATABASE_STATS\) pg_catalog.pg_class/, + 'vacuumdb -a -t'); $node->command_fails_like( [ 'vacuumdb', '-a', '-d', 'postgres' ], qr/cannot vacuum all databases and a specific one at the same time/, diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index 291766793e..7138c6e97e 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -72,6 +72,7 @@ static void vacuum_one_database(ConnParams *cparams, static void vacuum_all_databases(ConnParams *cparams, vacuumingOptions *vacopts, bool analyze_in_stages, + SimpleStringList *objects, int concurrentCons, const char *progname, bool echo, bool quiet); @@ -378,6 +379,7 @@ main(int argc, char *argv[]) vacuum_all_databases(&cparams, &vacopts, analyze_in_stages, + &objects, concurrentCons, progname, echo, quiet); } @@ -429,18 +431,6 @@ check_objfilter(void) (objfilter & OBJFILTER_DATABASE)) pg_fatal("cannot vacuum all databases and a specific one at the same time"); - if ((objfilter & OBJFILTER_ALL_DBS) && - (objfilter & OBJFILTER_TABLE)) - pg_fatal("cannot vacuum specific table(s) in all databases"); - - if ((objfilter & OBJFILTER_ALL_DBS) && - (objfilter & OBJFILTER_SCHEMA)) - pg_fatal("cannot vacuum specific schema(s) in all databases"); - - if ((objfilter & OBJFILTER_ALL_DBS) && - (objfilter & OBJFILTER_SCHEMA_EXCLUDE)) - pg_fatal("cannot exclude specific schema(s) in all databases"); - if ((objfilter & OBJFILTER_TABLE) && (objfilter & OBJFILTER_SCHEMA)) pg_fatal("cannot vacuum all tables in schema(s) and specific table(s) at the same time"); @@ -895,6 +885,7 @@ static void vacuum_all_databases(ConnParams *cparams, vacuumingOptions *vacopts, bool analyze_in_stages, + SimpleStringList *objects, int concurrentCons, const char *progname, bool echo, bool quiet) { @@ -927,7 +918,7 @@ vacuum_all_databases(ConnParams *cparams, vacuum_one_database(cparams, vacopts, stage, - NULL, + objects, concurrentCons, progname, echo, quiet); } @@ -941,7 +932,7 @@ vacuum_all_databases(ConnParams *cparams, vacuum_one_database(cparams, vacopts, ANALYZE_NO_STAGE, - NULL, + objects, concurrentCons, progname, echo, quiet); } -- 2.25.1 --UugvWAfsgieZRqgk Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v4-0002-clusterdb-Allow-specifying-tables-to-process-in-a.patch" From b6a7c1c0ca1f962c92960c3dc95ebba6d895a336 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Fri, 8 Mar 2024 15:18:29 -0600 Subject: [PATCH v4 2/3] clusterdb: Allow specifying tables to process in all databases. Presently, clusterdb's --table option cannot be used together with --all, i.e., you cannot specify tables to process in all databases. This commit removes this unnecessary restriction. In passing, change the synopsis in the documentation to use "[option...]" instead of "[--verbose | -v]". There are other general-purpose options (e.g., --quiet and --echo), but the synopsis currently only lists --verbose. Reviewed-by: Kyotaro Horiguchi, Dean Rasheed Discussion: https://postgr.es/m/20230628232402.GA1954626%40nathanxps13 --- doc/src/sgml/ref/clusterdb.sgml | 17 ++++++++-------- src/bin/scripts/clusterdb.c | 28 +++++++++++++++++--------- src/bin/scripts/t/011_clusterdb_all.pl | 11 ++++++++++ 3 files changed, 38 insertions(+), 18 deletions(-) diff --git a/doc/src/sgml/ref/clusterdb.sgml b/doc/src/sgml/ref/clusterdb.sgml index c838b22c44..d3145318b3 100644 --- a/doc/src/sgml/ref/clusterdb.sgml +++ b/doc/src/sgml/ref/clusterdb.sgml @@ -23,7 +23,7 @@ PostgreSQL documentation clusterdb connection-option - + option @@ -35,14 +35,13 @@ PostgreSQL documentation - dbname - - - - clusterdb - connection-option - - + + + dbname + + + + diff --git a/src/bin/scripts/clusterdb.c b/src/bin/scripts/clusterdb.c index 1f3aec1b5e..3503a3bb58 100644 --- a/src/bin/scripts/clusterdb.c +++ b/src/bin/scripts/clusterdb.c @@ -21,8 +21,9 @@ static void cluster_one_database(const ConnParams *cparams, const char *table, const char *progname, bool verbose, bool echo); -static void cluster_all_databases(ConnParams *cparams, const char *progname, - bool verbose, bool echo, bool quiet); +static void cluster_all_databases(ConnParams *cparams, SimpleStringList *tables, + const char *progname, bool verbose, bool echo, + bool quiet); static void help(const char *progname); @@ -147,12 +148,10 @@ main(int argc, char *argv[]) if (dbname) pg_fatal("cannot cluster all databases and a specific one at the same time"); - if (tables.head != NULL) - pg_fatal("cannot cluster specific table(s) in all databases"); - cparams.dbname = maintenance_db; - cluster_all_databases(&cparams, progname, verbose, echo, quiet); + cluster_all_databases(&cparams, &tables, + progname, verbose, echo, quiet); } else { @@ -226,8 +225,9 @@ cluster_one_database(const ConnParams *cparams, const char *table, static void -cluster_all_databases(ConnParams *cparams, const char *progname, - bool verbose, bool echo, bool quiet) +cluster_all_databases(ConnParams *cparams, SimpleStringList *tables, + const char *progname, bool verbose, bool echo, + bool quiet) { PGconn *conn; PGresult *result; @@ -251,7 +251,17 @@ cluster_all_databases(ConnParams *cparams, const char *progname, cparams->override_dbname = dbname; - cluster_one_database(cparams, NULL, progname, verbose, echo); + if (tables->head != NULL) + { + SimpleStringListCell *cell; + + for (cell = tables->head; cell; cell = cell->next) + cluster_one_database(cparams, cell->val, + progname, verbose, echo); + } + else + cluster_one_database(cparams, NULL, + progname, verbose, echo); } PQclear(result); diff --git a/src/bin/scripts/t/011_clusterdb_all.pl b/src/bin/scripts/t/011_clusterdb_all.pl index 04078a5b7e..3415e1d4f4 100644 --- a/src/bin/scripts/t/011_clusterdb_all.pl +++ b/src/bin/scripts/t/011_clusterdb_all.pl @@ -33,4 +33,15 @@ $node->command_fails_like([ 'clusterdb', '-d', 'regression_invalid'], qr/FATAL: cannot connect to invalid database "regression_invalid"/, 'clusterdb cannot target invalid database'); +$node->safe_psql('postgres', + 'CREATE TABLE test1 (a int); CREATE INDEX test1x ON test1 (a); CLUSTER test1 USING test1x' +); +$node->safe_psql('template1', + 'CREATE TABLE test1 (a int); CREATE INDEX test1x ON test1 (a); CLUSTER test1 USING test1x' +); +$node->issues_sql_like( + [ 'clusterdb', '-a', '-t', 'test1' ], + qr/statement: CLUSTER public\.test1/s, + 'cluster specific table in all databases'); + done_testing(); -- 2.25.1 --UugvWAfsgieZRqgk Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v4-0003-reindexdb-Allow-specifying-objects-to-process-in-.patch" From a876db53ad81504f6c7436eb019b3f4c43b9a093 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Fri, 8 Mar 2024 15:31:59 -0600 Subject: [PATCH v4 3/3] reindexdb: Allow specifying objects to process in all databases. Presently, reindexdb's --table, --schema, --index, and --system options cannot be used together with --all, i.e., you cannot specify objects to process in all databases. This commit removes this unnecessary restriction. Furthermore, it removes the restriction that --system cannot be used with --table, --schema, and --index. There is no such restriction for the latter options, and there is no technical reason to disallow these combinations. Reviewed-by: Kyotaro Horiguchi, Dean Rasheed Discussion: https://postgr.es/m/20230628232402.GA1954626%40nathanxps13 --- doc/src/sgml/ref/reindexdb.sgml | 38 ++++----- src/bin/scripts/reindexdb.c | 114 +++++++++++++------------ src/bin/scripts/t/090_reindexdb.pl | 14 +++ src/bin/scripts/t/091_reindexdb_all.pl | 20 +++++ 4 files changed, 110 insertions(+), 76 deletions(-) diff --git a/doc/src/sgml/ref/reindexdb.sgml b/doc/src/sgml/ref/reindexdb.sgml index 8d9ced212f..a877439dc5 100644 --- a/doc/src/sgml/ref/reindexdb.sgml +++ b/doc/src/sgml/ref/reindexdb.sgml @@ -55,30 +55,22 @@ PostgreSQL documentation - dbname - - - - reindexdb - connection-option - option - - - - - - - - - reindexdb - connection-option - option + + + + + + + + - - - - - dbname + + + dbname + + + + diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c index 6ae30dff31..231e5c8fd0 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,62 +206,33 @@ main(int argc, char *argv[]) setup_cancel_handler(NULL); + 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 (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"); - 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 (schemas.head != NULL) - pg_fatal("cannot reindex specific schema(s) and system catalogs at the same time"); - if (tables.head != NULL) - 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; - - reindex_one_database(&cparams, REINDEX_SYSTEM, NULL, - progname, echo, verbose, - concurrently, 1, tablespace); + concurrently, concurrentCons, tablespace, + syscatalog, &schemas, &tables, &indexes); } else { - /* - * 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) - pg_fatal("cannot use multiple jobs to reindex indexes"); - if (dbname == NULL) { if (getenv("PGDATABASE")) @@ -271,6 +245,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 +266,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 +691,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 +717,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 4f1a141132..429dd3acd6 100644 --- a/src/bin/scripts/t/090_reindexdb.pl +++ b/src/bin/scripts/t/090_reindexdb.pl @@ -262,4 +262,18 @@ $node->command_ok( [ 'reindexdb', '-j', '2', '--concurrently', '-d', 'postgres' ], 'parallel reindexdb on database, concurrently'); +# combinations of objects +$node->issues_sql_like( + [ 'reindexdb', '-s', '-t', 'test1', 'postgres' ], + qr/statement:\ REINDEX SYSTEM postgres;/, + 'specify both --system and --table'); +$node->issues_sql_like( + [ 'reindexdb', '-s', '-i', 'test1x', 'postgres' ], + qr/statement:\ REINDEX INDEX public.test1x;/, + 'specify both --system and --index'); +$node->issues_sql_like( + [ 'reindexdb', '-s', '-S', 'pg_catalog', 'postgres' ], + qr/statement:\ REINDEX SCHEMA pg_catalog;/, + 'specify both --system 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 a4540084fe..8061883f7f 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 --UugvWAfsgieZRqgk--