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

Commit7cb3048

Browse files
committed
Add option PROCESS_TOAST to VACUUM
This option controls if toast tables associated with a relation arevacuumed or not when running a manual VACUUM. It was already possibleto trigger a manual VACUUM on a toast relation without processing itsmain relation, but a manual vacuum on a main relation always forced avacuum on its toast table. This is useful in scenarios where the levelof bloat or transaction age of the main and toast relations differs alot.This option is an extension of the existing VACOPT_SKIPTOAST that wasused by autovacuum to control if toast relations should be skipped ornot. This internal flag is renamed to VACOPT_PROCESS_TOAST forconsistency with the new option.A new option switch, called --no-process-toast, is added to vacuumdb.Author: Nathan BossartReviewed-by: Kirk Jamison, Michael Paquier, Justin PryzbyDiscussion:https://postgr.es/m/BA8951E9-1524-48C5-94AF-73B1F0D7857F@amazon.com
1 parent5fd5900 commit7cb3048

File tree

10 files changed

+104
-14
lines changed

10 files changed

+104
-14
lines changed

‎doc/src/sgml/ref/vacuum.sgml

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
3333
DISABLE_PAGE_SKIPPING [ <replaceable class="parameter">boolean</replaceable> ]
3434
SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
3535
INDEX_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
36+
PROCESS_TOAST [ <replaceable class="parameter">boolean</replaceable> ]
3637
TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
3738
PARALLEL <replaceable class="parameter">integer</replaceable>
3839

@@ -210,6 +211,20 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
210211
</listitem>
211212
</varlistentry>
212213

214+
<varlistentry>
215+
<term><literal>PROCESS_TOAST</literal></term>
216+
<listitem>
217+
<para>
218+
Specifies that <command>VACUUM</command> should attempt to process the
219+
corresponding <literal>TOAST</literal> table for each relation, if one
220+
exists. This is normally the desired behavior and is the default.
221+
Setting this option to false may be useful when it is only necessary to
222+
vacuum the main relation. This option is required when the
223+
<literal>FULL</literal> option is used.
224+
</para>
225+
</listitem>
226+
</varlistentry>
227+
213228
<varlistentry>
214229
<term><literal>TRUNCATE</literal></term>
215230
<listitem>

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -244,6 +244,21 @@ PostgreSQL documentation
244244
</listitem>
245245
</varlistentry>
246246

247+
<varlistentry>
248+
<term><option>--no-process-toast</option></term>
249+
<listitem>
250+
<para>
251+
Skip the TOAST table associated with the table to vacuum, if any.
252+
</para>
253+
<note>
254+
<para>
255+
This option is only available for servers running
256+
<productname>PostgreSQL</productname> 14 and later.
257+
</para>
258+
</note>
259+
</listitem>
260+
</varlistentry>
261+
247262
<varlistentry>
248263
<term><option>--no-truncate</option></term>
249264
<listitem>

‎src/backend/commands/vacuum.c

Lines changed: 14 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -104,6 +104,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
104104
boolfreeze= false;
105105
boolfull= false;
106106
booldisable_page_skipping= false;
107+
boolprocess_toast= true;
107108
ListCell*lc;
108109

109110
/* Set default value */
@@ -140,6 +141,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
140141
disable_page_skipping=defGetBoolean(opt);
141142
elseif (strcmp(opt->defname,"index_cleanup")==0)
142143
params.index_cleanup=get_vacopt_ternary_value(opt);
144+
elseif (strcmp(opt->defname,"process_toast")==0)
145+
process_toast=defGetBoolean(opt);
143146
elseif (strcmp(opt->defname,"truncate")==0)
144147
params.truncate=get_vacopt_ternary_value(opt);
145148
elseif (strcmp(opt->defname,"parallel")==0)
@@ -189,13 +192,13 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
189192
(analyze ?VACOPT_ANALYZE :0) |
190193
(freeze ?VACOPT_FREEZE :0) |
191194
(full ?VACOPT_FULL :0) |
192-
(disable_page_skipping ?VACOPT_DISABLE_PAGE_SKIPPING :0);
195+
(disable_page_skipping ?VACOPT_DISABLE_PAGE_SKIPPING :0) |
196+
(process_toast ?VACOPT_PROCESS_TOAST :0);
193197

194198
/* sanity checks on options */
195199
Assert(params.options& (VACOPT_VACUUM |VACOPT_ANALYZE));
196200
Assert((params.options&VACOPT_VACUUM)||
197201
!(params.options& (VACOPT_FULL |VACOPT_FREEZE)));
198-
Assert(!(params.options&VACOPT_SKIPTOAST));
199202

200203
if ((params.options&VACOPT_FULL)&&params.nworkers>0)
201204
ereport(ERROR,
@@ -318,6 +321,13 @@ vacuum(List *relations, VacuumParams *params,
318321
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
319322
errmsg("VACUUM option DISABLE_PAGE_SKIPPING cannot be used with FULL")));
320323

