pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Nathan Bossart <nathandbossart@gmail.com>
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: vacuumdb/clusterdb/reindexdb: allow specifying objects to process in all databases
Date: Mon, 23 Oct 2023 15:25:42 -0500
Message-ID: <20231023202542.GA815077@nathanxps13> (raw )
In-Reply-To: <20230630051332.GB2640352@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 >
rebased
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Attachments: download all (3 files, .tar.gz)
v2-0001-vacuumdb-allow-specifying-tables-or-schemas-to-pr.patch (text/x-diff, 4.1K) download
From 9618c243cbd3056006acda0136036b432af37830 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 28 Jun 2023 15:12:18 -0700
Subject: [PATCH v2 1/3] vacuumdb: allow specifying tables or schemas to
process in all databases
---
src/bin/scripts/t/100_vacuumdb.pl | 24 ++++++++++++------------
src/bin/scripts/vacuumdb.c | 19 +++++--------------
2 files changed, 17 insertions(+), 26 deletions(-)
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 925079bbed..52926d53a5 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -188,18 +188,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 d682573dc1..0eddcaa047 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
v2-0002-clusterdb-allow-specifying-tables-to-process-in-a.patch (text/x-diff, 3.3K) download
From de22c8c0060512d1a9add03b6eb4265767fb061b Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 28 Jun 2023 15:12:58 -0700
Subject: [PATCH v2 2/3] clusterdb: allow specifying tables to process in all
databases
---
src/bin/scripts/clusterdb.c | 28 +++++++++++++++++---------
src/bin/scripts/t/011_clusterdb_all.pl | 11 ++++++++++
2 files changed, 30 insertions(+), 9 deletions(-)
diff --git a/src/bin/scripts/clusterdb.c b/src/bin/scripts/clusterdb.c
index 65428031c7..89f1e733fe 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 35f0b18f50..e1c06203a1 100644
--- a/src/bin/scripts/t/011_clusterdb_all.pl
+++ b/src/bin/scripts/t/011_clusterdb_all.pl
@@ -35,4 +35,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
v2-0003-reindexdb-allow-specifying-objects-to-process-in-.patch (text/x-diff, 9.1K) download
From 53c4cd841bdcd854075e6e532665752ec9eb4628 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
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
view thread (12+ messages) latest in thread
Message-ID: <20231023202542.GA815077@nathanxps13>
Permalink: ../20231023202542.GA815077@nathanxps13/
Also on: postgresql.org/message-id/20231023202542.GA815077@nathanxps13
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, horikyota.ntt@gmail.com
Subject: Re: vacuumdb/clusterdb/reindexdb: allow specifying objects to process in all databases
In-Reply-To: <20231023202542.GA815077@nathanxps13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox