Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vMCUO-003bIQ-1G for pgsql-hackers@arkaria.postgresql.org; Thu, 20 Nov 2025 21:46:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vMCUM-004HJc-1q for pgsql-hackers@arkaria.postgresql.org; Thu, 20 Nov 2025 21:46:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vMCUM-004HJT-0V for pgsql-hackers@lists.postgresql.org; Thu, 20 Nov 2025 21:46:50 +0000 Received: from mail-il1-x131.google.com ([2607:f8b0:4864:20::131]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vMCUK-000cMv-0z for pgsql-hackers@postgresql.org; Thu, 20 Nov 2025 21:46:50 +0000 Received: by mail-il1-x131.google.com with SMTP id e9e14a558f8ab-434717509afso7020415ab.2 for ; Thu, 20 Nov 2025 13:46:47 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1763675205; x=1764280005; darn=postgresql.org; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:from:to:cc:subject:date:message-id:reply-to; bh=1+5YWuSSy+I4pQMvUK0Ua2lx73s/3Io71YmSy7x7VbQ=; b=YCMipZOYrTGr/KawnqzgMD9qfx+GHgp8z6jGmey8FNRDZNRuzWgZVoYJmQgbb2xXR/ Cxd6GyodlKBGBUGYhNUo1cVC7nmac5x7B0rlzLAwnrZKdnf33+0pllP6cdUcjVWK7u6E rqp/ne+jvrIr87avyWVN9Da+UUvV7Bg+E/dPVEzvLLYVwqi5qexROUOY0FMASNbG9Iui 1vOOjMChS7if/47eFsfVIsuQ6R7zP2tbttVkqU7ZJB47WbW+yhzV9MfZC6iTFSfir9We dPJPk2+srv22YfSEah9GZ7kri0SsBxv0GvoxpHniGmYPnyqTMScx68MkNMhlwnWvBZcG RAAw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1763675205; x=1764280005; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:x-gm-gg:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=1+5YWuSSy+I4pQMvUK0Ua2lx73s/3Io71YmSy7x7VbQ=; b=rKTSB1MQE4WJhG3S1SLdb8CckFBF6Y+VHEZH8pAn0JJ4EYFIVmcBAdnb/KfHBjuJTA 2/ANFtxDVj3RRXxdXeQwEze2ULIuomRwiAcWC49+bGs7SNnfGqdT9wtt0wYcfkJ7qHNo Oza+PwOrivx+2DzHs12f8Kj7obP0v/ueANDI6G/krMvvOAgXkIX7NfbqFVEhGgXsk0Qw 2BMLCLGqdpQisKf1Fdyfz4o67c5mA1V/08zKz0YuyAcwfUU69XqGb+STeaD6g4WYof5q u8yqduNsdqWtMSNQ69mWgJWDuRhYPvfQ+ronVsTxOb/F+/fep6vIyrOgLXKf/84E7CO4 AdHw== X-Forwarded-Encrypted: i=1; AJvYcCV8wmo8YAjHnJZrAJutGU4YCUxbapjY4Nejbt6E39eJ5YH9BLlcXxlwcosKjl6Atz1J4Q7Bwxl1zWwuIaHh@postgresql.org X-Gm-Message-State: AOJu0YzVyyDXOPyB3ZJ2F8ax6mREN+fMmXAEslsq9KWUsBG+SvionrGm hvGPs/bOp5KlOqHM/feQFj/7+SY1O2AU/RIdukWyh7INwGfnzzX8OcI8NTItqQ== X-Gm-Gg: ASbGncs8i7CFjhE66Mw6tIrQZ6Yr2rW6IHzdiV3MTuFjPrQSHf0YqOXlpYTsHA1cVhz DfTs4bGGQLw+MozAt66wC+SNyry3zFRIc9Ua0qu/Sr9UihHdhphjJtuxu5Qx2JxDOeiDqcsOGuA jme+3R/o0Y1J2Btu1TFLNfuBoilYucPq5uV+IFEXIfPoET/xQNbd0WYl9Epit94oNU5p1u+LiLy /K5IeagkgFBKlGwdUYtX56gFWEtuhzN3+WIReT4r6zY8hsBAKFejfdbL59VWytz7ojb5hXnvcos Jg2a48RKuaU+PFMCE1DSvA9m92qvYx9Qm1xHhwBZyKCg1sr4yHD+BSMikJEMCOkMk8D0ZAuVgxY pHZmrGppGsRuQOuRMLVzGMYjdmEpkGbpRBqv/A5tVeag8LisYDspKFFeIx3qCWK0SP8NWUZmt1O N+0H8g9IxAUp7pQaBPhPx6cju1QOQN+bulDTzmhEGZLa1LcM2AGKhyR5nYQq39psg4RjOB0rVAI T/H X-Google-Smtp-Source: AGHT+IGwL4favJvpfMKccGMhAILG9zuvKKL1e4Jx6ccjNElD186wateqjfNgf8zq7nnmjHp1dRDIAQ== X-Received: by 2002:a92:c247:0:b0:433:7688:a6e6 with SMTP id e9e14a558f8ab-435b8e5edffmr1565915ab.20.1763675205132; Thu, 20 Nov 2025 13:46:45 -0800 (PST) Received: from nathan (162-195-168-172.lightspeed.stlsmo.sbcglobal.net. [162.195.168.172]) by smtp.gmail.com with ESMTPSA id 8926c6da1cb9f-5b954b210fdsm1402524173.30.2025.11.20.13.46.43 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 20 Nov 2025 13:46:44 -0800 (PST) Date: Thu, 20 Nov 2025 15:46:42 -0600 From: Nathan Bossart To: Corey Huinker Cc: Chao Li , pgsql-hackers@postgresql.org Subject: Re: vacuumdb: add --dry-run Message-ID: References: <3DB353E5-944D-4E18-9DA2-4151F44AA59B@gmail.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="3OyGmvNBiPiOL6vm" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --3OyGmvNBiPiOL6vm Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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 --3OyGmvNBiPiOL6vm Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=v4-0001-Add-ParallelSlotSetIdle.patch From 225e0002409fd3026e374290f0b664c63f281dd1 Mon Sep 17 00:00:00 2001 From: Nathan Bossart 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) --3OyGmvNBiPiOL6vm Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=v4-0002-Add-dry-run-to-vacuumdb.patch From 121cc204f732d5aeffb71cfa513b0ecb4bba1985 Mon Sep 17 00:00:00 2001 From: Corey Huinker 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 + + + + + Print, but do not execute, the vacuum and analyze commands that would + have been sent to the server (performs a dry run). + + + + 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) --3OyGmvNBiPiOL6vm--