v1-0004-Add-min-relation-size-option-to-vacuumdb.patch

From 8663a1a0c40142764d759bf375f3f7192d7c16e2 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Wed, 19 Dec 2018 20:15:39 +0000
Subject: [PATCH v1 4/4] Add --min-relation-size option to vacuumdb.

---
 doc/src/sgml/ref/vacuumdb.sgml    | 21 +++++++++++++++++++++
 src/bin/scripts/t/100_vacuumdb.pl |  9 ++++++++-
 src/bin/scripts/vacuumdb.c        | 33 ++++++++++++++++++++++++++++++---
 3 files changed, 59 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 94e8aac268..0fbccf310e 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -178,6 +178,27 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--min-relation-size <replaceable class="parameter">size</replaceable></option></term>
+      <listitem>
+       <para>
+        Only execute the vacuum or analyze commands on tables with a total size
+        of at least <replaceable class="parameter">size</replaceable> megabytes.
+       </para>
+       <para>
+        Note that this option cannot be used in conjunction with the
+        <option>--table</option> option.
+       </para>
+       <note>
+        <para>
+         This option is available for servers running PostgreSQL 8.1 and later.
+         If specified on a server running an older version of PostgreSQL, this
+         option is silently ignored.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--min-xid-age <replaceable class="parameter">xid_age</replaceable></option></term>
       <listitem>
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 45f79b244e..982195817e 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 => 30;
+use Test::More tests => 33;
 
 program_help_ok('vacuumdb');
 program_version_ok('vacuumdb');
@@ -75,3 +75,10 @@ $node->issues_sql_like(
 	[ 'vacuumdb', '--min-mxid-age=123456789', 'postgres' ],
 	qr/AND greatest\(mxid_age\(c.relminmxid\), mxid_age\(t.relminmxid\)\) >= 123456789/,
 	'vacuumdb --min-mxid-age');
+$node->command_fails(
+	[qw|vacuumdb -Zt funcidx --min-relation-size 8192|],
+	'specified table with --min-relation-size option');
+$node->issues_sql_like(
+	[ 'vacuumdb', '--min-relation-size=8192', 'postgres' ],
+	qr/AND pg_total_relation_size\(c.oid\) >= 8589934592/,
+	'vacuumdb --min-relation-size');
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index a2622fab24..243126bbeb 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -43,6 +43,7 @@ typedef struct vacuumingOptions
 	bool		skip_locked;
 	int			min_xid_age;
 	int			min_mxid_age;
+	long		min_rel_size_bytes;
 } vacuumingOptions;
 
 
@@ -116,6 +117,7 @@ main(int argc, char *argv[])
 		{"skip-locked", no_argument, NULL, 4},
 		{"min-xid-age", required_argument, NULL, 5},
 		{"min-mxid-age", required_argument, NULL, 6},
+		{"min-relation-size", required_argument, NULL, 7},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -240,6 +242,15 @@ main(int argc, char *argv[])
 					exit(1);
 				}
 				break;
+			case 7:
+				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);
@@ -288,6 +299,13 @@ main(int argc, char *argv[])
 		exit(1);
 	}
 
+	if (tables.head != NULL && vacopts.min_rel_size_bytes != 0)
+	{
+		fprintf(stderr, _("%s: cannot vacuum specific tables in conjunction with \"%s\"\n"),
+				progname, "--min-relation-size");
+		exit(1);
+	}
+
 	setup_cancel_handler();
 
 	/* Avoid opening extra connections. */
@@ -386,6 +404,7 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 	bool		parallel = concurrentCons > 1;
 	bool		filter_by_xid_age;
 	bool		filter_by_mxid_age;
+	bool		filter_by_rel_size;
 	const char *stage_commands[] = {
 		"SET default_statistics_target=1; SET vacuum_cost_delay=0;",
 		"SET default_statistics_target=10; RESET vacuum_cost_delay;",
@@ -416,20 +435,22 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 
 	/*
 	 * Before 7.2, age() did not exist.  Before 9.5, mxid_age() did not exist.
+	 * Before 8.1, pg_total_relation_size() did not exist.
 	 */
 	filter_by_xid_age = vacopts->min_xid_age != 0 && PQserverVersion(conn) >= 70200;
 	filter_by_mxid_age = vacopts->min_mxid_age != 0 && PQserverVersion(conn) >= 90500;
+	filter_by_rel_size = vacopts->min_rel_size_bytes != 0 && PQserverVersion(conn) >= 80100;
 
 	/*
 	 * If a table list is not provided and we're using multiple connections,
 	 * prepare the list of tables by querying the catalogs.
 	 *
 	 * If a table list is not provided and we're using one connection, prepare
-	 * the list of tables by querying the catalogs only if --min-xid-age or
-	 * --min-mxid-age is specified.
+	 * the list of tables by querying the catalogs only if --min-xid-age,
+	 * --min-mxid-age, or --min-relation-size is specified.
 	 */
 	if ((parallel && (!tables || !tables->head)) ||
-		(!parallel && (filter_by_xid_age || filter_by_mxid_age)))
+		(!parallel && (filter_by_xid_age || filter_by_mxid_age || filter_by_rel_size)))
 	{
 		PQExpBufferData buf;
 		PQExpBufferData catalog_query;
@@ -456,6 +477,11 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 							  " AND greatest(mxid_age(c.relminmxid), mxid_age(t.relminmxid)) >= %d\n",
 							  vacopts->min_mxid_age);
 
+		if (filter_by_rel_size)
+			appendPQExpBuffer(&catalog_query,
+							  " AND pg_total_relation_size(c.oid) >= %ld\n",
+							  vacopts->min_rel_size_bytes);
+
 		appendPQExpBuffer(&catalog_query, " ORDER BY c.relpages DESC;");
 		res = executeQuery(conn, catalog_query.data, progname, echo);
 		termPQExpBuffer(&catalog_query);
@@ -1087,6 +1113,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    miminum 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