Add support for INDEX_CLEANUP and TRUNCATE to vacuumdb

From 4a555d5a301535f01b9c9e469219bf9a071f5574 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Thu, 11 Jun 2020 16:46:26 +0000
Subject: [PATCH v2 1/1] Add --no-index-cleanup and --no-truncate to vacuumdb.

Both INDEX_CLEANUP and TRUNCATE have been available since v12.
This change adds support for disabling these options from vacuumdb.
---
 doc/src/sgml/ref/vacuumdb.sgml    | 26 ++++++++++++++++++
 src/bin/scripts/t/100_vacuumdb.pl | 16 ++++++++++-
 src/bin/scripts/vacuumdb.c        | 58 ++++++++++++++++++++++++++++++++++++++-
 3 files changed, 98 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index fd1dc140ab..68c1886950 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -226,6 +226,32 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--no-index-cleanup</option></term>
+      <listitem>
+       <para>
+        Do not remove index entries pointing to dead tuples.
+       </para>
+       <note>
+        This option is only available for servers running
+        <productname>PostgreSQL</productname> 12 and later.
+       </note>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>--no-truncate</option></term>
+      <listitem>
+       <para>
+        Do not truncate empty pages at the end of the table.
+       </para>
+       <note>
+        This option is only available for servers running
+        <productname>PostgreSQL</productname> 12 and later.
+       </note>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>-P <replaceable class="parameter">parallel_degree</replaceable></option></term>
       <term><option>--parallel=<replaceable class="parameter">parallel_degree</replaceable></option></term>
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index b136bd4457..9e36b6d2b0 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 => 49;
+use Test::More tests => 55;
 
 program_help_ok('vacuumdb');
 program_version_ok('vacuumdb');
@@ -48,6 +48,20 @@ $node->issues_sql_like(
 $node->command_fails(
 	[ 'vacuumdb', '--analyze-only', '--disable-page-skipping', 'postgres' ],
 	'--analyze-only and --disable-page-skipping specified together');
+$node->issues_sql_like(
+	[ 'vacuumdb', '--no-index-cleanup', 'postgres' ],
+	qr/statement: VACUUM \(INDEX_CLEANUP FALSE\).*;/,
+	'vacuumdb --no-index-cleanup');
+$node->command_fails(
+	[ 'vacuumdb', '--analyze-only', '--no-index-cleanup', 'postgres' ],
+	'--analyze-only and --no-index-cleanup specified together');
+$node->issues_sql_like(
+    [ 'vacuumdb', '--no-truncate', 'postgres' ],
+    qr/statement: VACUUM \(TRUNCATE FALSE\).*;/,
+    'vacuumdb --no-truncate');
+$node->command_fails(
+    [ 'vacuumdb', '--analyze-only', '--no-truncate', 'postgres' ],
+    '--analyze-only and --no-truncate specified together');
 $node->issues_sql_like(
 	[ 'vacuumdb', '-P', 2, 'postgres' ],
 	qr/statement: VACUUM \(PARALLEL 2\).*;/,
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 154084a086..6a3c941158 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -37,6 +37,8 @@ typedef struct vacuumingOptions
 	int			min_mxid_age;
 	int			parallel_workers;	/* >= 0 indicates user specified the
 									 * parallel degree, otherwise -1 */
+	bool		do_index_cleanup;
+	bool		do_truncate;
 } vacuumingOptions;
 
 
@@ -96,6 +98,8 @@ main(int argc, char *argv[])
 		{"skip-locked", no_argument, NULL, 5},
 		{"min-xid-age", required_argument, NULL, 6},
 		{"min-mxid-age", required_argument, NULL, 7},
+		{"no-index-cleanup", no_argument, NULL, 8},
+		{"no-truncate", no_argument, NULL, 9},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -117,9 +121,11 @@ main(int argc, char *argv[])
 	int			concurrentCons = 1;
 	int			tbl_count = 0;
 
-	/* initialize options to all false */
+	/* initialize options */
 	memset(&vacopts, 0, sizeof(vacopts));
 	vacopts.parallel_workers = -1;
+	vacopts.do_index_cleanup = true;
+	vacopts.do_truncate = true;
 
 	pg_logging_init(argv[0]);
 	progname = get_progname(argv[0]);
@@ -223,6 +229,12 @@ main(int argc, char *argv[])
 					exit(1);
 				}
 				break;
