v1-0003-Add-min-xid-age-and-min-mxid-age-options-to-vacuu.patch

From 066639fbd1673ad5044ae50ad50af6ff24cd93a3 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Wed, 19 Dec 2018 01:18:50 +0000
Subject: [PATCH v1 3/4] Add --min-xid-age and --min-mxid-age options to
 vacuumdb.

---
 doc/src/sgml/ref/vacuumdb.sgml    | 42 ++++++++++++++++++
 src/bin/scripts/t/100_vacuumdb.pl | 13 +++++-
 src/bin/scripts/vacuumdb.c        | 90 +++++++++++++++++++++++++++++++++------
 3 files changed, 130 insertions(+), 15 deletions(-)

diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 942c8ba874..94e8aac268 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -157,6 +157,48 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--min-mxid-age <replaceable class="parameter">mxid_age</replaceable></option></term>
+      <listitem>
+       <para>
+        Only execute the vacuum or analyze commands on tables with a multixact
+        ID age of at least <replaceable class="parameter">mxid_age</replaceable>.
+       </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 9.5 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>
+       <para>
+        Only execute the vacuum or analyze commands on tables with a transaction
+        ID age of at least <replaceable class="parameter">xid_age</replaceable>.
+       </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 7.2 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>-q</option></term>
       <term><option>--quiet</option></term>
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 239ed76834..45f79b244e 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 => 25;
+use Test::More tests => 30;
 
 program_help_ok('vacuumdb');
 program_version_ok('vacuumdb');
@@ -64,3 +64,14 @@ $node->command_ok([qw|vacuumdb -Z --table="need""q(uot"(")x") postgres|],
 $node->command_fails(
 	[qw|vacuumdb -Zt funcidx postgres|],
 	'unqualifed name via functional index');
+$node->command_fails(
+	[qw|vacuumdb -Zt funcidx --min-mxid-age 1000000000|],
+	'specified table with --min-mxid-age option');
+$node->issues_sql_like(
+	[ 'vacuumdb', '--min-xid-age=123456789', 'postgres' ],
+	qr/AND greatest\(age\(c.relfrozenxid\), age\(t.relfrozenxid\)\) >= 123456789/,
+	'vacuumdb --min-xid-age');
+$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');
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 5aa2833a73..a2622fab24 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -41,6 +41,8 @@ typedef struct vacuumingOptions
 	bool		full;
 	bool		freeze;
 	bool		skip_locked;
+	int			min_xid_age;
+	int			min_mxid_age;
 } vacuumingOptions;
 
 
@@ -112,6 +114,8 @@ main(int argc, char *argv[])
 		{"maintenance-db", required_argument, NULL, 2},
 		{"analyze-in-stages", no_argument, NULL, 3},
 		{"skip-locked", no_argument, NULL, 4},
+		{"min-xid-age", required_argument, NULL, 5},
+		{"min-mxid-age", required_argument, NULL, 6},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -218,6 +222,24 @@ main(int argc, char *argv[])
 			case 4:
 				vacopts.skip_locked = true;
 				break;
+			case 5:
+				vacopts.min_xid_age = atoi(optarg);
+				if (vacopts.min_xid_age <= 0)
+				{
+					fprintf(stderr, _("%s: minimum transaction ID age must be at least 1\n"),
+							progname);
+					exit(1);
+				}
+				break;
+			case 6:
+				vacopts.min_mxid_age = atoi(optarg);
+				if (vacopts.min_mxid_age <= 0)
+				{
+					fprintf(stderr, _("%s: minimum multixact ID age must be at least 1\n"),
+							progname);
+					exit(1);
+				}
+				break;
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -259,6 +281,13 @@ main(int argc, char *argv[])
 		/* allow 'and_analyze' with 'analyze_only' */
 	}
 
