DISABLE_PAGE_SKIPPING option for vacuumdb
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"));
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
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