pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Nathan Bossart <nathandbossart@gmail.com>
To: Corey Huinker <corey.huinker@gmail.com>
Cc: Chao Li <li.evan.chao@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: vacuumdb: add --dry-run
Date: Thu, 20 Nov 2025 15:46:42 -0600
Message-ID: <aR-MQgYi_FtSsy66@nathan> (raw )
In-Reply-To: <CADkLM=d+XROe3JgM5gZno0JGEgxnDDCYsEHgQjg5x7d0OQp5vA@mail.gmail.com >
References: <CADkLM=ckHkX7Of5SrK7g0LokPUwJ=kk8JU1GXGF5pZ1eBVr0=Q@mail.gmail.com >
<aRJW2_Yy9ALMkZUa@nathan >
<CADkLM=fo48tbJ75VPDDNgMV3f-QWLCk2TPujGP4ockxVDFRiTQ@mail.gmail.com >
<3DB353E5-944D-4E18-9DA2-4151F44AA59B@gmail.com >
<CADkLM=dgpe7wgjLhnpTycPRzUn6_U9Tk8qNAxHALi4-JqgPp=g@mail.gmail.com >
<aR5IMz4cngn8hkXI@nathan >
<CADkLM=dwBjL5fK-Dq_2qhAuHsHPab_Kt+R7KZTA9w_X0K6bENg@mail.gmail.com >
<CADkLM=d+XROe3JgM5gZno0JGEgxnDDCYsEHgQjg5x7d0OQp5vA@mail.gmail.com >
On Wed, Nov 19, 2025 at 07:54:06PM -0500, Corey Huinker wrote:
> Here's a shopping list of incremental changes. I'm happy with whatever
> makes the most sense to you.
Here is a v4 patch set. I've made a variety of small changes. I think
there's some room to bike-shed on the messages we send to the user to
assure them we're not actually doing anything, but this is roughly what I
imagined.
--
nathan
Attachments: download all (2 files, .tar.gz)
v4-0001-Add-ParallelSlotSetIdle.patch (text/plain, 1.6K) download
From 225e0002409fd3026e374290f0b664c63f281dd1 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Thu, 20 Nov 2025 14:16:02 -0600
Subject: [PATCH v4 1/2] 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)
v4-0002-Add-dry-run-to-vacuumdb.patch (text/plain, 10.8K) download
From 121cc204f732d5aeffb71cfa513b0ecb4bba1985 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Mon, 10 Nov 2025 14:33:41 -0500
Subject: [PATCH v4 2/2] 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 | 11 ++++++-
src/bin/scripts/vacuuming.c | 50 +++++++++++++++++--------------
src/bin/scripts/vacuuming.h | 3 +-
5 files changed, 62 insertions(+), 24 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 e117dac2242..68323fd2d10 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}
};
@@ -70,6 +71,7 @@ main(int argc, char *argv[])
ConnParams cparams;
bool echo = false;
bool quiet = false;
+ bool dry_run = false;
vacuumingOptions vacopts;
SimpleStringList objects = {NULL, NULL};
int concurrentCons = 1;
@@ -213,6 +215,9 @@ main(int argc, char *argv[])
case 14:
vacopts.missing_stats_only = true;
break;
+ case 15:
+ dry_run = true;
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -309,10 +314,13 @@ 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 (dry_run)
+ pg_log_info("Executing in dry-run mode.");
+
ret = vacuuming_main(&cparams, dbname, maintenance_db, &vacopts,
&objects, tbl_count,
concurrentCons,
- progname, echo, quiet);
+ progname, echo, quiet, dry_run);
exit(ret);
}
@@ -351,6 +359,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 f836f21fb03..b7e0ce65c2e 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -30,12 +30,14 @@ static int vacuum_one_database(ConnParams *cparams,
SimpleStringList *objects,
SimpleStringList **found_objs,
int concurrentCons,
- const char *progname, bool echo, bool quiet);
+ const char *progname, bool echo, bool quiet,
+ bool dry_run);
static int vacuum_all_databases(ConnParams *cparams,
vacuumingOptions *vacopts,
SimpleStringList *objects,
int concurrentCons,
- const char *progname, bool echo, bool quiet);
+ const char *progname, bool echo, bool quiet,
+ bool dry_run);
static SimpleStringList *retrieve_objects(PGconn *conn,
vacuumingOptions *vacopts,
SimpleStringList *objects,
@@ -43,8 +45,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
@@ -56,7 +58,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, bool echo, bool quiet, bool dry_run)
{
setup_cancel_handler(NULL);
@@ -71,7 +73,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
return vacuum_all_databases(cparams, vacopts,
objects,
concurrentCons,
- progname, echo, quiet);
+ progname, echo, quiet, dry_run);
}
else
{
@@ -100,7 +102,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
objects,
vacopts->missing_stats_only ? &found_objs : NULL,
concurrentCons,
- progname, echo, quiet);
+ progname, echo, quiet, dry_run);
if (ret != 0)
{
free_retrieved_objects(found_objs);
@@ -116,7 +118,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
ANALYZE_NO_STAGE,
objects, NULL,
concurrentCons,
- progname, echo, quiet);
+ progname, echo, quiet, dry_run);
}
}
@@ -167,7 +169,7 @@ vacuum_one_database(ConnParams *cparams,
SimpleStringList *objects,
SimpleStringList **found_objs,
int concurrentCons,
- const char *progname, bool echo, bool quiet)
+ const char *progname, bool echo, bool quiet, bool dry_run)
{
PQExpBufferData sql;
PGconn *conn;
@@ -338,7 +340,7 @@ vacuum_one_database(ConnParams *cparams,
* caller requested that mode. We have to prepare the initial connection
* ourselves before setting up the slots.
*/
- if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
+ if (vacopts->mode == MODE_ANALYZE_IN_STAGES && !dry_run)
{
initcmd = stage_commands[stage];
executeCommand(conn, initcmd, echo);
@@ -383,8 +385,8 @@ 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, sql.data,
+ echo, dry_run, tabname);
cell = cell->next;
} while (cell != NULL);
@@ -408,7 +410,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, cmd, echo, dry_run, NULL);
if (!ParallelSlotsWaitCompletion(sa))
ret = EXIT_FAILURE; /* error already reported by handler */
@@ -436,7 +438,7 @@ vacuum_all_databases(ConnParams *cparams,
vacuumingOptions *vacopts,
SimpleStringList *objects,
int concurrentCons,
- const char *progname, bool echo, bool quiet)
+ const char *progname, bool echo, bool quiet, bool dry_run)
{
int ret = EXIT_SUCCESS;
PGconn *conn;
@@ -474,7 +476,7 @@ vacuum_all_databases(ConnParams *cparams,
objects,
vacopts->missing_stats_only ? &found_objs[i] : NULL,
concurrentCons,
- progname, echo, quiet);
+ progname, echo, quiet, dry_run);
if (ret != EXIT_SUCCESS)
break;
}
@@ -499,7 +501,7 @@ vacuum_all_databases(ConnParams *cparams,
objects,
NULL,
concurrentCons,
- progname, echo, quiet);
+ progname, echo, quiet, dry_run);
if (ret != EXIT_SUCCESS)
break;
}
@@ -1001,15 +1003,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 49f968b32e5..46e53fc69c3 100644
--- a/src/bin/scripts/vacuuming.h
+++ b/src/bin/scripts/vacuuming.h
@@ -65,7 +65,8 @@ 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, bool echo, bool quiet,
+ bool dry_run);
extern char *escape_quotes(const char *src);
--
2.39.5 (Apple Git-154)
view thread (32+ messages) latest in thread
Message-ID: <aR-MQgYi_FtSsy66@nathan>
Permalink: ../aR-MQgYi_FtSsy66@nathan/
Also on: postgresql.org/message-id/aR-MQgYi_FtSsy66@nathan
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, corey.huinker@gmail.com, li.evan.chao@gmail.com
Subject: Re: vacuumdb: add --dry-run
In-Reply-To: <aR-MQgYi_FtSsy66@nathan>
* 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