+			case 8:
+				vacopts.do_index_cleanup = false;
+				break;
+			case 9:
+				vacopts.do_truncate = false;
+				break;
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -267,6 +279,18 @@ main(int argc, char *argv[])
 						 "disable-page-skipping");
 			exit(1);
 		}
+		if (!vacopts.do_index_cleanup)
+		{
+			pg_log_error("cannot use the \"%s\" option when performing only analyze",
+						 "no-index-cleanup");
+			exit(1);
+		}
+		if (!vacopts.do_truncate)
+		{
+			pg_log_error("cannot use the \"%s\" option when performing only analyze",
+						 "no-truncate");
+			exit(1);
+		}
 		/* allow 'and_analyze' with 'analyze_only' */
 	}
 
@@ -412,6 +436,22 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 		exit(1);
 	}
 
+	if (!vacopts->do_index_cleanup && PQserverVersion(conn) < 120000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "no-index-cleanup", "12");
+		exit(1);
+	}
+
+	if (!vacopts->do_truncate && PQserverVersion(conn) < 120000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "no-truncate", "12");
+		exit(1);
+	}
+
 	if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
 	{
 		PQfinish(conn);
@@ -832,6 +872,20 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
 				appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
 				sep = comma;
 			}
+			if (!vacopts->do_index_cleanup)
+			{
+				/* INDEX_CLEANUP is supported since v12 */
+				Assert(serverVersion >= 120000);
+				appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
+				sep = comma;
+			}
+			if (!vacopts->do_truncate)
+			{
+				/* TRUNCATE is supported since v12 */
+				Assert(serverVersion >= 120000);
+				appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
+				sep = comma;
+			}
 			if (vacopts->skip_locked)
 			{
 				/* SKIP_LOCKED is supported since v12 */
@@ -930,6 +984,8 @@ help(const char *progname)
 	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(_("      --no-index-cleanup          don't remove index entries that point to dead tuples\n"));
+	printf(_("      --no-truncate               don't truncate empty pages at the end of the table\n"));
 	printf(_("  -P, --parallel=PARALLEL_DEGREE  use this many background workers for vacuum, if available\n"));
 	printf(_("  -q, --quiet                     don't write any messages\n"));
 	printf(_("      --skip-locked               skip relations that cannot be immediately locked\n"));
-- 
2.16.6

From bb2eb9ddae447d84abbc3fee32f2cd2c2ef00763 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Thu, 18 Jun 2020 21:20:12 +0000
Subject: [PATCH v3 1/1] Add --no-index-cleanup and --no-truncate to vacuumdb.

Both INDEX_CLEANUP and TRUNCATE have been available since v12.
This change adds support for disabling these options from vacuumdb.
---
 doc/src/sgml/ref/vacuumdb.sgml    | 30 ++++++++++++++++++++
 src/bin/scripts/t/100_vacuumdb.pl | 16 ++++++++++-
 src/bin/scripts/vacuumdb.c        | 58 ++++++++++++++++++++++++++++++++++++++-
 3 files changed, 102 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index fd1dc140ab..95d6894cb0 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -226,6 +226,36 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--no-index-cleanup</option></term>
+      <listitem>
+       <para>
+        Do not remove index entries pointing to dead tuples.
+       </para>
+       <note>
+        <para>
+         This option is only available for servers running
+         <productname>PostgreSQL</productname> 12 and later.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>--no-truncate</option></term>
+      <listitem>
+       <para>
+        Do not truncate empty pages at the end of the table.
+       </para>
+       <note>
+        <para>
+         This option is only available for servers running
+         <productname>PostgreSQL</productname> 12 and later.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>-P <replaceable class="parameter">parallel_degree</replaceable></option></term>
       <term><option>--parallel=<replaceable class="parameter">parallel_degree</replaceable></option></term>
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index b136bd4457..9e36b6d2b0 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 => 49;
+use Test::More tests => 55;
 
 program_help_ok('vacuumdb');
 program_version_ok('vacuumdb');
@@ -48,6 +48,20 @@ $node->issues_sql_like(
 $node->command_fails(
 	[ 'vacuumdb', '--analyze-only', '--disable-page-skipping', 'postgres' ],
 	'--analyze-only and --disable-page-skipping specified together');
+$node->issues_sql_like(
+	[ 'vacuumdb', '--no-index-cleanup', 'postgres' ],
+	qr/statement: VACUUM \(INDEX_CLEANUP FALSE\).*;/,
+	'vacuumdb --no-index-cleanup');
+$node->command_fails(
+	[ 'vacuumdb', '--analyze-only', '--no-index-cleanup', 'postgres' ],
+	'--analyze-only and --no-index-cleanup specified together');
+$node->issues_sql_like(
+    [ 'vacuumdb', '--no-truncate', 'postgres' ],
+    qr/statement: VACUUM \(TRUNCATE FALSE\).*;/,
+    'vacuumdb --no-truncate');
+$node->command_fails(
+    [ 'vacuumdb', '--analyze-only', '--no-truncate', 'postgres' ],
+    '--analyze-only and --no-truncate specified together');
 $node->issues_sql_like(
 	[ 'vacuumdb', '-P', 2, 'postgres' ],
 	qr/statement: VACUUM \(PARALLEL 2\).*;/,
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 154084a086..6a3c941158 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -37,6 +37,8 @@ typedef struct vacuumingOptions
 	int			min_mxid_age;
 	int			parallel_workers;	/* >= 0 indicates user specified the
 									 * parallel degree, otherwise -1 */
+	bool		do_index_cleanup;
+	bool		do_truncate;
 } vacuumingOptions;
 
 
@@ -96,6 +98,8 @@ main(int argc, char *argv[])
 		{"skip-locked", no_argument, NULL, 5},
 		{"min-xid-age", required_argument, NULL, 6},
 		{"min-mxid-age", required_argument, NULL, 7},
+		{"no-index-cleanup", no_argument, NULL, 8},
+		{"no-truncate", no_argument, NULL, 9},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -117,9 +121,11 @@ main(int argc, char *argv[])
 	int			concurrentCons = 1;
 	int			tbl_count = 0;
 
-	/* initialize options to all false */
+	/* initialize options */
 	memset(&vacopts, 0, sizeof(vacopts));
 	vacopts.parallel_workers = -1;
+	vacopts.do_index_cleanup = true;
+	vacopts.do_truncate = true;
 
 	pg_logging_init(argv[0]);
 	progname = get_progname(argv[0]);
@@ -223,6 +229,12 @@ main(int argc, char *argv[])
 					exit(1);
 				}
 				break;
+			case 8:
+				vacopts.do_index_cleanup = false;
+				break;
+			case 9:
+				vacopts.do_truncate = false;
+				break;
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -267,6 +279,18 @@ main(int argc, char *argv[])
 						 "disable-page-skipping");
 			exit(1);
 		}
+		if (!vacopts.do_index_cleanup)
+		{
+			pg_log_error("cannot use the \"%s\" option when performing only analyze",
+						 "no-index-cleanup");
+			exit(1);
+		}
+		if (!vacopts.do_truncate)
+		{
+			pg_log_error("cannot use the \"%s\" option when performing only analyze",
+						 "no-truncate");
+			exit(1);
+		}
 		/* allow 'and_analyze' with 'analyze_only' */
 	}
 
@@ -412,6 +436,22 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 		exit(1);
 	}
 
+	if (!vacopts->do_index_cleanup && PQserverVersion(conn) < 120000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "no-index-cleanup", "12");
+		exit(1);
+	}
+
+	if (!vacopts->do_truncate && PQserverVersion(conn) < 120000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "no-truncate", "12");
+		exit(1);
+	}
+
 	if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
 	{
 		PQfinish(conn);
@@ -832,6 +872,20 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
 				appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
 				sep = comma;
 			}
+			if (!vacopts->do_index_cleanup)
+			{
+				/* INDEX_CLEANUP is supported since v12 */
+				Assert(serverVersion >= 120000);
+				appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
+				sep = comma;
+			}
+			if (!vacopts->do_truncate)
+			{
+				/* TRUNCATE is supported since v12 */
+				Assert(serverVersion >= 120000);
+				appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
+				sep = comma;
+			}
 			if (vacopts->skip_locked)
 			{
 				/* SKIP_LOCKED is supported since v12 */
@@ -930,6 +984,8 @@ help(const char *progname)
 	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(_("      --no-index-cleanup          don't remove index entries that point to dead tuples\n"));
+	printf(_("      --no-truncate               don't truncate empty pages at the end of the table\n"));
 	printf(_("  -P, --parallel=PARALLEL_DEGREE  use this many background workers for vacuum, if available\n"));
 	printf(_("  -q, --quiet                     don't write any messages\n"));
 	printf(_("      --skip-locked               skip relations that cannot be immediately locked\n"));
-- 
2.16.6