+	if (tables.head != NULL && (vacopts.min_xid_age != 0 || vacopts.min_mxid_age != 0))
+	{
+		fprintf(stderr, _("%s: cannot vacuum specific tables in conjunction with \"%s\" or \"%s\"\n"),
+				progname, "--min-xid-age", "--min-mxid-age");
+		exit(1);
+	}
+
 	setup_cancel_handler();
 
 	/* Avoid opening extra connections. */
@@ -355,6 +384,8 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 	int			i;
 	bool		failed = false;
 	bool		parallel = concurrentCons > 1;
+	bool		filter_by_xid_age;
+	bool		filter_by_mxid_age;
 	const char *stage_commands[] = {
 		"SET default_statistics_target=1; SET vacuum_cost_delay=0;",
 		"SET default_statistics_target=10; RESET vacuum_cost_delay;",
@@ -383,25 +414,51 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 		fflush(stdout);
 	}
 
+	/*
+	 * Before 7.2, age() did not exist.  Before 9.5, mxid_age() 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;
+
 	/*
 	 * 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.
 	 */
-	if (parallel && (!tables || !tables->head))
+	if ((parallel && (!tables || !tables->head)) ||
+		(!parallel && (filter_by_xid_age || filter_by_mxid_age)))
 	{
 		PQExpBufferData buf;
+		PQExpBufferData catalog_query;
 		PGresult   *res;
 		int			ntups;
 
-		res = executeQuery(conn,
-						   "SELECT c.relname, ns.nspname"
-						   " FROM pg_class c, pg_namespace ns\n"
-						   " WHERE relkind IN ("
-						   CppAsString2(RELKIND_RELATION) ", "
-						   CppAsString2(RELKIND_MATVIEW) ")"
-						   " AND c.relnamespace = ns.oid\n"
-						   " ORDER BY c.relpages DESC;",
-						   progname, echo);
+		initPQExpBuffer(&catalog_query);
+		appendPQExpBuffer(&catalog_query,
+						  "SELECT c.relname, ns.nspname"
+						  " FROM pg_class c\n"
+						  " JOIN pg_namespace ns ON c.relnamespace = ns.oid\n"
+						  " LEFT JOIN pg_class t ON c.reltoastrelid = t.oid\n"
+						  " WHERE c.relkind IN ("
+						  CppAsString2(RELKIND_RELATION) ", "
+						  CppAsString2(RELKIND_MATVIEW) ")\n");
+
+		if (filter_by_xid_age)
+			appendPQExpBuffer(&catalog_query,
+							  " AND greatest(age(c.relfrozenxid), age(t.relfrozenxid)) >= %d\n",
+							  vacopts->min_xid_age);
+
+		if (filter_by_mxid_age)
+			appendPQExpBuffer(&catalog_query,
+							  " AND greatest(mxid_age(c.relminmxid), mxid_age(t.relminmxid)) >= %d\n",
+							  vacopts->min_mxid_age);
+
+		appendPQExpBuffer(&catalog_query, " ORDER BY c.relpages DESC;");
+		res = executeQuery(conn, catalog_query.data, progname, echo);
+		termPQExpBuffer(&catalog_query);
 
 		ntups = PQntuples(res);
 		if (ntups == 0)
@@ -429,10 +486,13 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 		 * If there are more connections than vacuumable relations, we don't
 		 * need to use them all.
 		 */
-		if (concurrentCons > ntups)
-			concurrentCons = ntups;
-		if (concurrentCons <= 1)
-			parallel = false;
+		if (parallel)
+		{
+			if (concurrentCons > ntups)
+				concurrentCons = ntups;
+			if (concurrentCons <= 1)
+				parallel = false;
+		}
 		PQclear(res);
 	}
 
@@ -1026,6 +1086,8 @@ help(const char *progname)
 	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(_("      --min-mxid-age=MXID_AGE     minimum multixact ID age of tables to vacuum\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"));
 	printf(_("  -t, --table='TABLE[(COLUMNS)]'  vacuum specific table(s) only\n"));
-- 
2.16.5