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: Thu, 24 Jan 2019 00:49:28 +0000
Message-ID: <26A57CB3-2847-495F-93CA-9358959CDAD4@amazon.com> (raw)
In-Reply-To: <20190123034111.GH3873@paquier.xyz>
References: <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>
<F716EB06-0CBE-4342-9CFA-23409319B52A@amazon.com>
<20190123034111.GH3873@paquier.xyz>
On 1/22/19, 7:41 PM, "Michael Paquier" <michael@paquier.xyz> wrote:
> I have been looking at 0002, and I found a problem with the way
> ANALYZE queries are generated. For example take this table:
> CREATE TABLE aa1 (a int);
>
> Then if I try to run ANALYZE with vacuumdb it just works:
> $ vacuumdb -z --table 'aa1(b)'
> vacuumdb: vacuuming database "ioltas"
>
> Note that this fails with HEAD, but passes with your patch. The
> problem is that the query generated misses the lists of columns when
> processing them through split_table_columns_spec(), as what is
> generated is that:
> VACUUM (ANALYZE) public.aa1;
>
> So the result is actually incorrect because all the columns get
> processed.
>
> This patch moves the check about the existence of the relation when
> querying the catalogs, perhaps we would want the same for columns for
> consistency? Or not. That's a bit harder for sure, not impossible
> visibly, still that would mean duplicating a bunch of logic that the
> backend is doing by itself, so we could live without it I think.
Oh, wow. Thanks for pointing this out. I should have caught this.
With 0002, we are basically just throwing out the column lists
entirely as we obtain the qualified identifiers from the catalog
query. To fix this, I've added an optional CTE for tracking any
provided column lists. v5-0001 is your test patch for this case, and
v5-0002 splits out the work for split_table_columns_spec().
Nathan
Attachments:
[application/octet-stream] v5-0001-Add-tests-for-using-vacuumdb-with-column-lists-pr.patch (1.8K, ../26A57CB3-2847-495F-93CA-9358959CDAD4@amazon.com/2-v5-0001-Add-tests-for-using-vacuumdb-with-column-lists-pr.patch)
download | inline diff:
From 33305f7506be52ad1926d34d4413db9d4cd48472 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Wed, 23 Jan 2019 22:07:01 +0000
Subject: [PATCH v5 1/4] Add tests for using vacuumdb with column lists
provided.
---
src/bin/scripts/t/100_vacuumdb.pl | 15 ++++++++++++++-
1 file changed, 14 insertions(+), 1 deletion(-)
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.plindex 951202b40e..ff0d97971b 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 => 35;
program_help_ok('vacuumdb');
program_version_ok('vacuumdb');
@@ -63,6 +63,7 @@ $node->command_ok(
$node->safe_psql(
'postgres', q|
CREATE TABLE "need""q(uot" (")x" text);
+ CREATE TABLE vactable (a int, b int);
CREATE FUNCTION f0(int) RETURNS int LANGUAGE SQL AS 'SELECT $1 * $1';
CREATE FUNCTION f1(int) RETURNS int LANGUAGE SQL AS 'SELECT f0($1)';
@@ -75,3 +76,15 @@ $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(+ [ 'vacuumdb', '--analyze', '--table', 'vactable(c)', 'postgres' ],+ 'incorrect column name with ANALYZE');+$node->issues_sql_like(+ [ 'vacuumdb', '--analyze', '--table', 'vactable(a, b)', 'postgres' ],+ qr/statement: VACUUM \(ANALYZE\) public.vactable\(a, b\);/,+ 'vacuumdb --analyze with complete column list');+$node->issues_sql_like(+ [ 'vacuumdb', '--analyze-only', '--table', 'vactable(b)', 'postgres' ],+ qr/statement: ANALYZE public.vactable\(b\);/,+ 'vacuumdb --analyze-only with partial column list');--
2.16.5
[application/octet-stream] v5-0002-Export-split_table_columns_spec-for-use-in-vacuum.patch (1.9K, ../26A57CB3-2847-495F-93CA-9358959CDAD4@amazon.com/3-v5-0002-Export-split_table_columns_spec-for-use-in-vacuum.patch)
download | inline diff:
From 0686bfafda890148428fbcff215db932c54c0ade Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Wed, 23 Jan 2019 22:18:56 +0000
Subject: [PATCH v5 2/4] Export split_table_columns_spec() for use in vacuumdb.
---
src/bin/scripts/common.c | 8 ++++----
src/bin/scripts/common.h | 3 +++
2 files changed, 7 insertions(+), 4 deletions(-)
diff --git a/src/bin/scripts/common.c b/src/bin/scripts/common.cindex 4215bc3d6e..7139b7c667 100644--- a/src/bin/scripts/common.c+++ b/src/bin/scripts/common.c@@ -265,9 +265,9 @@ 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-split_table_columns_spec(const char *spec, int encoding,- char **table, const char **columns)+void+splitTableColumnsSpec(const char *spec, int encoding,+ char **table, const char **columns)
{
bool inquotes = false;
const char *cp = spec;
@@ -318,7 +318,7 @@ appendQualifiedRelation(PQExpBuffer buf, const char *spec,
return;
}
- split_table_columns_spec(spec, PQclientEncoding(conn), &table, &columns);+ splitTableColumnsSpec(spec, PQclientEncoding(conn), &table, &columns);
/*
* Query must remain ABSOLUTELY devoid of unqualified names. This would
diff --git a/src/bin/scripts/common.h b/src/bin/scripts/common.hindex 7c3888cefd..b59ade0c39 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 splitTableColumnsSpec(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);
--
2.16.5
[application/octet-stream] v5-0003-Always-use-a-catalog-query-to-discover-tables-to-.patch (10.2K, ../26A57CB3-2847-495F-93CA-9358959CDAD4@amazon.com/4-v5-0003-Always-use-a-catalog-query-to-discover-tables-to-.patch)
download | inline diff:
From caa66ac5de9a6de1c4c76f6450a4a0ee2f34e47c Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Wed, 23 Jan 2019 23:27:58 +0000
Subject: [PATCH v5 3/4] Always use a catalog query to discover tables to
process in vacuumdb.
---
src/bin/scripts/vacuumdb.c | 227 +++++++++++++++++++++++++++++++++------------
1 file changed, 170 insertions(+), 57 deletions(-)
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.cindex ec7d0a326a..b8bf2e3ff5 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,21 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
const char *progname, bool echo, bool quiet)
{
PQExpBufferData sql;
+ PQExpBufferData buf;+ PQExpBufferData catalog_query;+ PQExpBufferData cte;+ PQExpBufferData table_clause;+ PGresult *res;
PGconn *conn;
SimpleStringListCell *cell;
ParallelSlot *slots;
SimpleStringList dbtables = {NULL, NULL};
int i;
+ int ntups;
bool failed = false;
bool parallel = concurrentCons > 1;
+ bool tables_listed = false;+ bool columns_listed = false;
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 +417,172 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
fflush(stdout);
}
- initPQExpBuffer(&sql);+ /*+ * 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.+ */+ initPQExpBuffer(&cte);+ initPQExpBuffer(&table_clause);
/*
- * If a table list is not provided and we're using multiple connections,- * prepare the list of tables by querying the catalogs.+ * First, build the WITH clause and the list of tables for the catalog+ * query. The WITH clause is used to match any provided column lists with+ * the generated qualified identifiers. The table list is used to filter+ * for the tables provided via --table and to check that the table exists.+ * If a listed table does not exist, the catalog query will fail, as will+ * vacuumdb.
*/
- if (parallel && (!tables || !tables->head))+ 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;++ splitTableColumnsSpec(cell->val, PQclientEncoding(conn),+ &just_table, &just_columns);- simple_string_list_append(&dbtables, buf.data);- resetPQExpBuffer(&buf);+ /*+ * First, handle the table list. The "regclass" identifier is used to+ * look up the table's schema and name (using the default search_path)+ * in order to generate a qualified identifier for the VACUUM or+ * ANALYZE command.+ */+ if (!tables_listed)+ {+ appendPQExpBuffer(&table_clause, " AND (\n c.oid OPERATOR(pg_catalog.=) ");+ tables_listed = true;
}
+ else+ appendPQExpBuffer(&table_clause, " OR c.oid OPERATOR(pg_catalog.=) ");- termPQExpBuffer(&buf);- tables = &dbtables;+ appendStringLiteralConn(&table_clause, just_table, conn);+ appendPQExpBuffer(&table_clause, "::pg_catalog.regclass\n");
/*
- * If there are more connections than vacuumable relations, we don't- * need to use them all.+ * Now handle the column list, if provided. This generates a WITH+ * clause that is used to map the table name and schema with the+ * optional column list provided.
*/
+ if (just_columns != NULL && just_columns[0] != '\0')+ {+ if (!columns_listed)+ {+ appendPQExpBuffer(&cte, "WITH column_lists (table_name, column_list)"+ " AS (\n VALUES (");+ columns_listed = true;+ }+ else+ appendPQExpBuffer(&cte, ",\n (");++ appendStringLiteralConn(&cte, just_table, conn);+ appendPQExpBuffer(&cte, ", ");+ appendStringLiteralConn(&cte, just_columns, conn);+ appendPQExpBuffer(&cte, ")");+ }++ pg_free(just_table);++ cell = cell->next;+ if (cell == NULL)+ {+ appendPQExpBuffer(&table_clause, " )\n");++ if (columns_listed)+ appendPQExpBuffer(&cte, "\n)\n");+ }+ }++ /*+ * Build the full catalog query. If the user specified tables with column+ * lists, this consists of a WITH clause and a SELECT clause that filters+ * based upon the table names. If the user specified tables but no column+ * lists, just the SELECT clause with the table name filters is generated.+ * If no tables or column lists were given, the query will exclude the+ * table name filters as well.+ */+ initPQExpBuffer(&catalog_query);+ if (columns_listed)+ appendPQExpBufferStr(&catalog_query, cte.data);+ termPQExpBuffer(&cte);++ appendPQExpBuffer(&catalog_query, "SELECT c.relname, ns.nspname");++ if (columns_listed)+ appendPQExpBuffer(&catalog_query, ", column_lists.column_list");++ 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");++ if (columns_listed)+ appendPQExpBuffer(&catalog_query, " LEFT JOIN column_lists"+ " ON column_lists.table_name::pg_catalog.regclass OPERATOR(pg_catalog.=) c.oid\n");++ appendPQExpBuffer(&catalog_query, " WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array["+ CppAsString2(RELKIND_RELATION) ", "+ CppAsString2(RELKIND_MATVIEW) "])\n");++ if (tables_listed)+ appendPQExpBufferStr(&catalog_query, table_clause.data);+ termPQExpBuffer(&table_clause);++ /*+ * Execute the catalog query. We use the default search_path for this+ * query for consistency with table lookups done elsewhere by the user.+ */+ 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));++ /*+ * If no rows are returned, we are already done.+ */+ ntups = PQntuples(res);+ if (ntups == 0)+ {+ PQclear(res);+ PQfinish(conn);+ return;+ }++ /*+ * Build qualified identifiers for each table, including the column list+ * if given.+ */+ initPQExpBuffer(&buf);+ for (i = 0; i < ntups; i++)+ {+ appendPQExpBufferStr(&buf,+ fmtQualifiedId(PQgetvalue(res, i, 1),+ PQgetvalue(res, i, 0)));++ if (columns_listed && PQgetisnull(res, i, 2) == 0)+ appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2));++ 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 +618,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 +656,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 +666,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 +779,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 +872,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] v5-0004-Add-min-xid-age-and-min-mxid-age-options-to-vacuu.patch (7.3K, ../26A57CB3-2847-495F-93CA-9358959CDAD4@amazon.com/5-v5-0004-Add-min-xid-age-and-min-mxid-age-options-to-vacuu.patch)
download | inline diff:
From 9c1db3c811a0c1c2ba7cd2857bc5a1606a6fb014 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Tue, 22 Jan 2019 23:11:33 +0000
Subject: [PATCH v5 4/4] 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 | 10 +++++++-
src/bin/scripts/vacuumdb.c | 54 ++++++++++++++++++++++++++++++++++++++-
3 files changed, 99 insertions(+), 2 deletions(-)
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 ff0d97971b..b19a4abf26 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 => 35;+use Test::More tests => 39;
program_help_ok('vacuumdb');
program_version_ok('vacuumdb');
@@ -88,3 +88,11 @@ $node->issues_sql_like(
[ 'vacuumdb', '--analyze-only', '--table', 'vactable(b)', 'postgres' ],
qr/statement: ANALYZE public.vactable\(b\);/,
'vacuumdb --analyze-only with partial column list');
+$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 b8bf2e3ff5..7ca3766eef 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);
@@ -406,6 +428,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)
@@ -516,7 +552,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");
if (columns_listed)
appendPQExpBuffer(&catalog_query, " LEFT JOIN column_lists"
@@ -530,6 +568,18 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
appendPQExpBufferStr(&catalog_query, table_clause.data);
termPQExpBuffer(&table_clause);
+ 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);+
/*
* Execute the catalog query. We use the default search_path for this
* query for consistency with table lookups done elsewhere by the user.
@@ -1188,6 +1238,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: <26A57CB3-2847-495F-93CA-9358959CDAD4@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