From: Bossart, Nathan <bossartn@amazon.com>
To: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: A few new options for vacuumdb
Date: Wed, 19 Dec 2018 20:50:10 +0000
Message-ID: <FFE5373C-E26A-495B-B5C8-911EC4A41C5E@amazon.com> (raw)
Hi hackers,
I've attached a few patches to add some options to vacuumdb that might
be useful. Specifically, these patches add the --skip-locked,
--min-xid-age, --min-mxid-age, and --min-relation-size options.
If an option is specified for a server version that is not supported,
the option is silently ignored. For example, SKIP_LOCKED was only
added to VACUUM and ANALYZE for v12. Alternatively, I think we could
fail in vacuum_one_database() if an unsupported option is specified.
Some of these options will work on all currently supported versions,
so I am curious what others think about skipping some of these version
checks altogether.
In this set of patches, I've disallowed using --min-xid-age,
--min-mxid-age, and --min-relation-size in conjunction with the
--table option. IMO this combination of options is prone to
confusion. For example:
vacuumdb mydb --table mytable --min-relation-size 1024
It does not seem clear whether the user wants us to process mytable
only if it is at least 1 GB, or if we should process mytable in
addition to any other relations over 1 GB. Either way, I think trying
to support these combinations of options adds more complexity than it
is worth.
0001 is a minor fix that is somewhat separate from these new options,
although the new options will make the edge case it aims to fix much
easier to reach. When the catalogs are queried in parallel mode to
get the list of tables to process, we currently assume that at least
one table will be returned. If no tables are found, the tables
variable will stay as NULL, which leads to database-wide VACUUM or
ANALYZE commands. Since there are currently no user-configurable
options available for this catalog query, this case is likely
exceptionally rare. However, with the new options, it is much easier
to inadvertently filter out all relations.
I will be adding this work to the next commitfest.
Nathan
Attachments:
[application/octet-stream] v1-0002-Add-skip-locked-option-to-vacuumdb.patch (4.3K, ../FFE5373C-E26A-495B-B5C8-911EC4A41C5E@amazon.com/2-v1-0002-Add-skip-locked-option-to-vacuumdb.patch)
download | inline diff:
From 180ff0bcc0304d093d2e1fc40886b4a0ff4b5d56 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Wed, 19 Dec 2018 01:18:02 +0000
Subject: [PATCH v1 2/4] Add --skip-locked option to vacuumdb.
---
doc/src/sgml/ref/vacuumdb.sgml | 16 ++++++++++++++++
src/bin/scripts/t/100_vacuumdb.pl | 6 +++++-
src/bin/scripts/vacuumdb.c | 17 +++++++++++++++++
3 files changed, 38 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgmlindex 955a17a849..942c8ba874 100644--- a/doc/src/sgml/ref/vacuumdb.sgml+++ b/doc/src/sgml/ref/vacuumdb.sgml@@ -167,6 +167,22 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>+ <term><option>--skip-locked</option></term>+ <listitem>+ <para>+ Skip relations that cannot be immediately locked for processing.+ </para>+ <note>+ <para>+ This option is available for servers running PostgreSQL 12 and later.+ If specified for a server running an older version of PostgreSQL, this+ option is silently ignored.+ </para>+ </note>+ </listitem>+ </varlistentry>+
<varlistentry>
<term><option>-t <replaceable class="parameter">table</replaceable> [ (<replaceable class="parameter">column</replaceable> [,...]) ]</option></term>
<term><option>--table=<replaceable class="parameter">table</replaceable> [ (<replaceable class="parameter">column</replaceable> [,...]) ]</option></term>
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.plindex 4c477a27aa..239ed76834 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 => 23;+use Test::More tests => 25;
program_help_ok('vacuumdb');
program_version_ok('vacuumdb');
@@ -33,6 +33,10 @@ $node->issues_sql_like(
[ 'vacuumdb', '-Z', 'postgres' ],
qr/statement: ANALYZE;/,
'vacuumdb -Z');
+$node->issues_sql_like(+ [ 'vacuumdb', '--skip-locked', 'postgres' ],+ qr/statement: VACUUM \(SKIP_LOCKED\);/,+ 'vacuumdb --skip-locked');
$node->command_ok([qw(vacuumdb -Z --table=pg_am dbname=template1)],
'vacuumdb with connection string');
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.cindex 886895ed2f..5aa2833a73 100644--- a/src/bin/scripts/vacuumdb.c+++ b/src/bin/scripts/vacuumdb.c@@ -40,6 +40,7 @@ typedef struct vacuumingOptions
bool and_analyze;
bool full;
bool freeze;
+ bool skip_locked;
} vacuumingOptions;
@@ -110,6 +111,7 @@ main(int argc, char *argv[])
{"jobs", required_argument, NULL, 'j'},
{"maintenance-db", required_argument, NULL, 2},
{"analyze-in-stages", no_argument, NULL, 3},
+ {"skip-locked", no_argument, NULL, 4},
{NULL, 0, NULL, 0}
};
@@ -213,6 +215,9 @@ main(int argc, char *argv[])
case 3:
analyze_in_stages = vacopts.analyze_only = true;
break;
+ case 4:+ vacopts.skip_locked = true;+ break;
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -673,6 +678,17 @@ prepare_vacuum_command(PQExpBuffer sql, PGconn *conn,
appendPQExpBuffer(sql, "%sANALYZE", sep);
sep = comma;
}
++ /*+ * The SKIP_LOCKED option was added for VACUUM and ANALYZE in v12.+ * Silently ignore it if specified for an older version.+ */+ if (vacopts->skip_locked && PQserverVersion(conn) >= 120000)+ {+ appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);+ sep = comma;+ }+
if (sep != paren)
appendPQExpBufferChar(sql, ')');
}
@@ -1011,6 +1027,7 @@ help(const char *progname)
printf(_(" -F, --freeze freeze row transaction information\n"));
printf(_(" -j, --jobs=NUM use this many concurrent connections 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"));
printf(_(" -v, --verbose write a lot of output\n"));
printf(_(" -V, --version output version information, then exit\n"));
--
2.16.5
[application/octet-stream] v1-0003-Add-min-xid-age-and-min-mxid-age-options-to-vacuu.patch (9.0K, ../FFE5373C-E26A-495B-B5C8-911EC4A41C5E@amazon.com/3-v1-0003-Add-min-xid-age-and-min-mxid-age-options-to-vacuu.patch)
download | inline diff:
From 066639fbd1673ad5044ae50ad50af6ff24cd93a3 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Wed, 19 Dec 2018 01:18:50 +0000
Subject: [PATCH v1 3/4] Add --min-xid-age and --min-mxid-age options to
vacuumdb.
---
doc/src/sgml/ref/vacuumdb.sgml | 42 ++++++++++++++++++
src/bin/scripts/t/100_vacuumdb.pl | 13 +++++-
src/bin/scripts/vacuumdb.c | 90 +++++++++++++++++++++++++++++++++------
3 files changed, 130 insertions(+), 15 deletions(-)
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgmlindex 942c8ba874..94e8aac268 100644--- a/doc/src/sgml/ref/vacuumdb.sgml+++ b/doc/src/sgml/ref/vacuumdb.sgml@@ -157,6 +157,48 @@ 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>.+ </para>+ <para>+ Note that this option cannot be used in conjunction with the+ <option>--table</option> option.+ </para>+ <note>+ <para>+ This option is available for servers running PostgreSQL 9.5 and later.+ If specified on a server running an older version of PostgreSQL, this+ option is silently ignored.+ </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>.+ </para>+ <para>+ Note that this option cannot be used in conjunction with the+ <option>--table</option> option.+ </para>+ <note>+ <para>+ This option is available for servers running PostgreSQL 7.2 and later.+ If specified on a server running an older version of PostgreSQL, this+ option is silently ignored.+ </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.plindex 239ed76834..45f79b244e 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 => 25;+use Test::More tests => 30;
program_help_ok('vacuumdb');
program_version_ok('vacuumdb');
@@ -64,3 +64,14 @@ $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->command_fails(+ [qw|vacuumdb -Zt funcidx --min-mxid-age 1000000000|],+ 'specified table with --min-mxid-age option');+$node->issues_sql_like(+ [ 'vacuumdb', '--min-xid-age=123456789', 'postgres' ],+ qr/AND greatest\(age\(c.relfrozenxid\), age\(t.relfrozenxid\)\) >= 123456789/,+ 'vacuumdb --min-xid-age');+$node->issues_sql_like(+ [ 'vacuumdb', '--min-mxid-age=123456789', 'postgres' ],+ qr/AND greatest\(mxid_age\(c.relminmxid\), mxid_age\(t.relminmxid\)\) >= 123456789/,+ 'vacuumdb --min-mxid-age');diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.cindex 5aa2833a73..a2622fab24 100644--- a/src/bin/scripts/vacuumdb.c+++ b/src/bin/scripts/vacuumdb.c@@ -41,6 +41,8 @@ typedef struct vacuumingOptions
bool full;
bool freeze;
bool skip_locked;
+ int min_xid_age;+ int min_mxid_age;
} vacuumingOptions;
@@ -112,6 +114,8 @@ main(int argc, char *argv[])
{"maintenance-db", required_argument, NULL, 2},
{"analyze-in-stages", no_argument, NULL, 3},
{"skip-locked", no_argument, NULL, 4},
+ {"min-xid-age", required_argument, NULL, 5},+ {"min-mxid-age", required_argument, NULL, 6},
{NULL, 0, NULL, 0}
};
@@ -218,6 +222,24 @@ main(int argc, char *argv[])
case 4:
vacopts.skip_locked = true;
break;
+ case 5:+ 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 6:+ 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);
@@ -259,6 +281,13 @@ main(int argc, char *argv[])
/* allow 'and_analyze' with 'analyze_only' */
}
+ if (tables.head != NULL && (vacopts.min_xid_age != 0 || vacopts.min_mxid_age != 0))+ {+ fprintf(stderr, _("%s: cannot vacuum specific tables in conjunction with \"%s\" or \"%s\"\n"),+ progname, "--min-xid-age", "--min-mxid-age");+ exit(1);+ }+
setup_cancel_handler();
/* Avoid opening extra connections. */
@@ -355,6 +384,8 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
int i;
bool failed = false;
bool parallel = concurrentCons > 1;
+ bool filter_by_xid_age;+ bool filter_by_mxid_age;
const char *stage_commands[] = {
"SET default_statistics_target=1; SET vacuum_cost_delay=0;",
"SET default_statistics_target=10; RESET vacuum_cost_delay;",
@@ -383,25 +414,51 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
fflush(stdout);
}
+ /*+ * Before 7.2, age() did not exist. Before 9.5, mxid_age() did not exist.+ */+ filter_by_xid_age = vacopts->min_xid_age != 0 && PQserverVersion(conn) >= 70200;+ filter_by_mxid_age = vacopts->min_mxid_age != 0 && PQserverVersion(conn) >= 90500;+
/*
* If a table list is not provided and we're using multiple connections,
* prepare the list of tables by querying the catalogs.
+ *+ * If a table list is not provided and we're using one connection, prepare+ * the list of tables by querying the catalogs only if --min-xid-age or+ * --min-mxid-age is specified.
*/
- if (parallel && (!tables || !tables->head))+ if ((parallel && (!tables || !tables->head)) ||+ (!parallel && (filter_by_xid_age || filter_by_mxid_age)))
{
PQExpBufferData buf;
+ PQExpBufferData catalog_query;
PGresult *res;
int ntups;
- res = executeQuery(conn,- "SELECT c.relname, ns.nspname"- " FROM pg_class c, pg_namespace ns\n"- " WHERE relkind IN ("- CppAsString2(RELKIND_RELATION) ", "- CppAsString2(RELKIND_MATVIEW) ")"- " AND c.relnamespace = ns.oid\n"- " ORDER BY c.relpages DESC;",- progname, echo);+ initPQExpBuffer(&catalog_query);+ appendPQExpBuffer(&catalog_query,+ "SELECT c.relname, ns.nspname"+ " FROM pg_class c\n"+ " JOIN pg_namespace ns ON c.relnamespace = ns.oid\n"+ " LEFT JOIN pg_class t ON c.reltoastrelid = t.oid\n"+ " WHERE c.relkind IN ("+ CppAsString2(RELKIND_RELATION) ", "+ CppAsString2(RELKIND_MATVIEW) ")\n");++ if (filter_by_xid_age)+ appendPQExpBuffer(&catalog_query,+ " AND greatest(age(c.relfrozenxid), age(t.relfrozenxid)) >= %d\n",+ vacopts->min_xid_age);++ if (filter_by_mxid_age)+ appendPQExpBuffer(&catalog_query,+ " AND greatest(mxid_age(c.relminmxid), mxid_age(t.relminmxid)) >= %d\n",+ vacopts->min_mxid_age);++ appendPQExpBuffer(&catalog_query, " ORDER BY c.relpages DESC;");+ res = executeQuery(conn, catalog_query.data, progname, echo);+ termPQExpBuffer(&catalog_query);
ntups = PQntuples(res);
if (ntups == 0)
@@ -429,10 +486,13 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
* If there are more connections than vacuumable relations, we don't
* need to use them all.
*/
- if (concurrentCons > ntups)- concurrentCons = ntups;- if (concurrentCons <= 1)- parallel = false;+ if (parallel)+ {+ if (concurrentCons > ntups)+ concurrentCons = ntups;+ if (concurrentCons <= 1)+ parallel = false;+ }
PQclear(res);
}
@@ -1026,6 +1086,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
[application/octet-stream] v1-0004-Add-min-relation-size-option-to-vacuumdb.patch (6.7K, ../FFE5373C-E26A-495B-B5C8-911EC4A41C5E@amazon.com/4-v1-0004-Add-min-relation-size-option-to-vacuumdb.patch)
download | inline diff:
From 8663a1a0c40142764d759bf375f3f7192d7c16e2 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Wed, 19 Dec 2018 20:15:39 +0000
Subject: [PATCH v1 4/4] Add --min-relation-size option to vacuumdb.
---
doc/src/sgml/ref/vacuumdb.sgml | 21 +++++++++++++++++++++
src/bin/scripts/t/100_vacuumdb.pl | 9 ++++++++-
src/bin/scripts/vacuumdb.c | 33 ++++++++++++++++++++++++++++++---
3 files changed, 59 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgmlindex 94e8aac268..0fbccf310e 100644--- a/doc/src/sgml/ref/vacuumdb.sgml+++ b/doc/src/sgml/ref/vacuumdb.sgml@@ -178,6 +178,27 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>+ <term><option>--min-relation-size <replaceable class="parameter">size</replaceable></option></term>+ <listitem>+ <para>+ Only execute the vacuum or analyze commands on tables with a total size+ of at least <replaceable class="parameter">size</replaceable> megabytes.+ </para>+ <para>+ Note that this option cannot be used in conjunction with the+ <option>--table</option> option.+ </para>+ <note>+ <para>+ This option is available for servers running PostgreSQL 8.1 and later.+ If specified on a server running an older version of PostgreSQL, this+ option is silently ignored.+ </para>+ </note>+ </listitem>+ </varlistentry>+
<varlistentry>
<term><option>--min-xid-age <replaceable class="parameter">xid_age</replaceable></option></term>
<listitem>
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.plindex 45f79b244e..982195817e 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 => 33;
program_help_ok('vacuumdb');
program_version_ok('vacuumdb');
@@ -75,3 +75,10 @@ $node->issues_sql_like(
[ 'vacuumdb', '--min-mxid-age=123456789', 'postgres' ],
qr/AND greatest\(mxid_age\(c.relminmxid\), mxid_age\(t.relminmxid\)\) >= 123456789/,
'vacuumdb --min-mxid-age');
+$node->command_fails(+ [qw|vacuumdb -Zt funcidx --min-relation-size 8192|],+ 'specified table with --min-relation-size option');+$node->issues_sql_like(+ [ 'vacuumdb', '--min-relation-size=8192', 'postgres' ],+ qr/AND pg_total_relation_size\(c.oid\) >= 8589934592/,+ 'vacuumdb --min-relation-size');diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.cindex a2622fab24..243126bbeb 100644--- a/src/bin/scripts/vacuumdb.c+++ b/src/bin/scripts/vacuumdb.c@@ -43,6 +43,7 @@ typedef struct vacuumingOptions
bool skip_locked;
int min_xid_age;
int min_mxid_age;
+ long min_rel_size_bytes;
} vacuumingOptions;
@@ -116,6 +117,7 @@ main(int argc, char *argv[])
{"skip-locked", no_argument, NULL, 4},
{"min-xid-age", required_argument, NULL, 5},
{"min-mxid-age", required_argument, NULL, 6},
+ {"min-relation-size", required_argument, NULL, 7},
{NULL, 0, NULL, 0}
};
@@ -240,6 +242,15 @@ main(int argc, char *argv[])
exit(1);
}
break;
+ case 7:+ vacopts.min_rel_size_bytes = (long) atoi(optarg) * 1024L * 1024L;+ if (vacopts.min_rel_size_bytes <= 0)+ {+ fprintf(stderr, _("%s: minimum relation size must be at least 1\n"),+ progname);+ exit(1);+ }+ break;
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -288,6 +299,13 @@ main(int argc, char *argv[])
exit(1);
}
+ if (tables.head != NULL && vacopts.min_rel_size_bytes != 0)+ {+ fprintf(stderr, _("%s: cannot vacuum specific tables in conjunction with \"%s\"\n"),+ progname, "--min-relation-size");+ exit(1);+ }+
setup_cancel_handler();
/* Avoid opening extra connections. */
@@ -386,6 +404,7 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
bool parallel = concurrentCons > 1;
bool filter_by_xid_age;
bool filter_by_mxid_age;
+ bool filter_by_rel_size;
const char *stage_commands[] = {
"SET default_statistics_target=1; SET vacuum_cost_delay=0;",
"SET default_statistics_target=10; RESET vacuum_cost_delay;",
@@ -416,20 +435,22 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
/*
* Before 7.2, age() did not exist. Before 9.5, mxid_age() did not exist.
+ * Before 8.1, pg_total_relation_size() did not exist.
*/
filter_by_xid_age = vacopts->min_xid_age != 0 && PQserverVersion(conn) >= 70200;
filter_by_mxid_age = vacopts->min_mxid_age != 0 && PQserverVersion(conn) >= 90500;
+ filter_by_rel_size = vacopts->min_rel_size_bytes != 0 && PQserverVersion(conn) >= 80100;
/*
* If a table list is not provided and we're using multiple connections,
* prepare the list of tables by querying the catalogs.
*
* If a table list is not provided and we're using one connection, prepare
- * the list of tables by querying the catalogs only if --min-xid-age or- * --min-mxid-age is specified.+ * the list of tables by querying the catalogs only if --min-xid-age,+ * --min-mxid-age, or --min-relation-size is specified.
*/
if ((parallel && (!tables || !tables->head)) ||
- (!parallel && (filter_by_xid_age || filter_by_mxid_age)))+ (!parallel && (filter_by_xid_age || filter_by_mxid_age || filter_by_rel_size)))
{
PQExpBufferData buf;
PQExpBufferData catalog_query;
@@ -456,6 +477,11 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
" AND greatest(mxid_age(c.relminmxid), mxid_age(t.relminmxid)) >= %d\n",
vacopts->min_mxid_age);
+ if (filter_by_rel_size)+ appendPQExpBuffer(&catalog_query,+ " AND pg_total_relation_size(c.oid) >= %ld\n",+ vacopts->min_rel_size_bytes);+
appendPQExpBuffer(&catalog_query, " ORDER BY c.relpages DESC;");
res = executeQuery(conn, catalog_query.data, progname, echo);
termPQExpBuffer(&catalog_query);
@@ -1087,6 +1113,7 @@ help(const char *progname)
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-relation-size=SIZE miminum size of tables to vacuum, in megabytes\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"));
--
2.16.5
[application/octet-stream] v1-0001-Do-not-process-any-relations-if-the-catalog-query.patch (1.6K, ../FFE5373C-E26A-495B-B5C8-911EC4A41C5E@amazon.com/5-v1-0001-Do-not-process-any-relations-if-the-catalog-query.patch)
download | inline diff:
From bdc89983d9302ab47b94925ac9fe1f5888aeaaa1 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Wed, 19 Dec 2018 01:17:25 +0000
Subject: [PATCH v1 1/4] Do not process any relations if the catalog query
returns no results.
---
src/bin/scripts/vacuumdb.c | 14 ++++++++++----
1 file changed, 10 insertions(+), 4 deletions(-)
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.cindex bcea9e556d..886895ed2f 100644--- a/src/bin/scripts/vacuumdb.c+++ b/src/bin/scripts/vacuumdb.c@@ -378,8 +378,6 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
fflush(stdout);
}
- initPQExpBuffer(&sql);-
/*
* If a table list is not provided and we're using multiple connections,
* prepare the list of tables by querying the catalogs.
@@ -390,8 +388,6 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
PGresult *res;
int ntups;
- initPQExpBuffer(&buf);-
res = executeQuery(conn,
"SELECT c.relname, ns.nspname"
" FROM pg_class c, pg_namespace ns\n"
@@ -403,6 +399,14 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
progname, echo);
ntups = PQntuples(res);
+ if (ntups == 0)+ {+ PQclear(res);+ PQfinish(conn);+ return;+ }++ initPQExpBuffer(&buf);
for (i = 0; i < ntups; i++)
{
appendPQExpBufferStr(&buf,
@@ -461,6 +465,8 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
stage_commands[stage], progname, echo);
}
+ initPQExpBuffer(&sql);+
cell = tables ? tables->head : NULL;
do
{
--
2.16.5
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: bossartn@amazon.com
Subject: Re: A few new options for vacuumdb
In-Reply-To: <FFE5373C-E26A-495B-B5C8-911EC4A41C5E@amazon.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox