On Mon, Dec 08, 2025 at 12:27:26PM -0600, Nathan Bossart wrote: > On Mon, Dec 08, 2025 at 07:23:16PM +0100, Álvaro Herrera wrote: >> I haven't read this thread, but chanced to come across this and wanted >> to note recent commit c05dee191125. I'm not opposed to changing what >> went in there, but let's make them all the same. > > Ah, that must've been where I stole from originally. Will switch it back.
As promised... -- nathan
>From a416a9594fd7433e5abbd4f68de97fed0579ac78 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Wed, 3 Dec 2025 16:12:37 -0600 Subject: [PATCH v7 1/3] vacuumdb: Move some options to vacuumingOptions struct. TODO Reviewed-by: Corey Huinker <[email protected]> Reviewed-by: Kirill Reshke <[email protected]> Reviewed-by: Chao Li <[email protected]> Discussion: https://postgr.es/m/CADkLM%3DckHkX7Of5SrK7g0LokPUwJ%3Dkk8JU1GXGF5pZ1eBVr0%3DQ%40mail.gmail.com --- src/bin/scripts/vacuumdb.c | 12 ++------ src/bin/scripts/vacuuming.c | 61 ++++++++++++++++++------------------- src/bin/scripts/vacuuming.h | 4 ++- 3 files changed, 36 insertions(+), 41 deletions(-) diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index e117dac2242..6783c843637 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; @@ -78,11 +76,7 @@ main(int argc, char *argv[]) /* initialize options */ memset(&vacopts, 0, sizeof(vacopts)); - vacopts.objfilter = 0; /* no filter */ vacopts.parallel_workers = -1; - vacopts.buffer_usage_limit = NULL; - vacopts.no_index_cleanup = false; - vacopts.force_index_cleanup = false; vacopts.do_truncate = true; vacopts.process_main = true; vacopts.process_toast = true; @@ -110,7 +104,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 +137,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 +306,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..74a1f20e0f3 100644 --- a/src/bin/scripts/vacuuming.c +++ b/src/bin/scripts/vacuuming.c @@ -30,21 +30,20 @@ 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); -static void run_vacuum_command(PGconn *conn, const char *sql, bool echo, - const char *table); +static void run_vacuum_command(PGconn *conn, vacuumingOptions *vacopts, + const char *sql, const char *table); /* * Executes vacuum/analyze as indicated. Returns 0 if the plan is carried @@ -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); @@ -383,8 +383,7 @@ vacuum_one_database(ConnParams *cparams, * through ParallelSlotsGetIdle. */ ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); - run_vacuum_command(free_slot->connection, sql.data, - echo, tabname); + run_vacuum_command(free_slot->connection, vacopts, sql.data, tabname); cell = cell->next; } while (cell != NULL); @@ -408,7 +407,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, vacopts, cmd, NULL); if (!ParallelSlotsWaitCompletion(sa)) ret = EXIT_FAILURE; /* error already reported by handler */ @@ -436,17 +435,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 +473,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 +498,7 @@ vacuum_all_databases(ConnParams *cparams, objects, NULL, concurrentCons, - progname, echo, quiet); + progname); if (ret != EXIT_SUCCESS) break; } @@ -524,7 +523,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 +775,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 @@ -1001,12 +1000,12 @@ 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(PGconn *conn, vacuumingOptions *vacopts, + const char *sql, const char *table) { bool status; - if (echo) + if (vacopts->echo) printf("%s\n", sql); status = PQsendQuery(conn, sql) == 1; 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 dfaab136d2a7e2e5c93e98ee8f27a7ac0b5084ba Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Thu, 20 Nov 2025 14:16:02 -0600 Subject: [PATCH v7 2/3] Add ParallelSlotSetIdle(). TODO Reviewed-by: Corey Huinker <[email protected]> Reviewed-by: Kirill Reshke <[email protected]> Reviewed-by: Chao Li <[email protected] Discussion: https://postgr.es/m/CADkLM%3DckHkX7Of5SrK7g0LokPUwJ%3Dkk8JU1GXGF5pZ1eBVr0%3DQ%40mail.gmail.com --- 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 30c3cd08d298aa75937bed3cf2edb74633dc2c22 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Mon, 8 Dec 2025 13:22:22 -0600 Subject: [PATCH v7 3/3] vacuumdb: Add --dry-run. MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit TODO Author: Corey Huinker <[email protected]> Reviewed-by: Chao Li <[email protected]> Reviewed-by: Kirill Reshke <[email protected]> Reviewed-by: Álvaro Herrera <[email protected]> Discussion: https://postgr.es/m/CADkLM%3DckHkX7Of5SrK7g0LokPUwJ%3Dkk8JU1GXGF5pZ1eBVr0%3DQ%40mail.gmail.com --- doc/src/sgml/ref/vacuumdb.sgml | 10 ++++++++++ src/bin/scripts/t/100_vacuumdb.pl | 12 ++++++++++++ src/bin/scripts/vacuumdb.c | 9 +++++++++ src/bin/scripts/vacuuming.c | 30 ++++++++++++++++++++---------- src/bin/scripts/vacuuming.h | 1 + 5 files changed, 52 insertions(+), 10 deletions(-) diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml index 84c76d7350c..508c8dfa146 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. + </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 6783c843637..0bc443be348 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} }; @@ -207,6 +208,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); @@ -303,6 +307,10 @@ 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 && !vacopts.quiet) + pg_log_info("Executing in dry-run mode.\n" + "No commands will be sent to the server."); + ret = vacuuming_main(&cparams, dbname, maintenance_db, &vacopts, &objects, tbl_count, concurrentCons, @@ -345,6 +353,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 show the 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 74a1f20e0f3..73239e86e0a 100644 --- a/src/bin/scripts/vacuuming.c +++ b/src/bin/scripts/vacuuming.c @@ -42,8 +42,9 @@ 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, vacuumingOptions *vacopts, - const char *sql, const char *table); +static void run_vacuum_command(ParallelSlot *free_slot, + vacuumingOptions *vacopts, const char *sql, + const char *table); /* * Executes vacuum/analyze as indicated. Returns 0 if the plan is carried @@ -340,7 +341,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\n", initcmd); + else + executeCommand(conn, initcmd, vacopts->echo); } else initcmd = NULL; @@ -383,7 +388,7 @@ vacuum_one_database(ConnParams *cparams, * through ParallelSlotsGetIdle. */ ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); - run_vacuum_command(free_slot->connection, vacopts, sql.data, tabname); + run_vacuum_command(free_slot, vacopts, sql.data, tabname); cell = cell->next; } while (cell != NULL); @@ -407,7 +412,7 @@ vacuum_one_database(ConnParams *cparams, } ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); - run_vacuum_command(free_slot->connection, vacopts, cmd, NULL); + run_vacuum_command(free_slot, vacopts, cmd, NULL); if (!ParallelSlotsWaitCompletion(sa)) ret = EXIT_FAILURE; /* error already reported by handler */ @@ -995,20 +1000,25 @@ prepare_vacuum_command(PGconn *conn, PQExpBuffer sql, /* * Send a vacuum/analyze command to the server, returning after sending the - * command. + * command. If dry_run is true, the command is printed but not sent to the + * server. * * Any errors during command execution are reported to stderr. */ static void -run_vacuum_command(PGconn *conn, vacuumingOptions *vacopts, +run_vacuum_command(ParallelSlot *free_slot, vacuumingOptions *vacopts, const char *sql, const char *table) { - bool status; + bool status = true; + PGconn *conn = free_slot->connection; - if (vacopts->echo) + if (vacopts->echo || vacopts->dry_run) printf("%s\n", sql); - status = PQsendQuery(conn, sql) == 1; + if (vacopts->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)
