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 1rhzf9-00EVKd-Ph for pgsql-hackers@arkaria.postgresql.org; Wed, 06 Mar 2024 22:23:00 +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 1rhzf7-00F2sR-VK for pgsql-hackers@arkaria.postgresql.org; Wed, 06 Mar 2024 22:22:58 +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 1rhzf7-00F2l8-B0 for pgsql-hackers@lists.postgresql.org; Wed, 06 Mar 2024 22:22:58 +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 1rhzf3-003Cx4-1Y for pgsql-hackers@postgresql.org; Wed, 06 Mar 2024 22:22:56 +0000 Received: by mail-il1-x12c.google.com with SMTP id e9e14a558f8ab-36517375cc6so338185ab.1 for ; Wed, 06 Mar 2024 14:22:54 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1709763773; x=1710368573; 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=iPzNDQDGiQ+FtlEHF1KrtHXoEafUl9NIoRtKCF2rHvw=; b=SiwK6belIghBQkeMxpZhJZhR+8eGV51CbTumARoAxcKVmqPvKN7Lf2krJBU4Y6dcdp wY954buVwuQ3K1KWIwcCrlh3gqwH3fkLRSVZq6f4M2QYeOk2i6bxYkEdrHV/ovd5okII qvk/g7/ddZ7t+ITuJKZXme9Kn0F/a1+2luI+IVMIC/xULKY4K87253G3GdPvHfmLX7e6 WIj9brbZ7LCqAF+FsjCvQuYWRmBoYFbkDfGLS0y72fdOMWMMvY9O1JGv/NAAT2zBLmtC njcttuiauku8gwQ1zZR/l/aZoRCnGZVfj710TtiSqG7jthGpi6sJeC63o/rzezoElck6 xo6Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1709763773; x=1710368573; 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=iPzNDQDGiQ+FtlEHF1KrtHXoEafUl9NIoRtKCF2rHvw=; b=LSTxNGnQsOqmiRjHhnvj0PNqiPnL70F9zoLtS4zpJyNDW/vsoeSNsJayI881cbHACT p4PZ++SW82bBiyKGfQmxRBixBrgjcn/UVfevRNtly5HWxJ0pfUtXpmA9ARgvpKuXPBMa stftRv1pwnlRCQOTmpL+lcwuexdrxyK3Rr8kbTb7YQEsyjrSuqIO6SFxGN7KDyZiDNoQ Q2huKo8sGwmvAK6fCWE+C4moW4QKVNitGvNwWBtO9DBtKtMpb9maERWSfWow+ZHE5wYb I6Gaq0UQAD8j6Jw7pNffBjco+VMMoY2R5PBCYEzmc0cNSvZm+edmwprlz1mBuGQZJh9G jWgg== X-Forwarded-Encrypted: i=1; AJvYcCUXLlUeN0myuBrBzYmsKAW0Za+p98L7EZ3TjF8/dQuGi0W5LXasOY0UcHRar1AV1hlRO31l2r2a7l0OTqT3VWlJPbjMpwHtMjnf6uhx X-Gm-Message-State: AOJu0Yye+T0jDqoO6eQQqyyLkgqZxAjj8l9TTnhWMmOnthE0E5geBilz koO/rUkntrkIZAtliETuxK9UEFD73q91ZAYktyPF95yq1andaba1 X-Google-Smtp-Source: AGHT+IEMpWhbFI+Qb1B9egwzrGK7EikHPxG7+VuZlx77DbihWq2H7jlGx160+ixGJxwILx3Z0CGhbw== X-Received: by 2002:a05:6e02:1aaf:b0:365:117c:c957 with SMTP id l15-20020a056e021aaf00b00365117cc957mr20231960ilv.16.1709763773404; Wed, 06 Mar 2024 14:22:53 -0800 (PST) Received: from nathanxps13 (162-195-168-172.lightspeed.stlsmo.sbcglobal.net. [162.195.168.172]) by smtp.gmail.com with ESMTPSA id z13-20020a92d6cd000000b00365be3f841dsm3815432ilp.22.2024.03.06.14.22.52 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 06 Mar 2024 14:22:52 -0800 (PST) Date: Wed, 6 Mar 2024 16:22:51 -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: <20240306222251.GA386879@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> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="IS0zKkzwUGydFO0o" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --IS0zKkzwUGydFO0o Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Tue, Mar 05, 2024 at 11:20:13PM +0000, Dean Rasheed wrote: > I'm not sure how useful these changes are, but I don't really object. > You need to update the synopsis section of the docs though. Thanks for taking a look. I updated the synopsis sections in v3. I also spent some more time on the reindexdb patch (0003). I previously had decided to restrict combinations of tables, schemas, and indexes because I felt it was "ambiguous and inconsistent with vacuumdb," but looking closer, I think that's the wrong move. reindexdb already supports such combinations, which it interprets to mean it should reindex each listed object. So, I removed that change in v3. Even though reindexdb allows combinations of tables, schema, and indexes, it doesn't allow combinations of "system catalogs" and other objects, and it's not clear why. In v3, I've removed this restriction, which ended up simplifying the 0003 patch a bit. Like combinations of tables, schemas, and indexes, reindexdb will now interpret combinations that include --system to mean it should reindex each listed object as well as the system catalogs. Ideally, we'd allow similar combinations in vacuumdb, but I believe that would require a much more invasive patch, and I've already spent far more time on this change than I wanted to. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com --IS0zKkzwUGydFO0o Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0001-vacuumdb-allow-specifying-tables-or-schemas-to-pr.patch" From 84b3f5a8275d53707b15208d761567372b7b20a5 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Wed, 28 Jun 2023 15:12:18 -0700 Subject: [PATCH v3 1/3] vacuumdb: allow specifying tables or schemas to process in all databases --- 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 --IS0zKkzwUGydFO0o Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0002-clusterdb-allow-specifying-tables-to-process-in-a.patch" From 85df37577982b8106aa29bbc80644931b694a195 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Wed, 28 Jun 2023 15:12:58 -0700 Subject: [PATCH v3 2/3] clusterdb: allow specifying tables to process in all databases --- doc/src/sgml/ref/clusterdb.sgml | 15 +++++++------- src/bin/scripts/clusterdb.c | 28 +++++++++++++++++--------- src/bin/scripts/t/011_clusterdb_all.pl | 11 ++++++++++ 3 files changed, 37 insertions(+), 17 deletions(-) diff --git a/doc/src/sgml/ref/clusterdb.sgml b/doc/src/sgml/ref/clusterdb.sgml index c838b22c44..1dd608bd2a 100644 --- a/doc/src/sgml/ref/clusterdb.sgml +++ b/doc/src/sgml/ref/clusterdb.sgml @@ -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 --IS0zKkzwUGydFO0o Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0003-reindexdb-allow-specifying-objects-to-process-in-.patch" From a212b573c80905415e1bb9721c5bf4e85b66da0c Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Wed, 28 Jun 2023 15:36:28 -0700 Subject: [PATCH v3 3/3] reindexdb: allow specifying objects to process in all databases --- 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 --IS0zKkzwUGydFO0o--