v8-0001-Add-min-xid-age-and-min-mxid-age-options-to-vacuu.patch

From 547e4261d804fb82015dbb5c45ed3bfe54e9ca7f Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Wed, 30 Jan 2019 17:35:31 +0000
Subject: [PATCH v8 1/1] Add --min-xid-age and --min-mxid-age options to
 vacuumdb.

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

diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index f304627802..87b3349a59 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -172,6 +172,59 @@ 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>
+       <para>
+        For the purposes of this option, the multixact ID age of a relation is
+        the greatest of the ages of the main relation and its associated
+        <acronym>TOAST</acronym> table, if one exists.  Since the commands
+        issued by <application>vacuumdb</application> will also process the
+        <acronym>TOAST</acronym> table for the relation if necessary, it does
+        not need to be considered separately.
+       </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>
+       <para>
+        For the purposes of this option, the transaction ID age of a relation is
+        the greatest of the ages of the main relation and its associated
+        <acronym>TOAST</acronym> table, if one exists.  Since the commands
+        issued by <application>vacuumdb</application> will also process the
+        <acronym>TOAST</acronym> table for the relation if necessary, it does
+        not need to be considered separately.
+       </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 5e87af2d51..a560dba53f 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 => 38;
+use Test::More tests => 42;
 
 program_help_ok('vacuumdb');
 program_version_ok('vacuumdb');
@@ -95,3 +95,11 @@ $node->command_checks_all(
 	[qr/^.*vacuuming database "postgres"/],
 	[qr/^WARNING.*cannot vacuum non-tables or special system tables/s],
 	'vacuumdb with view');
+$node->issues_sql_like(
+	[ 'vacuumdb', '--table=pg_class', '--min-mxid-age=2147483000', 'postgres'],
+	qr/WHERE 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 40ba8283a2..dc1e4d3e90 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);
@@ -370,6 +392,7 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 	bool		failed = false;
 	bool		parallel = concurrentCons > 1;
 	bool		tables_listed = false;
+	bool		has_where = false;
 	const char *stage_commands[] = {
 		"SET default_statistics_target=1; SET vacuum_cost_delay=0;",
 		"SET default_statistics_target=10; RESET vacuum_cost_delay;",
@@ -403,6 +426,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)
@@ -477,7 +514,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");
 
 	/* Used to match the tables listed by the user */
 	if (tables_listed)
@@ -491,9 +530,41 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 	 * processed in which case the user will know about it.
 	 */
 	if (!tables_listed)
+	{
 		appendPQExpBuffer(&catalog_query, " WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array["
 						  CppAsString2(RELKIND_RELATION) ", "
 						  CppAsString2(RELKIND_MATVIEW) "])\n");
+		has_where = true;
+	}
+
+	/*
+	 * For --min-xid-age and --min-mxid-age, the age of the relation is the
+	 * greatest of the ages of the main relation and its associated TOAST
+	 * table.  The commands generated by vacuumdb will also process the TOAST
+	 * table for the relation if necessary, so it does not need to be
+	 * considered separately.  Note that ANALYZE is always skipped on TOAST
+	 * tables because the toaster always uses hardcoded index access and
+	 * statistics are totally unimportant for TOAST relations.
+	 */
+	if (vacopts->min_xid_age != 0)
+	{
+		appendPQExpBuffer(&catalog_query,
+						  " %s GREATEST(pg_catalog.age(c.relfrozenxid),"
+						  " pg_catalog.age(t.relfrozenxid)) OPERATOR(pg_catalog.>=) %d\n"
+						  " AND c.relfrozenxid OPERATOR(pg_catalog.!=) 0\n",
+						  has_where ? "AND" : "WHERE", vacopts->min_xid_age);
+		has_where = true;
+	}
+
+	if (vacopts->min_mxid_age != 0)
+	{
+		appendPQExpBuffer(&catalog_query,
+						  " %s GREATEST(pg_catalog.mxid_age(c.relminmxid),"
+						  " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=) %d\n"
+						  " AND c.relminmxid OPERATOR(pg_catalog.!=) 0\n",
+						  has_where ? "AND" : "WHERE", vacopts->min_mxid_age);
+		has_where = true;
+	}
 
 	/*
 	 * Execute the catalog query.  We use the default search_path for this
@@ -1152,6 +1223,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