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

From 3f59244bb34d2a7869ea000d380d2f2abb28842e Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Sat, 19 Jan 2019 01:20:49 +0000
Subject: [PATCH v3 3/3] 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 | 11 ++++++++-
 src/bin/scripts/vacuumdb.c        | 50 +++++++++++++++++++++++++++++++++++++++
 3 files changed, 97 insertions(+), 1 deletion(-)

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 3ab54c90e9..d5f3f66f5a 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 => 34;
 
 program_help_ok('vacuumdb');
 program_version_ok('vacuumdb');
@@ -75,3 +75,12 @@ $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->issues_sql_like(
+	[ 'vacuumdb', '--table=pg_class', '--min-mxid-age=123456789', 'postgres'],
+	qr/AND GREATEST\(pg_catalog.mxid_age\(c.relminmxid\), pg_catalog.mxid_age\(t.relminmxid\)\) OPERATOR\(pg_catalog.>=\) 123456789/,
+	'vacuumdb --table --min-mxid-age');
+$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');
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 490d742125..7283ced948 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);
@@ -403,6 +425,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)
@@ -432,6 +468,18 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 					  CppAsString2(RELKIND_RELATION) ", "
 					  CppAsString2(RELKIND_MATVIEW) ")\n");
 
+	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);
+
 	cell = tables ? tables->head : NULL;
 	while (cell != NULL)
 	{
@@ -1103,6 +1151,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