324+
/* sanity check for PROCESS_TOAST */
325+
if ((params->options&VACOPT_FULL)!=0&&
326+
(params->options&VACOPT_PROCESS_TOAST)==0)
327+
ereport(ERROR,
328+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
329+
errmsg("PROCESS_TOAST required with VACUUM FULL")));
330+
321331
/*
322332
* Send info about dead objects to the statistics collector, unless we are
323333
* in autovacuum --- autovacuum.c does this for itself.
@@ -1895,7 +1905,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
18951905
* us to process it. In VACUUM FULL, though, the toast table is
18961906
* automatically rebuilt by cluster_rel so we shouldn't recurse to it.
18971907
*/
1898-
if (!(params->options&VACOPT_SKIPTOAST)&& !(params->options&VACOPT_FULL))
1908+
if ((params->options&VACOPT_PROCESS_TOAST)!=0&&
1909+
(params->options&VACOPT_FULL)==0)
18991910
toast_relid=onerel->rd_rel->reltoastrelid;
19001911
else
19011912
toast_relid=InvalidOid;

‎src/backend/postmaster/autovacuum.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2918,8 +2918,9 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
29182918
tab=palloc(sizeof(autovac_table));
29192919
tab->at_relid=relid;
29202920
tab->at_sharedrel=classForm->relisshared;
2921-
tab->at_params.options=VACOPT_SKIPTOAST |
2922-
(dovacuum ?VACOPT_VACUUM :0) |
2921+
2922+
/* Note that this skips toast relations */
2923+
tab->at_params.options= (dovacuum ?VACOPT_VACUUM :0) |
29232924
(doanalyze ?VACOPT_ANALYZE :0) |
29242925
(!wraparound ?VACOPT_SKIP_LOCKED :0);
29252926
tab->at_params.index_cleanup=VACOPT_TERNARY_DEFAULT;

‎src/bin/psql/tab-complete.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3870,8 +3870,9 @@ psql_completion(const char *text, int start, int end)
38703870
if (ends_with(prev_wd,'(')||ends_with(prev_wd,','))
38713871
COMPLETE_WITH("FULL","FREEZE","ANALYZE","VERBOSE",
38723872
"DISABLE_PAGE_SKIPPING","SKIP_LOCKED",
3873-
"INDEX_CLEANUP","TRUNCATE","PARALLEL");
3874-
elseif (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE"))
3873+
"INDEX_CLEANUP","PROCESS_TOAST",
3874+
"TRUNCATE","PARALLEL");
3875+
elseif (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|PROCESS_TOAST|TRUNCATE"))
38753876
COMPLETE_WITH("ON","OFF");
38763877
}
38773878
elseif (HeadMatches("VACUUM")&&TailMatches("("))

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

Lines changed: 13 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33

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

88
program_help_ok('vacuumdb');
99
program_version_ok('vacuumdb');
@@ -56,12 +56,19 @@
5656
['vacuumdb','--analyze-only','--no-index-cleanup','postgres' ],
5757
'--analyze-only and --no-index-cleanup specified together');
5858
$node->issues_sql_like(
59-
['vacuumdb','--no-truncate','postgres' ],
60-
qr/statement: VACUUM\(TRUNCATE FALSE\).*;/,
61-
'vacuumdb --no-truncate');
59+
['vacuumdb','--no-truncate','postgres' ],
60+
qr/statement: VACUUM\(TRUNCATE FALSE\).*;/,
61+
'vacuumdb --no-truncate');
6262
$node->command_fails(
63-
['vacuumdb','--analyze-only','--no-truncate','postgres' ],
64-
'--analyze-only and --no-truncate specified together');
63+
['vacuumdb','--analyze-only','--no-truncate','postgres' ],
64+
'--analyze-only and --no-truncate specified together');
65+
$node->issues_sql_like(
66+
['vacuumdb','--no-process-toast','postgres' ],
67+
qr/statement: VACUUM\(PROCESS_TOAST FALSE\).*;/,
68+
'vacuumdb --no-process-toast');
69+
$node->command_fails(
70+
['vacuumdb','--analyze-only','--no-process-toast','postgres' ],
71+
'--analyze-only and --no-process-toast specified together');
6572
$node->issues_sql_like(
6673
['vacuumdb','-P', 2,'postgres' ],
6774
qr/statement: VACUUM\(PARALLEL 2\).*;/,

‎src/bin/scripts/vacuumdb.c

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,6 +41,7 @@ typedef struct vacuumingOptions
4141
* parallel degree, otherwise -1 */
4242
booldo_index_cleanup;
4343
booldo_truncate;
44+
boolprocess_toast;
4445
}vacuumingOptions;
4546

4647

