On Thu, Nov 20, 2025 at 04:16:13PM -0600, Nathan Bossart wrote: > On Thu, Nov 20, 2025 at 05:09:54PM -0500, Corey Huinker wrote: >> I have no objections to, but I am curious about the factors that went into >> making dry_run an independent boolean rather than part of vacopts. > > My thinking was that it's closer to "echo" and "quiet" than anything in > vacuumingOptions. Most of that stuff seems geared towards controlling the > precise behavior of the commands rather than the behavior of the > application. TBH I think it'd be fine either way. We could probably even > move "echo" and "quiet" into vacuumingOptions if we really wanted to.
Yeah, I'm finding myself liking the idea of moving all of these things into vacuumingOptions so that we don't have to cart around so many bool arguments. Here's a new patch set that does it this way. The remaining question in my mind is where we should let the user know that we're in dry-run mode. The three options I see are 1) at the beginning of vacuumdb execution, 2) in the !quiet block for each database, and 3) in each command (via a comment). In v5, I've added a message to all three, but I'm eager to hear what folks think. -- nathan
>From 69911834a8046c226ce6c54a6afd2b51830084d0 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Wed, 3 Dec 2025 16:12:37 -0600 Subject: [PATCH v5 1/3] Move some vacuumdb options to vacopts struct. --- src/bin/scripts/vacuumdb.c | 8 +++--- src/bin/scripts/vacuuming.c | 50 ++++++++++++++++++------------------- src/bin/scripts/vacuuming.h | 4 ++- 3 files changed, 31 insertions(+), 31 deletions(-) diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index e117dac2242..f72ad9dd0ba 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -68,8 +68,6 @@ main(int argc, char *argv[]) const char *dbname = NULL; const char *maintenance_db = NULL; ConnParams cparams; - bool echo = false; - bool quiet = false; vacuumingOptions vacopts; SimpleStringList objects = {NULL, NULL}; int concurrentCons = 1; @@ -110,7 +108,7 @@ main(int argc, char *argv[]) dbname = pg_strdup(optarg); break; case 'e': - echo = true; + vacopts.echo = true; break; case 'f': vacopts.full = true; @@ -143,7 +141,7 @@ main(int argc, char *argv[]) exit(1); break; case 'q': - quiet = true; + vacopts.quiet = true; break; case 't': vacopts.objfilter |= OBJFILTER_TABLE; @@ -312,7 +310,7 @@ main(int argc, char *argv[]) ret = vacuuming_main(&cparams, dbname, maintenance_db, &vacopts, &objects, tbl_count, concurrentCons, - progname, echo, quiet); + progname); exit(ret); } diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c index f836f21fb03..1af242b60d7 100644 --- a/src/bin/scripts/vacuuming.c +++ b/src/bin/scripts/vacuuming.c @@ -30,16 +30,15 @@ static int vacuum_one_database(ConnParams *cparams, SimpleStringList *objects, SimpleStringList **found_objs, int concurrentCons, - const char *progname, bool echo, bool quiet); + const char *progname); static int vacuum_all_databases(ConnParams *cparams, vacuumingOptions *vacopts, SimpleStringList *objects, int concurrentCons, - const char *progname, bool echo, bool quiet); + const char *progname); static SimpleStringList *retrieve_objects(PGconn *conn, vacuumingOptions *vacopts, - SimpleStringList *objects, - bool echo); + SimpleStringList *objects); static void free_retrieved_objects(SimpleStringList *list); static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql, vacuumingOptions *vacopts, const char *table); @@ -56,7 +55,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname, const char *maintenance_db, vacuumingOptions *vacopts, SimpleStringList *objects, unsigned int tbl_count, int concurrentCons, - const char *progname, bool echo, bool quiet) + const char *progname) { setup_cancel_handler(NULL); @@ -71,7 +70,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname, return vacuum_all_databases(cparams, vacopts, objects, concurrentCons, - progname, echo, quiet); + progname); } else { @@ -100,7 +99,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname, objects, vacopts->missing_stats_only ? &found_objs : NULL, concurrentCons, - progname, echo, quiet); + progname); if (ret != 0) { free_retrieved_objects(found_objs); @@ -116,7 +115,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname, ANALYZE_NO_STAGE, objects, NULL, concurrentCons, - progname, echo, quiet); + progname); } } @@ -167,7 +166,7 @@ vacuum_one_database(ConnParams *cparams, SimpleStringList *objects, SimpleStringList **found_objs, int concurrentCons, - const char *progname, bool echo, bool quiet) + const char *progname) { PQExpBufferData sql; PGconn *conn; @@ -192,7 +191,7 @@ vacuum_one_database(ConnParams *cparams, Assert(stage == ANALYZE_NO_STAGE || (stage >= 0 && stage < ANALYZE_NUM_STAGES)); - conn = connectDatabase(cparams, progname, echo, false, true); + conn = connectDatabase(cparams, progname, vacopts->echo, false, true); if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600) { @@ -281,7 +280,7 @@ vacuum_one_database(ConnParams *cparams, /* skip_database_stats is used automatically if server supports it */ vacopts->skip_database_stats = (PQserverVersion(conn) >= 160000); - if (!quiet) + if (!vacopts->quiet) { if (vacopts->mode == MODE_ANALYZE_IN_STAGES) printf(_("%s: processing database \"%s\": %s\n"), @@ -302,7 +301,7 @@ vacuum_one_database(ConnParams *cparams, retobjs = *found_objs; else { - retobjs = retrieve_objects(conn, vacopts, objects, echo); + retobjs = retrieve_objects(conn, vacopts, objects); if (found_objs) *found_objs = retobjs; else @@ -341,7 +340,7 @@ vacuum_one_database(ConnParams *cparams, if (vacopts->mode == MODE_ANALYZE_IN_STAGES) { initcmd = stage_commands[stage]; - executeCommand(conn, initcmd, echo); + executeCommand(conn, initcmd, vacopts->echo); } else initcmd = NULL; @@ -351,7 +350,8 @@ vacuum_one_database(ConnParams *cparams, * for the first slot. If not in parallel mode, the first slot in the * array contains the connection. */ - sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd); + sa = ParallelSlotsSetup(concurrentCons, cparams, progname, + vacopts->echo, initcmd); ParallelSlotsAdoptConn(sa, conn); initPQExpBuffer(&sql); @@ -384,7 +384,7 @@ vacuum_one_database(ConnParams *cparams, */ ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); run_vacuum_command(free_slot->connection, sql.data, - echo, tabname); + vacopts->echo, tabname); cell = cell->next; } while (cell != NULL); @@ -408,7 +408,7 @@ vacuum_one_database(ConnParams *cparams, } ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); - run_vacuum_command(free_slot->connection, cmd, echo, NULL); + run_vacuum_command(free_slot->connection, cmd, vacopts->echo, NULL); if (!ParallelSlotsWaitCompletion(sa)) ret = EXIT_FAILURE; /* error already reported by handler */ @@ -436,17 +436,17 @@ vacuum_all_databases(ConnParams *cparams, vacuumingOptions *vacopts, SimpleStringList *objects, int concurrentCons, - const char *progname, bool echo, bool quiet) + const char *progname) { int ret = EXIT_SUCCESS; PGconn *conn; PGresult *result; int numdbs; - conn = connectMaintenanceDatabase(cparams, progname, echo); + conn = connectMaintenanceDatabase(cparams, progname, vacopts->echo); result = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;", - echo); + vacopts->echo); numdbs = PQntuples(result); PQfinish(conn); @@ -474,7 +474,7 @@ vacuum_all_databases(ConnParams *cparams, objects, vacopts->missing_stats_only ? &found_objs[i] : NULL, concurrentCons, - progname, echo, quiet); + progname); if (ret != EXIT_SUCCESS) break; } @@ -499,7 +499,7 @@ vacuum_all_databases(ConnParams *cparams, objects, NULL, concurrentCons, - progname, echo, quiet); + progname); if (ret != EXIT_SUCCESS) break; } @@ -524,7 +524,7 @@ vacuum_all_databases(ConnParams *cparams, */ static SimpleStringList * retrieve_objects(PGconn *conn, vacuumingOptions *vacopts, - SimpleStringList *objects, bool echo) + SimpleStringList *objects) { PQExpBufferData buf; PQExpBufferData catalog_query; @@ -776,10 +776,10 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts, * query for consistency with table lookups done elsewhere by the user. */ appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;"); - executeCommand(conn, "RESET search_path;", echo); - res = executeQuery(conn, catalog_query.data, echo); + executeCommand(conn, "RESET search_path;", vacopts->echo); + res = executeQuery(conn, catalog_query.data, vacopts->echo); termPQExpBuffer(&catalog_query); - PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, echo)); + PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, vacopts->echo)); /* * Build qualified identifiers for each table, including the column list diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h index 49f968b32e5..90db4fa1a64 100644 --- a/src/bin/scripts/vacuuming.h +++ b/src/bin/scripts/vacuuming.h @@ -51,6 +51,8 @@ typedef struct vacuumingOptions bool skip_database_stats; char *buffer_usage_limit; bool missing_stats_only; + bool echo; + bool quiet; } vacuumingOptions; /* Valid values for vacuumingOptions->objfilter */ @@ -65,7 +67,7 @@ extern int vacuuming_main(ConnParams *cparams, const char *dbname, SimpleStringList *objects, unsigned int tbl_count, int concurrentCons, - const char *progname, bool echo, bool quiet); + const char *progname); extern char *escape_quotes(const char *src); -- 2.39.5 (Apple Git-154)
>From 3b35624019529d8aac10dcc81b9e2e0c88cb9748 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Thu, 20 Nov 2025 14:16:02 -0600 Subject: [PATCH v5 2/3] Add ParallelSlotSetIdle(). --- src/fe_utils/parallel_slot.c | 6 ++---- src/include/fe_utils/parallel_slot.h | 7 +++++++ 2 files changed, 9 insertions(+), 4 deletions(-) diff --git a/src/fe_utils/parallel_slot.c b/src/fe_utils/parallel_slot.c index 253a840865e..1b9bc8b0da8 100644 --- a/src/fe_utils/parallel_slot.c +++ b/src/fe_utils/parallel_slot.c @@ -269,8 +269,7 @@ wait_on_slots(ParallelSlotArray *sa) else { /* This connection has become idle */ - sa->slots[i].inUse = false; - ParallelSlotClearHandler(&sa->slots[i]); + ParallelSlotSetIdle(&sa->slots[i]); break; } } @@ -509,8 +508,7 @@ ParallelSlotsWaitCompletion(ParallelSlotArray *sa) if (!consumeQueryResult(&sa->slots[i])) return false; /* Mark connection as idle */ - sa->slots[i].inUse = false; - ParallelSlotClearHandler(&sa->slots[i]); + ParallelSlotSetIdle(&sa->slots[i]); } return true; diff --git a/src/include/fe_utils/parallel_slot.h b/src/include/fe_utils/parallel_slot.h index 7770a20de34..4a5fbfb3d8c 100644 --- a/src/include/fe_utils/parallel_slot.h +++ b/src/include/fe_utils/parallel_slot.h @@ -58,6 +58,13 @@ ParallelSlotClearHandler(ParallelSlot *slot) slot->handler_context = NULL; } +static inline void +ParallelSlotSetIdle(ParallelSlot *slot) +{ + slot->inUse = false; + ParallelSlotClearHandler(slot); +} + extern ParallelSlot *ParallelSlotsGetIdle(ParallelSlotArray *sa, const char *dbname); -- 2.39.5 (Apple Git-154)
>From 1b1860b15f4937f3d7ebbddce14de0bfe302b128 Mon Sep 17 00:00:00 2001 From: Corey Huinker <[email protected]> Date: Mon, 10 Nov 2025 14:33:41 -0500 Subject: [PATCH v5 3/3] Add --dry-run to vacuumdb. This option answers the question "what tables would be affected if I ran a command using these options" without actually initiating those actions. --- doc/src/sgml/ref/vacuumdb.sgml | 10 +++++++ src/bin/scripts/t/100_vacuumdb.pl | 12 +++++++++ src/bin/scripts/vacuumdb.c | 8 ++++++ src/bin/scripts/vacuuming.c | 44 ++++++++++++++++++++----------- src/bin/scripts/vacuuming.h | 1 + 5 files changed, 59 insertions(+), 16 deletions(-) diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml index 84c76d7350c..89aa7fac4f8 100644 --- a/doc/src/sgml/ref/vacuumdb.sgml +++ b/doc/src/sgml/ref/vacuumdb.sgml @@ -171,6 +171,16 @@ PostgreSQL documentation </listitem> </varlistentry> + <varlistentry> + <term><option>--dry-run</option></term> + <listitem> + <para> + Print, but do not execute, the vacuum and analyze commands that would + have been sent to the server (performs a dry run). + </para> + </listitem> + </varlistentry> + <varlistentry> <term><option>-e</option></term> <term><option>--echo</option></term> diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl index a16fad593f7..fb2fecdd3c6 100644 --- a/src/bin/scripts/t/100_vacuumdb.pl +++ b/src/bin/scripts/t/100_vacuumdb.pl @@ -169,6 +169,10 @@ $node->issues_sql_like( [ 'vacuumdb', '--schema' => '"Foo"', 'postgres' ], qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/, 'vacuumdb --schema'); +$node->issues_sql_unlike( + [ 'vacuumdb', '--schema' => '"Foo"', 'postgres', '--dry-run' ], + qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/, + 'vacuumdb --dry-run'); $node->issues_sql_like( [ 'vacuumdb', '--schema' => '"Foo"', '--schema' => '"Bar"', 'postgres' ], qr/VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar @@ -241,6 +245,14 @@ $node->safe_psql('postgres', q| CREATE TABLE regression_vacuumdb_test AS select generate_series(1, 10) a, generate_series(2, 11) b; ALTER TABLE regression_vacuumdb_test ADD COLUMN c INT GENERATED ALWAYS AS (a + b); |); +$node->issues_sql_unlike( + [ + 'vacuumdb', '--analyze-only', '--dry-run', + '--missing-stats-only', '-t', + 'regression_vacuumdb_test', 'postgres' + ], + qr/statement:\ ANALYZE/sx, + '--missing-stats-only --dry-run'); $node->issues_sql_like( [ 'vacuumdb', '--analyze-only', diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index f72ad9dd0ba..c1d8891736c 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -59,6 +59,7 @@ main(int argc, char *argv[]) {"no-process-main", no_argument, NULL, 12}, {"buffer-usage-limit", required_argument, NULL, 13}, {"missing-stats-only", no_argument, NULL, 14}, + {"dry-run", no_argument, NULL, 15}, {NULL, 0, NULL, 0} }; @@ -211,6 +212,9 @@ main(int argc, char *argv[]) case 14: vacopts.missing_stats_only = true; break; + case 15: + vacopts.dry_run = true; + break; default: /* getopt_long already emitted a complaint */ pg_log_error_hint("Try \"%s --help\" for more information.", progname); @@ -307,6 +311,9 @@ main(int argc, char *argv[]) pg_fatal("cannot use the \"%s\" option without \"%s\" or \"%s\"", "missing-stats-only", "analyze-only", "analyze-in-stages"); + if (vacopts.dry_run) + pg_log_info("Executing in dry-run mode."); + ret = vacuuming_main(&cparams, dbname, maintenance_db, &vacopts, &objects, tbl_count, concurrentCons, @@ -349,6 +356,7 @@ help(const char *progname) printf(_(" --buffer-usage-limit=SIZE size of ring buffer used for vacuum\n")); printf(_(" -d, --dbname=DBNAME database to vacuum\n")); printf(_(" --disable-page-skipping disable all page-skipping behavior\n")); + printf(_(" --dry-run dry run, show commands that would be sent to the server\n")); printf(_(" -e, --echo show the commands being sent to the server\n")); printf(_(" -f, --full do full vacuuming\n")); printf(_(" -F, --freeze freeze row transaction information\n")); diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c index 1af242b60d7..32df69bde82 100644 --- a/src/bin/scripts/vacuuming.c +++ b/src/bin/scripts/vacuuming.c @@ -42,8 +42,8 @@ static SimpleStringList *retrieve_objects(PGconn *conn, static void free_retrieved_objects(SimpleStringList *list); static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql, vacuumingOptions *vacopts, const char *table); -static void run_vacuum_command(PGconn *conn, const char *sql, bool echo, - const char *table); +static void run_vacuum_command(ParallelSlot *free_slot, const char *sql, + bool echo, bool dry_run, const char *table); /* * Executes vacuum/analyze as indicated. Returns 0 if the plan is carried @@ -283,11 +283,14 @@ vacuum_one_database(ConnParams *cparams, if (!vacopts->quiet) { if (vacopts->mode == MODE_ANALYZE_IN_STAGES) - printf(_("%s: processing database \"%s\": %s\n"), - progname, PQdb(conn), _(stage_messages[stage])); + printf(_("%s: processing database \"%s\"%s: %s\n"), + progname, PQdb(conn), + vacopts->dry_run ? " (dry-run)" : "", + _(stage_messages[stage])); else - printf(_("%s: vacuuming database \"%s\"\n"), - progname, PQdb(conn)); + printf(_("%s: vacuuming database \"%s\"%s\n"), + progname, PQdb(conn), + vacopts->dry_run ? " (dry-run)" : ""); fflush(stdout); } @@ -340,7 +343,11 @@ vacuum_one_database(ConnParams *cparams, if (vacopts->mode == MODE_ANALYZE_IN_STAGES) { initcmd = stage_commands[stage]; - executeCommand(conn, initcmd, vacopts->echo); + + if (vacopts->dry_run) + printf("%s -- not executed\n", initcmd); + else + executeCommand(conn, initcmd, vacopts->echo); } else initcmd = NULL; @@ -383,8 +390,8 @@ vacuum_one_database(ConnParams *cparams, * through ParallelSlotsGetIdle. */ ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); - run_vacuum_command(free_slot->connection, sql.data, - vacopts->echo, tabname); + run_vacuum_command(free_slot, sql.data, vacopts->echo, + vacopts->dry_run, tabname); cell = cell->next; } while (cell != NULL); @@ -408,7 +415,8 @@ vacuum_one_database(ConnParams *cparams, } ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); - run_vacuum_command(free_slot->connection, cmd, vacopts->echo, NULL); + run_vacuum_command(free_slot, cmd, vacopts->echo, + vacopts->dry_run, NULL); if (!ParallelSlotsWaitCompletion(sa)) ret = EXIT_FAILURE; /* error already reported by handler */ @@ -1001,15 +1009,19 @@ prepare_vacuum_command(PGconn *conn, PQExpBuffer sql, * Any errors during command execution are reported to stderr. */ static void -run_vacuum_command(PGconn *conn, const char *sql, bool echo, - const char *table) +run_vacuum_command(ParallelSlot *free_slot, const char *sql, + bool echo, bool dry_run, const char *table) { - bool status; + bool status = true; + PGconn *conn = free_slot->connection; - if (echo) - printf("%s\n", sql); + if (echo || dry_run) + printf("%s%s\n", sql, dry_run ? " -- not executed" : ""); - status = PQsendQuery(conn, sql) == 1; + if (dry_run) + ParallelSlotSetIdle(free_slot); + else + status = PQsendQuery(conn, sql) == 1; if (!status) { diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h index 90db4fa1a64..586b6caa3d6 100644 --- a/src/bin/scripts/vacuuming.h +++ b/src/bin/scripts/vacuuming.h @@ -53,6 +53,7 @@ typedef struct vacuumingOptions bool missing_stats_only; bool echo; bool quiet; + bool dry_run; } vacuumingOptions; /* Valid values for vacuumingOptions->objfilter */ -- 2.39.5 (Apple Git-154)
