From: Bossart, Nathan <bossartn@amazon.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: A few new options for vacuumdb
Date: Tue, 22 Jan 2019 23:21:32 +0000
Message-ID: <F716EB06-0CBE-4342-9CFA-23409319B52A@amazon.com> (raw)
In-Reply-To: <20190122060730.GD8719@paquier.xyz>
References: <33F59F9F-78A6-4ECE-BFE0-B1E4AAF40094@amazon.com>
<579F2B3A-4EA5-4032-AAD9-4AEE9219A4AB@amazon.com>
<CAD21AoDHsFx_Rnp-AB2UT2ORc3_PmCx18-wfrA_38i038D5WZQ@mail.gmail.com>
<20190108020300.GH22498@paquier.xyz>
<FF67D651-A7AF-4E70-9401-805E3CFB4DC9@amazon.com>
<20190109010637.GC21835@paquier.xyz>
<CAD21AoAqsSoTFWjajz1yycwxDnLZSWfV7wT7mmMBqy2k-jkLUQ@mail.gmail.com>
<20190109043316.GH21835@paquier.xyz>
<CAD21AoCzekiuDaGN-JqtoLgaYqqxGQ6nTcNqN0bm1EkvmcUKPA@mail.gmail.com>
<5C971918-E566-41AB-895A-C4793D56B041@amazon.com>
<20190122060730.GD8719@paquier.xyz>
On 1/21/19, 10:08 PM, "Michael Paquier" <michael@paquier.xyz> wrote:
> On Mon, Jan 21, 2019 at 10:09:09PM +0000, Bossart, Nathan wrote:
>> Here's a new patch set that should address the feedback in this
>> thread. The changes in this version include:
>>
>> - 0001 is a small fix to the 'vacuumdb --disable-page-skipping'
>> documentation. My suggestion is to keep it short and simple like
>> --full, --freeze, --skip-locked, --verbose, and --analyze. The
>> DISABLE_PAGE_SKIPPING option is well-described in the VACUUM
>> documentation, and IMO it is reasonably obvious that such vacuumdb
>> options correspond to the VACUUM options.
>
> Okay, committed this one.
Thanks.
> Regarding the search_path business, there is actually similar business
> in expand_table_name_patterns() for pg_dump if you look close by, so
> as users calling --table don't have to specify the schema, and just
> stick with patterns.
I see. The "WHERE c.relkind..." clause looks a bit different than
what I have, so I've altered vacuumdb's catalog query to match. This
was changed in expand_table_name_patterns() as part of 582edc369cd for
a security fix, so we should probably do something similar here.
> + /*
> + * Prepare the list of tables to process by querying the catalogs.
> + *
> + * Since we execute the constructed query with the default search_path
> + * (which could be unsafe), it is important to fully qualify everything.
> + */
> It is not only important, but also absolutely mandatory, so let's make
> the comment insisting harder on that point. From this point of view,
> the query that you are building is visibly correct.
I've updated the comment as suggested.
> + appendStringLiteralConn(&catalog_query, just_table, conn);
> + appendPQExpBuffer(&catalog_query, "::pg_catalog.regclass\n");
> +
> + pg_free(just_table);
> +
> + cell = cell->next;
> + if (cell == NULL)
> + appendPQExpBuffer(&catalog_query, " )\n");
> Hm. It seems to me that you are duplicating what
> processSQLNamePattern() does, so we ought to use it. And it is
> possible to control the generation of the different WHERE clauses with
> a single query based on the number of elements in the list. Perhaps I
> am missing something? It looks unnecessary to export
> split_table_columns_spec() as well.
I'm sorry, I don't quite follow this. AFAICT processSQLNamePattern()
is for generating WHERE clauses based on a wildcard-pattern string for
psql and pg_dump. This portion of the patch is generating a WHERE
clause to filter only for the tables listed via --table, and I don't
think the --table option currently accepts patterns. Since you can
also provide a list of columns with the --table option, I am using
split_table_columns_spec() to retrieve only the table name for the OID
lookup.
> - qr/statement: ANALYZE;/,
> + qr/statement: ANALYZE pg_catalog\./,
> Or we could just use "ANALYZE \.;/", perhaps patching it first.
> Perhaps my regexp here is incorrect, but I just mean to check for an
> ANALYZE query which begins by "ANALYZE " and finishes with ";",
> without caring about what is in-between. This would make the tests
> more portable.
Sure, I've split this out into a separate patch.
> +$node->issues_sql_like(
> + [ 'vacuumdb', '--table=pg_class', '--min-mxid-age=123456789',
> 'postgres'],
> + qr/AND GREATEST\(pg_catalog.mxid_age\(c.relminmxid\),
> pg_catalog.mxid_age\(t.relminmxid\)\) OPERATOR\(pg_catalog.>=\) 123456789/,
> + 'vacuumdb --table --min-mxid-age');
> +$node->issues_sql_like(
> + [ 'vacuumdb', '--min-xid-age=987654321', 'postgres' ],
> + qr/AND GREATEST\(pg_catalog.age\(c.relfrozenxid\),
> pg_catalog.age\(t.relfrozenxid\)\) OPERATOR\(pg_catalog.>=\) 987654321/,
> + 'vacuumdb --min-xid-age');
> It may be better to use numbers which make sure that no relations are
> actually fetched, so as if the surrounding tests are messed up we
> never make them longer than necessary just to check the shape of the
> query.
I think it's already pretty unlikely that any matching relations would
be found, but I've updated these values to be within the range where
any matching database has already stopped accepting commands to
prevent wraparound.
Nathan
Attachments:
[application/octet-stream] v4-0001-Adjust-vacuumdb-test-regex-in-preparation-for-alw.patch (2.1K, ../F716EB06-0CBE-4342-9CFA-23409319B52A@amazon.com/2-v4-0001-Adjust-vacuumdb-test-regex-in-preparation-for-alw.patch)
download | inline diff:
From 1cdbe18297a1225a1f8665fef4e9b7f4cbf68368 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Tue, 22 Jan 2019 22:33:07 +0000
Subject: [PATCH v4 1/3] Adjust vacuumdb test regex in preparation for always
using a catalog query to discover tables.
---
src/bin/scripts/t/100_vacuumdb.pl | 16 ++++++++--------
1 file changed, 8 insertions(+), 8 deletions(-)
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.plindex 7cb2542e47..951202b40e 100644--- a/src/bin/scripts/t/100_vacuumdb.pl+++ b/src/bin/scripts/t/100_vacuumdb.pl@@ -15,35 +15,35 @@ $node->start;
$node->issues_sql_like(
[ 'vacuumdb', 'postgres' ],
- qr/statement: VACUUM;/,+ qr/statement: VACUUM.*;/,
'SQL VACUUM run');
$node->issues_sql_like(
[ 'vacuumdb', '-f', 'postgres' ],
- qr/statement: VACUUM \(FULL\);/,+ qr/statement: VACUUM \(FULL\).*;/,
'vacuumdb -f');
$node->issues_sql_like(
[ 'vacuumdb', '-F', 'postgres' ],
- qr/statement: VACUUM \(FREEZE\);/,+ qr/statement: VACUUM \(FREEZE\).*;/,
'vacuumdb -F');
$node->issues_sql_like(
[ 'vacuumdb', '-zj2', 'postgres' ],
- qr/statement: VACUUM \(ANALYZE\) pg_catalog\./,+ qr/statement: VACUUM \(ANALYZE\).*;/,
'vacuumdb -zj2');
$node->issues_sql_like(
[ 'vacuumdb', '-Z', 'postgres' ],
- qr/statement: ANALYZE;/,+ qr/statement: ANALYZE.*;/,
'vacuumdb -Z');
$node->issues_sql_like(
[ 'vacuumdb', '--disable-page-skipping', 'postgres' ],
- qr/statement: VACUUM \(DISABLE_PAGE_SKIPPING\);/,+ qr/statement: VACUUM \(DISABLE_PAGE_SKIPPING\).*;/,
'vacuumdb --disable-page-skipping');
$node->issues_sql_like(
[ 'vacuumdb', '--skip-locked', 'postgres' ],
- qr/statement: VACUUM \(SKIP_LOCKED\);/,+ qr/statement: VACUUM \(SKIP_LOCKED\).*;/,
'vacuumdb --skip-locked');
$node->issues_sql_like(
[ 'vacuumdb', '--skip-locked', '--analyze-only', 'postgres' ],
- qr/statement: ANALYZE \(SKIP_LOCKED\);/,+ qr/statement: ANALYZE \(SKIP_LOCKED\).*;/,
'vacuumdb --skip-locked --analyze-only');
$node->command_fails(
[ 'vacuumdb', '--analyze-only', '--disable-page-skipping', 'postgres' ],
--
2.16.5
[application/octet-stream] v4-0002-Always-use-a-catalog-query-to-discover-tables-to-.patch (8.6K, ../F716EB06-0CBE-4342-9CFA-23409319B52A@amazon.com/3-v4-0002-Always-use-a-catalog-query-to-discover-tables-to-.patch)
download | inline diff:
From 98803895b2422a4e7ef78b922fb195e116b02110 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Tue, 22 Jan 2019 23:08:01 +0000
Subject: [PATCH v4 2/3] Always use a catalog query to discover tables to
process in vacuumdb.
---
src/bin/scripts/common.c | 2 +-
src/bin/scripts/common.h | 3 +
src/bin/scripts/vacuumdb.c | 149 +++++++++++++++++++++++++++------------------
3 files changed, 93 insertions(+), 61 deletions(-)
diff --git a/src/bin/scripts/common.c b/src/bin/scripts/common.cindex 4215bc3d6e..493408d15d 100644--- a/src/bin/scripts/common.c+++ b/src/bin/scripts/common.c@@ -265,7 +265,7 @@ executeMaintenanceCommand(PGconn *conn, const char *query, bool echo)
* finish using them, pg_free(*table). *columns is a pointer into "spec",
* possibly to its NUL terminator.
*/
-static void+void
split_table_columns_spec(const char *spec, int encoding,
char **table, const char **columns)
{
diff --git a/src/bin/scripts/common.h b/src/bin/scripts/common.hindex 7c3888cefd..f3a7616bae 100644--- a/src/bin/scripts/common.h+++ b/src/bin/scripts/common.h@@ -48,6 +48,9 @@ extern void executeCommand(PGconn *conn, const char *query,
extern bool executeMaintenanceCommand(PGconn *conn, const char *query,
bool echo);
+extern void split_table_columns_spec(const char *spec, int encoding,+ char **table, const char **columns);+
extern void appendQualifiedRelation(PQExpBuffer buf, const char *name,
PGconn *conn, const char *progname, bool echo);
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.cindex ec7d0a326a..b72e65c36d 100644--- a/src/bin/scripts/vacuumdb.c+++ b/src/bin/scripts/vacuumdb.c@@ -19,6 +19,7 @@
#include "catalog/pg_class_d.h"
#include "common.h"
+#include "fe_utils/connect.h"
#include "fe_utils/simple_list.h"
#include "fe_utils/string_utils.h"
@@ -62,9 +63,7 @@ static void vacuum_all_databases(vacuumingOptions *vacopts,
const char *progname, bool echo, bool quiet);
static void prepare_vacuum_command(PQExpBuffer sql, PGconn *conn,
- vacuumingOptions *vacopts, const char *table,- bool table_pre_qualified,- const char *progname, bool echo);+ vacuumingOptions *vacopts, const char *table);
static void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
const char *table, const char *progname, bool async);
@@ -359,13 +358,18 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
const char *progname, bool echo, bool quiet)
{
PQExpBufferData sql;
+ PQExpBufferData buf;+ PQExpBufferData catalog_query;+ PGresult *res;
PGconn *conn;
SimpleStringListCell *cell;
ParallelSlot *slots;
SimpleStringList dbtables = {NULL, NULL};
int i;
+ int ntups;
bool failed = false;
bool parallel = concurrentCons > 1;
+ bool first_table = true;
const char *stage_commands[] = {
"SET default_statistics_target=1; SET vacuum_cost_delay=0;",
"SET default_statistics_target=10; RESET vacuum_cost_delay;",
@@ -410,54 +414,91 @@ 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.+ * Prepare the list of tables to process by querying the catalogs.+ *+ * Since we execute the constructed query with the default search_path+ * (which could be unsafe), everything in this query must be fully+ * qualified.
*/
- if (parallel && (!tables || !tables->head))+ initPQExpBuffer(&catalog_query);+ appendPQExpBuffer(&catalog_query,+ "SELECT c.relname, ns.nspname"+ " FROM pg_catalog.pg_class c\n"+ " JOIN pg_catalog.pg_namespace ns"+ " 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"+ " WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array["+ CppAsString2(RELKIND_RELATION) ", "+ CppAsString2(RELKIND_MATVIEW) "])\n");++ cell = tables ? tables->head : NULL;+ while (cell != NULL)
{
- PQExpBufferData buf;- PGresult *res;- int ntups;-- initPQExpBuffer(&buf);-- 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);-- ntups = PQntuples(res);- for (i = 0; i < ntups; i++)- {- appendPQExpBufferStr(&buf,- fmtQualifiedId(PQgetvalue(res, i, 1),- PQgetvalue(res, i, 0)));+ char *just_table;+ const char *just_columns;- simple_string_list_append(&dbtables, buf.data);- resetPQExpBuffer(&buf);+ split_table_columns_spec(cell->val, PQclientEncoding(conn),+ &just_table, &just_columns);++ if (first_table)+ {+ appendPQExpBuffer(&catalog_query, " AND (\n c.oid OPERATOR(pg_catalog.=) ");+ first_table = false;
}
+ else+ appendPQExpBuffer(&catalog_query, " OR c.oid OPERATOR(pg_catalog.=) ");- termPQExpBuffer(&buf);- tables = &dbtables;+ appendStringLiteralConn(&catalog_query, just_table, conn);+ appendPQExpBuffer(&catalog_query, "::pg_catalog.regclass\n");- /*- * If there are more connections than vacuumable relations, we don't- * need to use them all.- */+ pg_free(just_table);++ cell = cell->next;+ if (cell == NULL)+ appendPQExpBuffer(&catalog_query, " )\n");+ }++ appendPQExpBuffer(&catalog_query, " ORDER BY c.relpages DESC;");+ executeCommand(conn, "RESET search_path;", progname, echo);+ res = executeQuery(conn, catalog_query.data, progname, echo);+ termPQExpBuffer(&catalog_query);+ PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL,+ progname, echo));++ ntups = PQntuples(res);+ if (ntups == 0)+ {+ PQclear(res);+ PQfinish(conn);+ return;+ }++ initPQExpBuffer(&buf);+ for (i = 0; i < ntups; i++)+ {+ appendPQExpBufferStr(&buf,+ fmtQualifiedId(PQgetvalue(res, i, 1),+ PQgetvalue(res, i, 0)));++ simple_string_list_append(&dbtables, buf.data);+ resetPQExpBuffer(&buf);+ }+ termPQExpBuffer(&buf);++ /*+ * If there are more connections than vacuumable relations, we don't need+ * to use them all.+ */+ if (parallel)+ {
if (concurrentCons > ntups)
concurrentCons = ntups;
if (concurrentCons <= 1)
parallel = false;
- PQclear(res);
}
+ PQclear(res);
/*
* Setup the database connections. We reuse the connection we already have
@@ -493,10 +534,12 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
stage_commands[stage], progname, echo);
}
- cell = tables ? tables->head : NULL;+ initPQExpBuffer(&sql);++ cell = dbtables.head;
do
{
- const char *tabname = cell ? cell->val : NULL;+ const char *tabname = cell->val;
ParallelSlot *free_slot;
if (CancelRequested)
@@ -529,12 +572,7 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
else
free_slot = slots;
- /*- * Prepare the vacuum command. Note that in some cases this requires- * query execution, so be sure to use the free connection.- */- prepare_vacuum_command(&sql, free_slot->connection, vacopts, tabname,- tables == &dbtables, progname, echo);+ prepare_vacuum_command(&sql, free_slot->connection, vacopts, tabname);
/*
* Execute the vacuum. If not in parallel mode, this terminates the
@@ -544,8 +582,7 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
run_vacuum_command(free_slot->connection, sql.data,
echo, tabname, progname, parallel);
- if (cell)- cell = cell->next;+ cell = cell->next;
} while (cell != NULL);
if (parallel)
@@ -658,9 +695,7 @@ vacuum_all_databases(vacuumingOptions *vacopts,
*/
static void
prepare_vacuum_command(PQExpBuffer sql, PGconn *conn,
- vacuumingOptions *vacopts, const char *table,- bool table_pre_qualified,- const char *progname, bool echo)+ vacuumingOptions *vacopts, const char *table)
{
const char *paren = " (";
const char *comma = ", ";
@@ -753,14 +788,8 @@ prepare_vacuum_command(PQExpBuffer sql, PGconn *conn,
}
}
- if (table)- {- appendPQExpBufferChar(sql, ' ');- if (table_pre_qualified)- appendPQExpBufferStr(sql, table);- else- appendQualifiedRelation(sql, table, conn, progname, echo);- }+ appendPQExpBufferChar(sql, ' ');+ appendPQExpBufferStr(sql, table);
appendPQExpBufferChar(sql, ';');
}
--
2.16.5
[application/octet-stream] v4-0003-Add-min-xid-age-and-min-mxid-age-options-to-vacuu.patch (6.7K, ../F716EB06-0CBE-4342-9CFA-23409319B52A@amazon.com/4-v4-0003-Add-min-xid-age-and-min-mxid-age-options-to-vacuu.patch)
download | inline diff:
From a97cc74d8511ebb47585b7865caf94a6b44890bd Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Tue, 22 Jan 2019 23:11:33 +0000
Subject: [PATCH v4 3/3] Add --min-xid-age and --min-mxid-age options to
vacuumdb.
---
doc/src/sgml/ref/vacuumdb.sgml | 37 +++++++++++++++++++++++++++++
src/bin/scripts/t/100_vacuumdb.pl | 11 ++++++++-
src/bin/scripts/vacuumdb.c | 50 +++++++++++++++++++++++++++++++++++++++
3 files changed, 97 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgmlindex f304627802..29ccba2a5a 100644--- a/doc/src/sgml/ref/vacuumdb.sgml+++ b/doc/src/sgml/ref/vacuumdb.sgml@@ -172,6 +172,43 @@ 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>+ <note>+ <para>+ This option is only available for servers running PostgreSQL 9.5 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>+ <note>+ <para>+ This option is only available for servers running PostgreSQL 7.2 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.plindex 951202b40e..62355a50e2 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 => 34;
program_help_ok('vacuumdb');
program_version_ok('vacuumdb');
@@ -75,3 +75,12 @@ $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->issues_sql_like(+ [ 'vacuumdb', '--table=pg_class', '--min-mxid-age=2147483000', 'postgres'],+ qr/AND GREATEST\(pg_catalog.mxid_age\(c.relminmxid\), pg_catalog.mxid_age\(t.relminmxid\)\) OPERATOR\(pg_catalog.>=\) 2147483000/,+ 'vacuumdb --table --min-mxid-age');+$node->issues_sql_like(+ [ 'vacuumdb', '--min-xid-age=2147483001', 'postgres' ],+ qr/AND GREATEST\(pg_catalog.age\(c.relfrozenxid\), pg_catalog.age\(t.relfrozenxid\)\) OPERATOR\(pg_catalog.>=\) 2147483001/,+ 'vacuumdb --min-xid-age');diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.cindex b72e65c36d..22cf55e8d3 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);
@@ -403,6 +425,20 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
exit(1);
}
+ if (vacopts->min_xid_age != 0 && PQserverVersion(conn) < 70200)+ {+ fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 7.2\n"),+ progname, "--min-xid-age");+ exit(1);+ }++ if (vacopts->min_mxid_age != 0 && PQserverVersion(conn) < 90500)+ {+ fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 9.5\n"),+ progname, "--min-mxid-age");+ exit(1);+ }+
if (!quiet)
{
if (stage != ANALYZE_NO_STAGE)
@@ -433,6 +469,18 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
CppAsString2(RELKIND_RELATION) ", "
CppAsString2(RELKIND_MATVIEW) "])\n");
+ if (vacopts->min_xid_age != 0)+ appendPQExpBuffer(&catalog_query,+ " AND GREATEST(pg_catalog.age(c.relfrozenxid),"+ " pg_catalog.age(t.relfrozenxid)) OPERATOR(pg_catalog.>=) %d\n",+ vacopts->min_xid_age);++ if (vacopts->min_mxid_age != 0)+ appendPQExpBuffer(&catalog_query,+ " AND GREATEST(pg_catalog.mxid_age(c.relminmxid),"+ " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=) %d\n",+ vacopts->min_mxid_age);+
cell = tables ? tables->head : NULL;
while (cell != NULL)
{
@@ -1104,6 +1152,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
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, michael@paquier.xyz, sawada.mshk@gmail.com, robertmhaas@gmail.com
Subject: Re: A few new options for vacuumdb
In-Reply-To: <F716EB06-0CBE-4342-9CFA-23409319B52A@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