@@ -99,6 +100,7 @@ main(int argc, char *argv[])
99100
{"min-mxid-age",required_argument,NULL,7},
100101
{"no-index-cleanup",no_argument,NULL,8},
101102
{"no-truncate",no_argument,NULL,9},
103+
{"no-process-toast",no_argument,NULL,10},
102104
{NULL,0,NULL,0}
103105
};
104106

@@ -126,6 +128,7 @@ main(int argc, char *argv[])
126128
vacopts.parallel_workers=-1;
127129
vacopts.do_index_cleanup= true;
128130
vacopts.do_truncate= true;
131+
vacopts.process_toast= true;
129132

130133
pg_logging_init(argv[0]);
131134
progname=get_progname(argv[0]);
@@ -235,6 +238,9 @@ main(int argc, char *argv[])
235238
case9:
236239
vacopts.do_truncate= false;
237240
break;
241+
case10:
242+
vacopts.process_toast= false;
243+
break;
238244
default:
239245
fprintf(stderr,_("Try \"%s --help\" for more information.\n"),progname);
240246
exit(1);
@@ -291,6 +297,12 @@ main(int argc, char *argv[])
291297
"no-truncate");
292298
exit(1);
293299
}
300+
if (!vacopts.process_toast)
301+
{
302+
pg_log_error("cannot use the \"%s\" option when performing only analyze",
303+
"no-process-toast");
304+
exit(1);
305+
}
294306
/* allow 'and_analyze' with 'analyze_only' */
295307
}
296308

@@ -456,6 +468,14 @@ vacuum_one_database(const ConnParams *cparams,
456468
exit(1);
457469
}
458470

471+
if (!vacopts->process_toast&&PQserverVersion(conn)<140000)
472+
{
473+
PQfinish(conn);
474+
pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
475+
"no-process-toast","14");
476+
exit(1);
477+
}
478+
459479
if (vacopts->skip_locked&&PQserverVersion(conn)<120000)
460480
{
461481
PQfinish(conn);
@@ -872,6 +892,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
872892
appendPQExpBuffer(sql,"%sTRUNCATE FALSE",sep);
873893
sep=comma;
874894
}
895+
if (!vacopts->process_toast)
896+
{
897+
/* PROCESS_TOAST is supported since v14 */
898+
Assert(serverVersion >=140000);
899+
appendPQExpBuffer(sql,"%sPROCESS_TOAST FALSE",sep);
900+
sep=comma;
901+
}
875902
if (vacopts->skip_locked)
876903
{
877904
/* SKIP_LOCKED is supported since v12 */
@@ -971,6 +998,7 @@ help(const char *progname)
971998
printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
972999
printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
9731000
printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
1001+
printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
9741002
printf(_(" --no-truncate don't truncate empty pages at the end of the table\n"));
9751003
printf(_(" -P, --parallel=PARALLEL_DEGREE use this many background workers for vacuum, if available\n"));
9761004
printf(_(" -q, --quiet don't write any messages\n"));

‎src/include/commands/vacuum.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -181,7 +181,7 @@ typedef struct VacAttrStats
181181
#defineVACOPT_FREEZE 0x08/* FREEZE option */
182182
#defineVACOPT_FULL 0x10/* FULL (non-concurrent) vacuum */
183183
#defineVACOPT_SKIP_LOCKED 0x20/* skip if cannot get lock */
184-
#defineVACOPT_SKIPTOAST 0x40/* don't process the TOAST table, if any */
184+
#defineVACOPT_PROCESS_TOAST 0x40/* process the TOAST table, if any */
185185
#defineVACOPT_DISABLE_PAGE_SKIPPING 0x80/* don't skip any pages */
186186

187187
/*

‎src/test/regress/expected/vacuum.out

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -252,6 +252,12 @@ RESET default_transaction_isolation;
252252
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
253253
ANALYZE vactst;
254254
COMMIT;
255+
-- PROCESS_TOAST option
256+
ALTER TABLE vactst ADD COLUMN t TEXT;
257+
ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
258+
VACUUM (PROCESS_TOAST FALSE) vactst;
259+
VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
260+
ERROR: PROCESS_TOAST required with VACUUM FULL
255261
DROP TABLE vaccluster;
256262
DROP TABLE vactst;
257263
DROP TABLE vacparted;

‎src/test/regress/sql/vacuum.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -213,6 +213,12 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
213213
ANALYZE vactst;
214214
COMMIT;
215215

216+
-- PROCESS_TOAST option
217+
ALTERTABLE vactst ADD COLUMN tTEXT;
218+
ALTERTABLE vactst ALTER COLUMN tSET STORAGE EXTERNAL;
219+
VACUUM (PROCESS_TOAST FALSE) vactst;
220+
VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
221+
216222
DROPTABLE vaccluster;
217223
DROPTABLE vactst;
218224
DROPTABLE vacparted;

0 commit comments

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