On Wed, Jan 30, 2019 at 05:45:58PM +0000, Bossart, Nathan wrote: > On 1/29/19, 4:47 PM, "Michael Paquier" <mich...@paquier.xyz> wrote: >> Oh, OK. This makes sense. It would be nice to add a comment in the >> patch and to document this calculation method in the docs of >> vacuumdb. > > Sure, this is added in v8.
Thanks, Nathan. Something which was not correct in the patch is the compatibility of the query. xid <> xid has been added in 9.6, so the new options will not be able to work with older versions. The versions marked as compatible in the last patch came from the age-ing functions, but you added direct comparisons with relfrozenxid and relminmxid in the latest versions of the patch. This implementation goes down a couple of released versions, which is useful enough in my opinion, so I would keep it as-is. I have added as well some markups around "PostgreSQL" in the docs, and extra casts for the integer/xid values of the query. The test patterns are also simplified, and I added tests for incorrect values of --min-xid-age and --min-mxid-age. Does that look correct to you? > Thanks. Something else I noticed is that we do not retrieve foreign > tables and partitioned tables for --analyze and --analyze-only. > However, that has long been the case for parallel mode, and this issue > should probably get its own thread. Good point, this goes down a couple of releases, and statistics on both may be useful to compile for a system-wide operation. Spawning a separate thread looks adapted to me. -- Michael
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index f304627802..41c7f3df79 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -172,6 +172,60 @@ 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
+ <productname>PostgreSQL</productname> 9.6 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
+ <productname>PostgreSQL</productname> 9.6 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..7f3a9b14a9 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 => 44;
program_help_ok('vacuumdb');
program_version_ok('vacuumdb');
@@ -95,3 +95,20 @@ $node->command_checks_all(
[qr/^.*vacuuming database "postgres"/],
[qr/^WARNING.*cannot vacuum non-tables or special system tables/s],
'vacuumdb with view');
+$node->command_fails(
+ [ 'vacuumdb', '--table', 'vactable', '--min-mxid-age', '0',
+ 'postgres'],
+ 'vacuumdb --min-mxid-age with incorrect value');
+$node->command_fails(
+ [ 'vacuumdb', '--table', 'vactable', '--min-xid-age', '0',
+ 'postgres'],
+ 'vacuumdb --min-xid-age with incorrect value');
+$node->issues_sql_like(
+ [ 'vacuumdb', '--table', 'vactable', '--min-mxid-age', '2147483000',
+ 'postgres'],
+ qr/GREATEST.*relminmxid.*2147483000/,
+ 'vacuumdb --table --min-mxid-age');
+$node->issues_sql_like(
+ [ 'vacuumdb', '--min-xid-age', '2147483001', 'postgres' ],
+ qr/GREATEST.*relfrozenxid.*2147483001/,
+ 'vacuumdb --table --min-xid-age');
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 40ba8283a2..cb503569bb 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) < 90600)
+ {
+ fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 9.6\n"),
+ progname, "--min-xid-age");
+ exit(1);
+ }
+
+ if (vacopts->min_mxid_age != 0 && PQserverVersion(conn) < 90600)
+ {
+ fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 9.6\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,43 @@ 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.
+ */
+ 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'::pg_catalog.int4\n"
+ " AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
+ " '0'::pg_catalog.xid\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'::pg_catalog.int4\n"
+ " AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
+ " '0'::pg_catalog.xid\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 +1225,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"));
signature.asc
Description: PGP signature
