DISABLE_PAGE_SKIPPING option for vacuumdb

Started by Masahiko Sawadaover 9 years ago3 messages
#1Masahiko Sawada
sawada.mshk@gmail.com
1 attachment(s)

Hi,

Attached patch add --disable-page-skipping option to vacuumed command for 9.6.
If by any chance the freeze map causes the serious data corruption bug
then the user will want to run pg_check_visible() and vacuum with
DISABLE_PAGE_SKIPPING option to the multiple tables having problem.
In this case, I think that this option for vacuumdb would be convenient.

Please give me feedback.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Attachments:

disable_page_skipping_option_for_vacuumdb_v1.patchapplication/octet-stream; name=disable_page_skipping_option_for_vacuumdb_v1.patchDownload
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 92b8984..5ae2473 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -248,6 +248,15 @@ PostgreSQL documentation
      </varlistentry>
 
      <varlistentry>
+      <term><option>--disable-page-skipping</option></term>
+      <listitem>
+       <para>
+        Disable all page-skipping behavior while vacuum.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
        <term><option>-?</></term>
        <term><option>--help</></term>
        <listitem>
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index c183ccb..7f279fd 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 => 18;
+use Test::More tests => 20;
 
 program_help_ok('vacuumdb');
 program_version_ok('vacuumdb');
@@ -30,6 +30,10 @@ $node->issues_sql_like(
 	qr/statement: VACUUM \(ANALYZE\);/,
 	'vacuumdb -z');
 $node->issues_sql_like(
+	[ 'vacuumdb', '--disable-page-skipping', 'postgres' ],
+	qr/statement: VACUUM \(DISABLE_PAGE_SKIPPING\);/,
+	'vacuumdb --disable-page-skipping');
+$node->issues_sql_like(
 	[ 'vacuumdb', '-Z', 'postgres' ],
 	qr/statement: ANALYZE;/,
 	'vacuumdb -Z');
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index c10b58b..1cb81e0 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -35,6 +35,7 @@ typedef struct vacuumingOptions
 	bool		and_analyze;
 	bool		full;
 	bool		freeze;
+	bool		disable_page_skipping;
 } vacuumingOptions;
 
 
@@ -100,6 +101,7 @@ main(int argc, char *argv[])
 		{"jobs", required_argument, NULL, 'j'},
 		{"maintenance-db", required_argument, NULL, 2},
 		{"analyze-in-stages", no_argument, NULL, 3},
+		{"disable-page-skipping", no_argument, NULL, 4},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -116,6 +118,7 @@ main(int argc, char *argv[])
 	bool		quiet = false;
 	vacuumingOptions vacopts;
 	bool		analyze_in_stages = false;
+	bool		disable_page_skipping = false;
 	bool		alldb = false;
 	SimpleStringList tables = {NULL, NULL};
 	int			concurrentCons = 1;
@@ -203,6 +206,9 @@ main(int argc, char *argv[])
 			case 3:
 				analyze_in_stages = vacopts.analyze_only = true;
 				break;
+			case 4:
+				disable_page_skipping = vacopts.disable_page_skipping = true;
+				break;
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -241,9 +247,22 @@ main(int argc, char *argv[])
 					progname, "freeze");
 			exit(1);
 		}
+		if (vacopts.disable_page_skipping)
+		{
+			fprintf(stderr, _("%s: cannot use the \"%s\" option when performing only analyze\n"),
+					progname, "disable-page-skipping");
+			exit(1);
+		}
 		/* allow 'and_analyze' with 'analyze_only' */
 	}
 
+	if (vacopts.full && vacopts.disable_page_skipping)
+	{
+		fprintf(stderr, _("%s: connot use the \"%s\" option and \"%s\" option at the same time\n"),
+				progname, "full", "disable-page-skipping");
+		exit(1);
+	}
+
 	setup_cancel_handler();
 
 	/* Avoid opening extra connections. */
@@ -648,6 +667,11 @@ prepare_vacuum_command(PQExpBuffer sql, PGconn *conn, vacuumingOptions *vacopts,
 				appendPQExpBuffer(sql, "%sANALYZE", sep);
 				sep = comma;
 			}
+			if (vacopts->disable_page_skipping)
+			{
+				appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
+				sep = comma;
+			}
 			if (sep != paren)
 				appendPQExpBufferChar(sql, ')');
 		}
@@ -956,6 +980,7 @@ help(const char *progname)
 	printf(_("  -Z, --analyze-only              only update optimizer statistics; no vacuum\n"));
 	printf(_("      --analyze-in-stages         only update optimizer statistics, in multiple\n"
 			 "                                  stages for faster results; no vacuum\n"));
+	printf(_("      --disable-page-skipping     disable all page-skipping behaviour while vacuum\n"));
 	printf(_("  -?, --help                      show this help, then exit\n"));
 	printf(_("\nConnection options:\n"));
 	printf(_("  -h, --host=HOSTNAME       database server host or socket directory\n"));
#2Robert Haas
robertmhaas@gmail.com
In reply to: Masahiko Sawada (#1)
Re: DISABLE_PAGE_SKIPPING option for vacuumdb

On Thu, Sep 8, 2016 at 1:32 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote:

Attached patch add --disable-page-skipping option to vacuumed command for 9.6.
If by any chance the freeze map causes the serious data corruption bug
then the user will want to run pg_check_visible() and vacuum with
DISABLE_PAGE_SKIPPING option to the multiple tables having problem.
In this case, I think that this option for vacuumdb would be convenient.

Please give me feedback.

I think this isn't really necessary.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#3Masahiko Sawada
sawada.mshk@gmail.com
In reply to: Robert Haas (#2)
Re: DISABLE_PAGE_SKIPPING option for vacuumdb

On Wed, Sep 14, 2016 at 2:17 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Sep 8, 2016 at 1:32 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote:

Attached patch add --disable-page-skipping option to vacuumed command for 9.6.
If by any chance the freeze map causes the serious data corruption bug
then the user will want to run pg_check_visible() and vacuum with
DISABLE_PAGE_SKIPPING option to the multiple tables having problem.
In this case, I think that this option for vacuumdb would be convenient.

Please give me feedback.

I think this isn't really necessary.

Thank you for comment.

Okay, I thought it would be useful for user who want to vacuum with
disable_page_skipping specified table but it's non-essential.
I pull it back. Thanks.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers