aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
authorMichael Paquier <michael@paquier.xyz>2019-01-31 13:06:51 +0900
committerMichael Paquier <michael@paquier.xyz>2019-01-31 13:07:56 +0900
commit00d1e88d36687ceae1be2317fac90e967941c085 (patch)
tree1a3c52aa0db5956a743649108b4361b18ee15368 /src
parent5f5c01459053c0975b89b5aa1a3d705e677348ae (diff)
downloadpostgresql-00d1e88d36687ceae1be2317fac90e967941c085.tar.gz
postgresql-00d1e88d36687ceae1be2317fac90e967941c085.zip
Add --min-xid-age and --min-mxid-age options to vacuumdb
These two new options can be used to improve the selectivity of relations to vacuum or analyze even further depending on the age of respectively their transaction ID or multixact ID, so as it is possible to prioritize tables to prevent wraparound of one or the other. Combined with --table, it is possible to target a subset of tables to choose as potential processing targets. Author: Nathan Bossart Reviewed-by: Michael Paquier, Masahiko Sawada Discussion: https://postgr.es/m/FFE5373C-E26A-495B-B5C8-911EC4A41C5E@amazon.com
Diffstat (limited to 'src')
-rw-r--r--src/bin/scripts/t/100_vacuumdb.pl19
-rw-r--r--src/bin/scripts/vacuumdb.c77
2 files changed, 94 insertions, 2 deletions
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 5e87af2d519..7f3a9b14a91 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 40ba8283a28..5ac41ea757b 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.relminmxid 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"));