diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 955a17a849..da4d51e763 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -102,6 +102,23 @@ PostgreSQL documentation
+
+
+
+
+ Disable all page-skipping behavior during processing based on
+ the visibility map, similarly to the option
+ DISABLE_PAGE_SKIPPING for VACUUM.
+
+
+
+ This option is only available for servers running
+ PostgreSQL 9.6 and later.
+
+
+
+
+
@@ -167,6 +184,21 @@ PostgreSQL documentation
+
+
+
+
+ Skip relations that cannot be immediately locked for processing.
+
+
+
+ This option is only available for servers running
+ PostgreSQL 12 and later.
+
+
+
+
+
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 4c477a27aa..4a2c71d665 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -3,7 +3,7 @@ use warnings;
use PostgresNode;
use TestLib;
-use Test::More tests => 23;
+use Test::More tests => 30;
program_help_ok('vacuumdb');
program_version_ok('vacuumdb');
@@ -33,6 +33,21 @@ $node->issues_sql_like(
[ 'vacuumdb', '-Z', 'postgres' ],
qr/statement: ANALYZE;/,
'vacuumdb -Z');
+$node->issues_sql_like(
+ [ 'vacuumdb', '--disable-page-skipping', 'postgres' ],
+ qr/statement: VACUUM \(DISABLE_PAGE_SKIPPING\);/,
+ 'vacuumdb --disable-page-skipping');
+$node->issues_sql_like(
+ [ 'vacuumdb', '--skip-locked', 'postgres' ],
+ qr/statement: VACUUM \(SKIP_LOCKED\);/,
+ 'vacuumdb --skip-locked');
+$node->issues_sql_like(
+ [ 'vacuumdb', '--skip-locked', '--analyze-only', 'postgres' ],
+ qr/statement: ANALYZE \(SKIP_LOCKED\);/,
+ 'vacuumdb --skip-locked --analyze-only');
+$node->command_fails(
+ [qw(vacuumdb --analyze-only --disable-page-skipping postgres)],
+ '--analyze-only and --disable-page-skipping specified together');
$node->command_ok([qw(vacuumdb -Z --table=pg_am dbname=template1)],
'vacuumdb with connection string');
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index e0a8303f3e..0b82f5ed50 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -40,6 +40,8 @@ typedef struct vacuumingOptions
bool and_analyze;
bool full;
bool freeze;
+ bool disable_page_skipping;
+ bool skip_locked;
} vacuumingOptions;
@@ -110,6 +112,8 @@ main(int argc, char *argv[])
{"jobs", required_argument, NULL, 'j'},
{"maintenance-db", required_argument, NULL, 2},
{"analyze-in-stages", no_argument, NULL, 3},
+ {"disable-page-skipping", no_argument, NULL, 4},
+ {"skip-locked", no_argument, NULL, 5},
{NULL, 0, NULL, 0}
};
@@ -213,6 +217,12 @@ main(int argc, char *argv[])
case 3:
analyze_in_stages = vacopts.analyze_only = true;
break;
+ case 4:
+ vacopts.disable_page_skipping = true;
+ break;
+ case 5:
+ vacopts.skip_locked = true;
+ break;
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -251,6 +261,12 @@ main(int argc, char *argv[])
progname, "freeze");
exit(1);
}
+ if (vacopts.disable_page_skipping)
+ {
+ fprintf(stderr, _("%s: cannot use the \"%s\" option when performing only analyze\n"),
+ progname, "disable-page-skipping");
+ exit(1);
+ }
/* allow 'and_analyze' with 'analyze_only' */
}
@@ -367,6 +383,20 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
conn = connectDatabase(dbname, host, port, username, prompt_password,
progname, echo, false, true);
+ if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600)
+ {
+ fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 9.6\n"),
+ progname, "disable-page-skipping");
+ exit(1);
+ }
+
+ if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
+ {
+ fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 12\n"),
+ progname, "skip-locked");
+ exit(1);
+ }
+
if (!quiet)
{
if (stage != ANALYZE_NO_STAGE)
@@ -630,23 +660,61 @@ prepare_vacuum_command(PQExpBuffer sql, PGconn *conn,
bool table_pre_qualified,
const char *progname, bool echo)
{
+ const char *paren = " (";
+ const char *comma = ", ";
+ const char *sep = paren;
+
resetPQExpBuffer(sql);
if (vacopts->analyze_only)
{
appendPQExpBufferStr(sql, "ANALYZE");
- if (vacopts->verbose)
- appendPQExpBufferStr(sql, " VERBOSE");
+
+ /* parenthesized grammar of ANALYZE is supported since v11 */
+ if (PQserverVersion(conn) >= 110000)
+ {
+ if (vacopts->skip_locked)
+ {
+ /* SKIP_LOCKED is supported since v12 */
+ Assert(PQserverVersion(conn) >= 120000);
+ appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
+ sep = comma;
+ }
+ if (vacopts->verbose)
+ {
+ appendPQExpBuffer(sql, "%sVERBOSE", sep);
+ sep = comma;
+ }
+ if (sep != paren)
+ appendPQExpBufferChar(sql, ')');
+ }
+ else
+ {
+ if (vacopts->verbose)
+ appendPQExpBufferStr(sql, " VERBOSE");
+ }
}
else
{
appendPQExpBufferStr(sql, "VACUUM");
+
+ /* parenthesized grammar of VACUUM is supported since v9.0 */
if (PQserverVersion(conn) >= 90000)
{
- const char *paren = " (";
- const char *comma = ", ";
- const char *sep = paren;
-
+ if (vacopts->disable_page_skipping)
+ {
+ /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
+ Assert(PQserverVersion(conn) >= 90600);
+ appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
+ sep = comma;
+ }
+ if (vacopts->skip_locked)
+ {
+ /* SKIP_LOCKED is supported since v12 */
+ Assert(PQserverVersion(conn) >= 120000);
+ appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
+ sep = comma;
+ }
if (vacopts->full)
{
appendPQExpBuffer(sql, "%sFULL", sep);
@@ -1000,11 +1068,13 @@ help(const char *progname)
printf(_("\nOptions:\n"));
printf(_(" -a, --all vacuum all databases\n"));
printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
+ printf(_(" --disable-page-skipping disable all page-skipping behavior\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"));
printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
printf(_(" -q, --quiet don't write any messages\n"));
+ printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
printf(_(" -v, --verbose write a lot of output\n"));
printf(_(" -V, --version output version information, then exit\n"));