v5-0004-Add-min-xid-age-and-min-mxid-age-options-to-vacuu.patch

From 9c1db3c811a0c1c2ba7cd2857bc5a1606a6fb014 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Tue, 22 Jan 2019 23:11:33 +0000
Subject: [PATCH v5 4/4] Add --min-xid-age and --min-mxid-age options to
 vacuumdb.

---
 doc/src/sgml/ref/vacuumdb.sgml    | 37 +++++++++++++++++++++++++++
 src/bin/scripts/t/100_vacuumdb.pl | 10 +++++++-
 src/bin/scripts/vacuumdb.c        | 54 ++++++++++++++++++++++++++++++++++++++-
 3 files changed, 99 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index f304627802..29ccba2a5a 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -172,6 +172,43 @@ 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>.
+        This setting is useful for prioritizing tables to process to prevent
+        multixact ID wraparound (see <xref
+        linkend="vacuum-for-multixact-wraparound"/>).
+       </para>
+       <note>
+        <para>
+         This option is only available for servers running PostgreSQL 9.5 and
+         later.
+        </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>.
+        This setting is useful for prioritizing tables to process to prevent
+        transaction ID wraparound (see <xref linkend="vacuum-for-wraparound"/>).
+       </para>
+       <note>
+        <para>
+         This option is only available for servers running PostgreSQL 7.2 and
+         later.
+        </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 ff0d97971b..b19a4abf26 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 => 35;
+use Test::More tests => 39;
 
 program_help_ok('vacuumdb');
 program_version_ok('vacuumdb');
@@ -88,3 +88,11 @@ $node->issues_sql_like(
 	[ 'vacuumdb', '--analyze-only', '--table', 'vactable(b)', 'postgres' ],
 	qr/statement: ANALYZE public.vactable\(b\);/,
 	'vacuumdb --analyze-only with partial column list');
+$node->issues_sql_like(
+	[ 'vacuumdb', '--table=pg_class', '--min-mxid-age=2147483000', 'postgres'],
+	qr/AND GREATEST\(pg_catalog.mxid_age\(c.relminmxid\), pg_catalog.mxid_age\(t.relminmxid\)\) OPERATOR\(pg_catalog.>=\) 2147483000/,
+	'vacuumdb --table --min-mxid-age');
+$node->issues_sql_like(
+	[ 'vacuumdb', '--min-xid-age=2147483001', 'postgres' ],
+	qr/AND GREATEST\(pg_catalog.age\(c.relfrozenxid\), pg_catalog.age\(t.relfrozenxid\)\) OPERATOR\(pg_catalog.>=\) 2147483001/,
+	'vacuumdb --min-xid-age');
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index b8bf2e3ff5..7ca3766eef 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -43,6 +43,8 @@ typedef struct vacuumingOptions
 	bool		freeze;
 	bool		disable_page_skipping;
 	bool		skip_locked;
+	int			min_xid_age;
+	int			min_mxid_age;
 } vacuumingOptions;
 
 
@@ -113,6 +115,8 @@ main(int argc, char *argv[])
 		{"analyze-in-stages", no_argument, NULL, 3},
 		{"disable-page-skipping", no_argument, NULL, 4},
 		{"skip-locked", no_argument, NULL, 5},
+		{"min-xid-age", required_argument, NULL, 6},
+		{"min-mxid-age", required_argument, NULL, 7},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -222,6 +226,24 @@ main(int argc, char *argv[])
 			case 5:
 				vacopts.skip_locked = true;
 				break;
+			case 6:
+				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 7:
+				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);
@@ -406,6 +428,20 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 		exit(1);
 	}
 
+	if (vacopts->min_xid_age != 0 && PQserverVersion(conn) < 70200)
+	{
+		fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 7.2\n"),
+				progname, "--min-xid-age");
+		exit(1);
+	}
+
+	if (vacopts->min_mxid_age != 0 && PQserverVersion(conn) < 90500)
+	{
+		fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 9.5\n"),
+				progname, "--min-mxid-age");
+		exit(1);
+	}
+
 	if (!quiet)
 	{
 		if (stage != ANALYZE_NO_STAGE)
@@ -516,7 +552,9 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 	appendPQExpBuffer(&catalog_query,
 					  " FROM pg_catalog.pg_class c\n"
 					  " JOIN pg_catalog.pg_namespace ns"
-					  " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n");
+					  " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
+					  " LEFT JOIN pg_catalog.pg_class t"
+					  " ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
 
 	if (columns_listed)
 		appendPQExpBuffer(&catalog_query, " LEFT JOIN column_lists"
@@ -530,6 +568,18 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 		appendPQExpBufferStr(&catalog_query, table_clause.data);
 	termPQExpBuffer(&table_clause);
 
+	if (vacopts->min_xid_age != 0)
+		appendPQExpBuffer(&catalog_query,
+						  " AND GREATEST(pg_catalog.age(c.relfrozenxid),"
+						  " pg_catalog.age(t.relfrozenxid)) OPERATOR(pg_catalog.>=) %d\n",
+						  vacopts->min_xid_age);
+
+	if (vacopts->min_mxid_age != 0)
+		appendPQExpBuffer(&catalog_query,
+						  " AND GREATEST(pg_catalog.mxid_age(c.relminmxid),"
+						  " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=) %d\n",
+						  vacopts->min_mxid_age);
+
 	/*
 	 * Execute the catalog query.  We use the default search_path for this
 	 * query for consistency with table lookups done elsewhere by the user.
@@ -1188,6 +1238,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