From 2d58da7fd23a6f5f3e27209cb1a06261a3495c0c Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Fri, 4 Jan 2019 22:11:32 +0000 Subject: [PATCH v2 6/6] Add --min-relation-size option to vacuumdb. --- doc/src/sgml/ref/vacuumdb.sgml | 16 ++++++++++++++++ src/bin/scripts/t/100_vacuumdb.pl | 6 +++++- src/bin/scripts/vacuumdb.c | 24 ++++++++++++++++++++++++ 3 files changed, 45 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml index e42d4998ca..c72c245d8d 100644 --- a/doc/src/sgml/ref/vacuumdb.sgml +++ b/doc/src/sgml/ref/vacuumdb.sgml @@ -188,6 +188,22 @@ PostgreSQL documentation + + + + + Only execute the vacuum or analyze commands on tables with a total size + of at least size megabytes. + + + + This option is only available for servers running PostgreSQL 8.1 and + later. + + + + + diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl index 02683da029..fb608c5180 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 => 32; +use Test::More tests => 34; program_help_ok('vacuumdb'); program_version_ok('vacuumdb'); @@ -80,3 +80,7 @@ $node->issues_sql_like( [ 'vacuumdb', '--min-xid-age=987654321', 'postgres' ], qr/AND GREATEST\(pg_catalog.age\(c.relfrozenxid\), pg_catalog.age\(t.relfrozenxid\)\) OPERATOR\(pg_catalog.>=\) 987654321/, 'vacuumdb --min-xid-age'); +$node->issues_sql_like( + [ 'vacuumdb', '--min-relation-size=8192', 'postgres' ], + qr/AND pg_catalog.pg_total_relation_size\(c.oid\) OPERATOR\(pg_catalog.>=\) 8589934592/, + 'vacuumdb --min-relation-size'); diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index 7792fd1c61..dc5f204ceb 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -45,6 +45,7 @@ typedef struct vacuumingOptions bool skip_locked; int min_xid_age; int min_mxid_age; + long min_rel_size_bytes; } vacuumingOptions; @@ -117,6 +118,7 @@ main(int argc, char *argv[]) {"skip-locked", no_argument, NULL, 5}, {"min-xid-age", required_argument, NULL, 6}, {"min-mxid-age", required_argument, NULL, 7}, + {"min-relation-size", required_argument, NULL, 8}, {NULL, 0, NULL, 0} }; @@ -244,6 +246,15 @@ main(int argc, char *argv[]) exit(1); } break; + case 8: + vacopts.min_rel_size_bytes = (long) atoi(optarg) * 1024L * 1024L; + if (vacopts.min_rel_size_bytes <= 0) + { + fprintf(stderr, _("%s: minimum relation size must be at least 1\n"), + progname); + exit(1); + } + break; default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); @@ -437,6 +448,13 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts, exit(1); } + if (vacopts->min_rel_size_bytes != 0 && PQserverVersion(conn) < 80100) + { + fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 8.1\n"), + progname, "--min-relation-size"); + exit(1); + } + if (!quiet) { if (stage != ANALYZE_NO_STAGE) @@ -472,6 +490,11 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts, " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=) %d\n", vacopts->min_mxid_age); + if (vacopts->min_rel_size_bytes != 0) + appendPQExpBuffer(&catalog_query, + " AND pg_catalog.pg_total_relation_size(c.oid) OPERATOR(pg_catalog.>=) %ld\n", + vacopts->min_rel_size_bytes); + cell = tables ? tables->head : NULL; while (cell != NULL) { @@ -1112,6 +1135,7 @@ help(const char *progname) printf(_(" -F, --freeze freeze row transaction information\n")); printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n")); printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n")); + printf(_(" --min-relation-size=SIZE minimum size of tables to vacuum, in megabytes\n")); printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n")); printf(_(" -q, --quiet don't write any messages\n")); printf(_(" --skip-locked skip relations that cannot be immediately locked\n")); -- 2.16.5