pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Nathan Bossart <nathandbossart@gmail.com>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: 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: Fri, 8 Mar 2024 16:03:22 -0600
Message-ID: <20240308220322.GA1060542@nathanxps13> (raw )
In-Reply-To: <CAEZATCXgbfoFwO7Z7-Aa_4CtE7hMuJU2p9Mvd+0f0qFQ3-GyEg@mail.gmail.com >
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 >
<CAEZATCX2FoAq_c1=z=cCy0xi32ZY4ZG887KL4_abSno_yDXUhw@mail.gmail.com >
<20240306222251.GA386879@nathanxps13 >
<CAEZATCXgbfoFwO7Z7-Aa_4CtE7hMuJU2p9Mvd+0f0qFQ3-GyEg@mail.gmail.com >
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
Attachments: download all (3 files, .tar.gz)
v4-0001-vacuumdb-Allow-specifying-objects-to-process-in-a.patch (text/x-diff, 7.3K) download
From 460da2161265b042079727c1178eff92b3d537b6 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
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
</arg>
</arg>
- <arg choice="opt"><replaceable>dbname</replaceable></arg>
+ <arg choice="opt">
+ <group choice="plain">
+ <arg choice="plain"><replaceable>dbname</replaceable></arg>
+ <arg choice="plain"><option>-a</option></arg>
+ <arg choice="plain"><option>--all</option></arg>
+ </group>
+ </arg>
</cmdsynopsis>
<cmdsynopsis>
@@ -47,40 +53,44 @@ PostgreSQL documentation
<arg choice="plain" rep="repeat">
<arg choice="opt">
<group choice="plain">
- <arg choice="plain">
- <arg choice="opt">
- <group choice="plain">
- <arg choice="plain"><option>-n</option></arg>
- <arg choice="plain"><option>--schema</option></arg>
- </group>
- <replaceable>schema</replaceable>
- </arg>
- </arg>
-
- <arg choice="plain">
- <arg choice="opt">
- <group choice="plain">
- <arg choice="plain"><option>-N</option></arg>
- <arg choice="plain"><option>--exclude-schema</option></arg>
- </group>
- <replaceable>schema</replaceable>
- </arg>
- </arg>
+ <arg choice="plain"><option>-n</option></arg>
+ <arg choice="plain"><option>--schema</option></arg>
</group>
+ <replaceable>schema</replaceable>
</arg>
</arg>
- <arg choice="opt"><replaceable>dbname</replaceable></arg>
+ <arg choice="opt">
+ <group choice="plain">
+ <arg choice="plain"><replaceable>dbname</replaceable></arg>
+ <arg choice="plain"><option>-a</option></arg>
+ <arg choice="plain"><option>--all</option></arg>
+ </group>
+ </arg>
</cmdsynopsis>
<cmdsynopsis>
<command>vacuumdb</command>
<arg rep="repeat"><replaceable>connection-option</replaceable></arg>
<arg rep="repeat"><replaceable>option</replaceable></arg>
- <group choice="plain">
- <arg choice="plain"><option>-a</option></arg>
- <arg choice="plain"><option>--all</option></arg>
- </group>
+
+ <arg choice="plain" rep="repeat">
+ <arg choice="opt">
+ <group choice="plain">
+ <arg choice="plain"><option>-N</option></arg>
+ <arg choice="plain"><option>--exclude-schema</option></arg>
+ </group>
+ <replaceable>schema</replaceable>
+ </arg>
+ </arg>
+
+ <arg choice="opt">
+ <group choice="plain">
+ <arg choice="plain"><replaceable>dbname</replaceable></arg>
+ <arg choice="plain"><option>-a</option></arg>
+ <arg choice="plain"><option>--all</option></arg>
+ </group>
+ </arg>
</cmdsynopsis>
</refsynopsisdiv>
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
v4-0002-clusterdb-Allow-specifying-tables-to-process-in-a.patch (text/x-diff, 5.2K) download
From b6a7c1c0ca1f962c92960c3dc95ebba6d895a336 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
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
<cmdsynopsis>
<command>clusterdb</command>
<arg rep="repeat"><replaceable>connection-option</replaceable></arg>
- <group choice="opt"><arg choice="plain"><option>--verbose</option></arg><arg choice="plain"><option>-v</option></arg></group>
+ <arg rep="repeat"><replaceable>option</replaceable></arg>
<arg choice="plain" rep="repeat">
<arg choice="opt">
@@ -35,14 +35,13 @@ PostgreSQL documentation
</arg>
</arg>
- <arg choice="opt"><replaceable>dbname</replaceable></arg>
- </cmdsynopsis>
-
- <cmdsynopsis>
- <command>clusterdb</command>
- <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
- <group choice="opt"><arg choice="plain"><option>--verbose</option></arg><arg choice="plain"><option>-v</option></arg></group>
- <group choice="plain"><arg choice="plain"><option>--all</option></arg><arg choice="plain"><option>-a</option></arg></group>
+ <arg choice="opt">
+ <group choice="plain">
+ <arg choice="plain"><replaceable>dbname</replaceable></arg>
+ <arg choice="plain"><option>-a</option></arg>
+ <arg choice="plain"><option>--all</option></arg>
+ </group>
+ </arg>
</cmdsynopsis>
</refsynopsisdiv>
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
v4-0003-reindexdb-Allow-specifying-objects-to-process-in-.patch (text/x-diff, 10.6K) download
From a876db53ad81504f6c7436eb019b3f4c43b9a093 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
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
</arg>
</arg>
- <arg choice="opt"><replaceable>dbname</replaceable></arg>
- </cmdsynopsis>
-
- <cmdsynopsis>
- <command>reindexdb</command>
- <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
- <arg rep="repeat"><replaceable>option</replaceable></arg>
-
- <group choice="plain">
- <arg choice="plain"><option>-a</option></arg>
- <arg choice="plain"><option>--all</option></arg>
- </group>
- </cmdsynopsis>
-
- <cmdsynopsis>
- <command>reindexdb</command>
- <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
- <arg rep="repeat"><replaceable>option</replaceable></arg>
+ <arg choice="plain">
+ <arg choice="opt">
+ <group choice="plain">
+ <arg choice="plain"><option>-s</option></arg>
+ <arg choice="plain"><option>--system</option></arg>
+ </group>
+ </arg>
+ </arg>
- <group choice="plain">
- <arg choice="plain"><option>-s</option></arg>
- <arg choice="plain"><option>--system</option></arg>
- </group>
- <arg choice="opt"><replaceable>dbname</replaceable></arg>
+ <arg choice="opt">
+ <group choice="plain">
+ <arg choice="plain"><replaceable>dbname</replaceable></arg>
+ <arg choice="plain"><option>-a</option></arg>
+ <arg choice="plain"><option>--all</option></arg>
+ </group>
+ </arg>
</cmdsynopsis>
</refsynopsisdiv>
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
view thread (12+ messages) latest in thread
Message-ID: <20240308220322.GA1060542@nathanxps13>
Permalink: ../20240308220322.GA1060542@nathanxps13/
Also on: postgresql.org/message-id/20240308220322.GA1060542@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, dean.a.rasheed@gmail.com, horikyota.ntt@gmail.com
Subject: Re: vacuumdb/clusterdb/reindexdb: allow specifying objects to process in all databases
In-Reply-To: <20240308220322.GA1060542@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