>
> > My attempts to test this all got stuck in wait_on_slots(). I haven't
> > looked too closely, but I suspect the issue is that the socket never
> > becomes readable because we don't send a query. If I set
> free_slot->inUse
> > to false before printing the command, it no longer hangs. We probably
> want
> > to create a function in parallel_slot.c to mark slots that we don't
> intend
> > to give a query as idle.
> >
> > Would that be preferable to skipping the creation of extra connections
> for parallel workers? I can see it both ways. On the one hand we want to
> give as true a reflection of "what would happen with these options", and on
> the other hand one could view the creation of extra workers as "real" vs a
> dry run.
> >
> >
>
>
Now with zero hangs and some test cases. I didn't create a function (yet)
as it seemed trivial.
From 396f243e729e2aaaf59cd820def4e093e51033bc Mon Sep 17 00:00:00 2001
From: Corey Huinker <[email protected]>
Date: Mon, 10 Nov 2025 14:33:41 -0500
Subject: [PATCH v2] 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.
---
src/bin/scripts/t/100_vacuumdb.pl | 12 +++++++++
src/bin/scripts/vacuumdb.c | 6 +++++
src/bin/scripts/vacuuming.c | 42 +++++++++++++++++++++++--------
src/bin/scripts/vacuuming.h | 1 +
doc/src/sgml/ref/vacuumdb.sgml | 11 ++++++++
5 files changed, 61 insertions(+), 11 deletions(-)
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index a16fad593f7..9fef3cbb80f 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 --schema');
$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 with missing stats');
$node->issues_sql_like(
[
'vacuumdb', '--analyze-only',
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index e117dac2242..aa0dc366eb0 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}
};
@@ -86,6 +87,7 @@ main(int argc, char *argv[])
vacopts.do_truncate = true;
vacopts.process_main = true;
vacopts.process_toast = true;
+ vacopts.dry_run = false;
/* the same for connection parameters */
memset(&cparams, 0, sizeof(cparams));
@@ -213,6 +215,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);
@@ -375,6 +380,7 @@ help(const char *progname)
printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
" stages for faster results; no vacuum\n"));
+ printf(_(" --dry-run do not vacuum/analyze the selected tables, only print\n"));
printf(_(" -?, --help show this help, then exit\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index f836f21fb03..5d9c07f8715 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -378,13 +378,25 @@ vacuum_one_database(ConnParams *cparams,
prepare_vacuum_command(free_slot->connection, &sql,
vacopts, tabname);
- /*
- * Execute the vacuum. All errors are handled in processQueryResult
- * through ParallelSlotsGetIdle.
- */
- ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, sql.data,
- echo, tabname);
+ if (vacopts->dry_run)
+ {
+ /*
+ * Print the command that we would have run in a real run,
+ * the immediately mark the unused slot as free again.
+ */
+ printf("not executed: %s\n", sql.data);
+ free_slot->inUse = false;
+ }
+ else
+ {
+ /*
+ * Execute the vacuum. All errors are handled in processQueryResult
+ * through ParallelSlotsGetIdle.
+ */
+ ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
+ run_vacuum_command(free_slot->connection, sql.data,
+ echo, tabname);
+ }
cell = cell->next;
} while (cell != NULL);
@@ -407,11 +419,19 @@ vacuum_one_database(ConnParams *cparams,
goto finish;
}
- ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, cmd, echo, NULL);
+ if (vacopts->dry_run)
+ {
+ printf("not executed: %s\n", cmd);
+ free_slot->inUse = false;
+ }
+ else
+ {
+ ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
+ run_vacuum_command(free_slot->connection, cmd, echo, NULL);
- if (!ParallelSlotsWaitCompletion(sa))
- ret = EXIT_FAILURE; /* error already reported by handler */
+ if (!ParallelSlotsWaitCompletion(sa))
+ ret = EXIT_FAILURE; /* error already reported by handler */
+ }
}
finish:
diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h
index 49f968b32e5..50155239e7e 100644
--- a/src/bin/scripts/vacuuming.h
+++ b/src/bin/scripts/vacuuming.h
@@ -51,6 +51,7 @@ typedef struct vacuumingOptions
bool skip_database_stats;
char *buffer_usage_limit;
bool missing_stats_only;
+ bool dry_run;
} vacuumingOptions;
/* Valid values for vacuumingOptions->objfilter */
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 84c76d7350c..100691b579a 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -171,6 +171,17 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--dry-run</option></term>
+ <listitem>
+ <para>
+ Print but do not execute the vacuum or analyze commands generated.
+ This is useful for testing the effects of various command-line options
+ before actually running the commands.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-e</option></term>
<term><option>--echo</option></term>
base-commit: 6b46669883fac9521c20fe4e2c55ccfbee778591
--
2.51.1
