Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit00d1e88

Browse files
committed
Add --min-xid-age and --min-mxid-age options to vacuumdb
These two new options can be used to improve the selectivity ofrelations to vacuum or analyze even further depending on the age ofrespectively their transaction ID or multixact ID, so as it is possibleto prioritize tables to prevent wraparound of one or the other.Combined with --table, it is possible to target a subset of tables tochoose as potential processing targets.Author: Nathan BossartReviewed-by: Michael Paquier, Masahiko SawadaDiscussion:https://postgr.es/m/FFE5373C-E26A-495B-B5C8-911EC4A41C5E@amazon.com
1 parent5f5c014 commit00d1e88

File tree

3 files changed

+148
-2
lines changed

3 files changed

+148
-2
lines changed

‎doc/src/sgml/ref/vacuumdb.sgml

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -172,6 +172,60 @@ PostgreSQL documentation
172172
</listitem>
173173
</varlistentry>
174174

175+
<varlistentry>
176+
<term><option>--min-mxid-age <replaceable class="parameter">mxid_age</replaceable></option></term>
177+
<listitem>
178+
<para>
179+
Only execute the vacuum or analyze commands on tables with a multixact
180+
ID age of at least <replaceable class="parameter">mxid_age</replaceable>.
181+
This setting is useful for prioritizing tables to process to prevent
182+
multixact ID wraparound (see
183+
<xref linkend="vacuum-for-multixact-wraparound"/>).
184+
</para>
185+
<para>
186+
For the purposes of this option, the multixact ID age of a relation is
187+
the greatest of the ages of the main relation and its associated
188+
<acronym>TOAST</acronym> table, if one exists. Since the commands
189+
issued by <application>vacuumdb</application> will also process the
190+
<acronym>TOAST</acronym> table for the relation if necessary, it does
191+
not need to be considered separately.
192+
</para>
193+
<note>
194+
<para>
195+
This option is only available for servers running
196+
<productname>PostgreSQL</productname> 9.6 and later.
197+
</para>
198+
</note>
199+
</listitem>
200+
</varlistentry>
201+
202+
<varlistentry>
203+
<term><option>--min-xid-age <replaceable class="parameter">xid_age</replaceable></option></term>
204+
<listitem>
205+
<para>
206+
Only execute the vacuum or analyze commands on tables with a
207+
transaction ID age of at least
208+
<replaceable class="parameter">xid_age</replaceable>. This setting
209+
is useful for prioritizing tables to process to prevent transaction
210+
ID wraparound (see <xref linkend="vacuum-for-wraparound"/>).
211+
</para>
212+
<para>
213+
For the purposes of this option, the transaction ID age of a relation
214+
is the greatest of the ages of the main relation and its associated
215+
<acronym>TOAST</acronym> table, if one exists. Since the commands
216+
issued by <application>vacuumdb</application> will also process the
217+
<acronym>TOAST</acronym> table for the relation if necessary, it does
218+
not need to be considered separately.
219+
</para>
220+
<note>
221+
<para>
222+
This option is only available for servers running
223+
<productname>PostgreSQL</productname> 9.6 and later.
224+
</para>
225+
</note>
226+
</listitem>
227+
</varlistentry>
228+
175229
<varlistentry>
176230
<term><option>-q</option></term>
177231
<term><option>--quiet</option></term>

‎src/bin/scripts/t/100_vacuumdb.pl

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33

44
use PostgresNode;
55
use TestLib;
6-
use Test::Moretests=>38;
6+
use Test::Moretests=>44;
77

88
program_help_ok('vacuumdb');
99
program_version_ok('vacuumdb');
@@ -95,3 +95,20 @@
9595
[qr/^.*vacuuming database "postgres"/],
9696
[qr/^WARNING.*cannot vacuum non-tables or special system tables/s],
9797
'vacuumdb with view');
98+
$node->command_fails(
99+
['vacuumdb','--table','vactable','--min-mxid-age','0',
100+
'postgres'],
101+
'vacuumdb --min-mxid-age with incorrect value');
102+
$node->command_fails(
103+
['vacuumdb','--table','vactable','--min-xid-age','0',
104+
'postgres'],
105+
'vacuumdb --min-xid-age with incorrect value');
106+
$node->issues_sql_like(
107+
['vacuumdb','--table','vactable','--min-mxid-age','2147483000',
108+
'postgres'],
109+
qr/GREATEST.*relminmxid.*2147483000/,
110+
'vacuumdb --table --min-mxid-age');
111+
$node->issues_sql_like(
112+
['vacuumdb','--min-xid-age','2147483001','postgres' ],
113+
qr/GREATEST.*relfrozenxid.*2147483001/,
114+
'vacuumdb --table --min-xid-age');

‎src/bin/scripts/vacuumdb.c

Lines changed: 76 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -43,6 +43,8 @@ typedef struct vacuumingOptions
4343
boolfreeze;
4444
booldisable_page_skipping;
4545
boolskip_locked;
46+
intmin_xid_age;
47+
intmin_mxid_age;
4648
}vacuumingOptions;
4749

4850

@@ -113,6 +115,8 @@ main(int argc, char *argv[])
113115
{"analyze-in-stages",no_argument,NULL,3},
114116
{"disable-page-skipping",no_argument,NULL,4},
115117
{"skip-locked",no_argument,NULL,5},
118+
{"min-xid-age",required_argument,NULL,6},
119+
{"min-mxid-age",required_argument,NULL,7},
116120
{NULL,0,NULL,0}
117121
};
118122

@@ -222,6 +226,24 @@ main(int argc, char *argv[])
222226
case5:
223227
vacopts.skip_locked= true;
224228
break;
229+
case6:
230+
vacopts.min_xid_age=atoi(optarg);
231+
if (vacopts.min_xid_age <=0)
232+
{
233+
fprintf(stderr,_("%s: minimum transaction ID age must be at least 1\n"),
234+
progname);
235+
exit(1);
236+
}
237+
break;
238+
case7:
239+
vacopts.min_mxid_age=atoi(optarg);
240+
if (vacopts.min_mxid_age <=0)
241+
{
242+
fprintf(stderr,_("%s: minimum multixact ID age must be at least 1\n"),
243+
progname);
244+
exit(1);
245+
}
246+
break;
225247
default:
226248
fprintf(stderr,_("Try \"%s --help\" for more information.\n"),progname);
227249
exit(1);
@@ -370,6 +392,7 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
370392
boolfailed= false;
371393
boolparallel=concurrentCons>1;
372394
booltables_listed= false;
395+
boolhas_where= false;
373396
constchar*stage_commands[]= {
374397
"SET default_statistics_target=1; SET vacuum_cost_delay=0;",
375398
"SET default_statistics_target=10; RESET vacuum_cost_delay;",
@@ -403,6 +426,20 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
403426
exit(1);
404427
}
405428

429+
if (vacopts->min_xid_age!=0&&PQserverVersion(conn)<90600)
430+
{
431+
fprintf(stderr,_("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 9.6\n"),
432+
progname,"--min-xid-age");
433+
exit(1);
434+
}
435+
436+
if (vacopts->min_mxid_age!=0&&PQserverVersion(conn)<90600)
437+
{
438+
fprintf(stderr,_("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 9.6\n"),
439+
progname,"--min-mxid-age");
440+
exit(1);
441+
}
442+
406443
if (!quiet)
407444
{
408445
if (stage!=ANALYZE_NO_STAGE)
@@ -477,7 +514,9 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
477514
appendPQExpBuffer(&catalog_query,
478515
" FROM pg_catalog.pg_class c\n"
479516
" JOIN pg_catalog.pg_namespace ns"
480-
" ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n");
517+
" ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
518+
" LEFT JOIN pg_catalog.pg_class t"
519+
" ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
481520

482521
/* Used to match the tables listed by the user */
483522
if (tables_listed)
@@ -491,9 +530,43 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
491530
* processed in which case the user will know about it.
492531
*/
493532
if (!tables_listed)
533+
{
494534
appendPQExpBuffer(&catalog_query," WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array["
495535
CppAsString2(RELKIND_RELATION)", "
496536
CppAsString2(RELKIND_MATVIEW)"])\n");
537+
has_where= true;
538+
}
539+
540+
/*
541+
* For --min-xid-age and --min-mxid-age, the age of the relation is the
542+
* greatest of the ages of the main relation and its associated TOAST
543+
* table. The commands generated by vacuumdb will also process the TOAST
544+
* table for the relation if necessary, so it does not need to be
545+
* considered separately.
546+
*/
547+
if (vacopts->min_xid_age!=0)
548+
{
549+
appendPQExpBuffer(&catalog_query,
550+
" %s GREATEST(pg_catalog.age(c.relfrozenxid),"
551+
" pg_catalog.age(t.relfrozenxid)) "
552+
" OPERATOR(pg_catalog.>=) '%d'::pg_catalog.int4\n"
553+
" AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
554+
" '0'::pg_catalog.xid\n",
555+
has_where ?"AND" :"WHERE",vacopts->min_xid_age);
556+
has_where= true;
557+
}
558+
559+
if (vacopts->min_mxid_age!=0)
560+
{
561+
appendPQExpBuffer(&catalog_query,
562+
" %s GREATEST(pg_catalog.mxid_age(c.relminmxid),"
563+
" pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=)"
564+
" '%d'::pg_catalog.int4\n"
565+
" AND c.relminmxid OPERATOR(pg_catalog.!=)"
566+
" '0'::pg_catalog.xid\n",
567+
has_where ?"AND" :"WHERE",vacopts->min_mxid_age);
568+
has_where= true;
569+
}
497570

498571
/*
499572
* Execute the catalog query. We use the default search_path for this
@@ -1152,6 +1225,8 @@ help(const char *progname)
11521225
printf(_(" -f, --full do full vacuuming\n"));
11531226
printf(_(" -F, --freeze freeze row transaction information\n"));
11541227
printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
1228+
printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
1229+
printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
11551230
printf(_(" -q, --quiet don't write any messages\n"));
11561231
printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
11571232
printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp