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

Commit4211fbd

Browse files
committed
Add PROCESS_MAIN to VACUUM
Disabling this option is useful to run VACUUM (with or without FULL) ononly the toast table of a relation, bypassing the main relation. Thisoption is enabled by default.Running directly VACUUM on a toast table was already possible withoutthis feature, by using the non-deterministic name of a toast relation(as of pg_toast.pg_toast_N, where N would be the OID of the parentrelation) in the VACUUM command, and it required a scan of pg_class toknow the name of the toast table. So this feature is basically ashortcut to be able to run VACUUM or VACUUM FULL on a toast relation,using only the name of the parent relation.A new switch called --no-process-main is added to vacuumdb, to work asan equivalent of PROCESS_MAIN.Regression tests are added to cover VACUUM and VACUUM FULL, looking atpg_stat_all_tables.vacuum_count to see how many vacuums have run oneach table, main or toast.Author: Nathan BossartReviewed-by: Masahiko SawadaDiscussion:https://postgr.es/m/20221230000028.GA435655@nathanxps13
1 parent46d490a commit4211fbd

File tree

10 files changed

+156
-15
lines changed

10 files changed

+156
-15
lines changed

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

Lines changed: 13 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 { AUTO | ON | OFF }
36+
PROCESS_MAIN [ <replaceable class="parameter">boolean</replaceable> ]
3637
PROCESS_TOAST [ <replaceable class="parameter">boolean</replaceable> ]
3738
TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
3839
PARALLEL <replaceable class="parameter">integer</replaceable>
@@ -238,6 +239,18 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
238239
</listitem>
239240
</varlistentry>
240241

242+
<varlistentry>
243+
<term><literal>PROCESS_MAIN</literal></term>
244+
<listitem>
245+
<para>
246+
Specifies that <command>VACUUM</command> should attempt to process the
247+
main relation. This is usually the desired behavior and is the default.
248+
Setting this option to false may be useful when it is only necessary to
249+
vacuum a relation's corresponding <literal>TOAST</literal> table.
250+
</para>
251+
</listitem>
252+
</varlistentry>
253+
241254
<varlistentry>
242255
<term><literal>PROCESS_TOAST</literal></term>
243256
<listitem>

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -317,6 +317,21 @@ PostgreSQL documentation
317317
</listitem>
318318
</varlistentry>
319319

320+
<varlistentry>
321+
<term><option>--no-process-main</option></term>
322+
<listitem>
323+
<para>
324+
Skip the main relation.
325+
</para>
326+
<note>
327+
<para>
328+
This option is only available for servers running
329+
<productname>PostgreSQL</productname> 16 and later.
330+
</para>
331+
</note>
332+
</listitem>
333+
</varlistentry>
334+
320335
<varlistentry>
321336
<term><option>--no-process-toast</option></term>
322337
<listitem>

‎src/backend/commands/vacuum.c‎

Lines changed: 22 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -115,6 +115,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
115115
boolfreeze= false;
116116
boolfull= false;
117117
booldisable_page_skipping= false;
118+
boolprocess_main= true;
118119
boolprocess_toast= true;
119120
boolskip_database_stats= false;
120121
boolonly_database_stats= false;
@@ -168,6 +169,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
168169
params.index_cleanup=get_vacoptval_from_boolean(opt);
169170
}
170171
}
172+
elseif (strcmp(opt->defname,"process_main")==0)
173+
process_main=defGetBoolean(opt);
171174
elseif (strcmp(opt->defname,"process_toast")==0)
172175
process_toast=defGetBoolean(opt);
173176
elseif (strcmp(opt->defname,"truncate")==0)
@@ -224,6 +227,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
224227
(freeze ?VACOPT_FREEZE :0) |
225228
(full ?VACOPT_FULL :0) |
226229
(disable_page_skipping ?VACOPT_DISABLE_PAGE_SKIPPING :0) |
230+
(process_main ?VACOPT_PROCESS_MAIN :0) |
227231
(process_toast ?VACOPT_PROCESS_TOAST :0) |
228232
(skip_database_stats ?VACOPT_SKIP_DATABASE_STATS :0) |
229233
(only_database_stats ?VACOPT_ONLY_DATABASE_STATS :0);
@@ -367,9 +371,10 @@ vacuum(List *relations, VacuumParams *params,
367371
ereport(ERROR,
368372
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
369373
errmsg("ONLY_DATABASE_STATS cannot be specified with a list of tables")));
370-
/* don't require people to turn off PROCESS_TOAST explicitly */
374+
/* don't require people to turn off PROCESS_TOAST/MAIN explicitly */
371375
if (params->options& ~(VACOPT_VACUUM |
372376
VACOPT_VERBOSE |
377+
VACOPT_PROCESS_MAIN |
373378
VACOPT_PROCESS_TOAST |
374379
VACOPT_ONLY_DATABASE_STATS))
375380
ereport(ERROR,
@@ -2031,10 +2036,12 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs)
20312036
/*
20322037
* Remember the relation's TOAST relation for later, if the caller asked
20332038
* us to process it. In VACUUM FULL, though, the toast table is
2034-
* automatically rebuilt by cluster_rel so we shouldn't recurse to it.
2039+
* automatically rebuilt by cluster_rel so we shouldn't recurse to it,
2040+
* unless PROCESS_MAIN is disabled.
20352041
*/
20362042
if ((params->options&VACOPT_PROCESS_TOAST)!=0&&
2037-
(params->options&VACOPT_FULL)==0)
2043+
((params->options&VACOPT_FULL)==0||
2044+
(params->options&VACOPT_PROCESS_MAIN)==0))
20382045
toast_relid=rel->rd_rel->reltoastrelid;
20392046
else
20402047
toast_relid=InvalidOid;
@@ -2053,7 +2060,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs)
20532060
/*
20542061
* Do the actual work --- either FULL or "lazy" vacuum
20552062
*/
2056-
if (params->options&VACOPT_FULL)
2063+
if ((params->options&VACOPT_FULL)&&
2064+
(params->options&VACOPT_PROCESS_MAIN))
20572065
{
20582066
ClusterParamscluster_params= {0};
20592067

@@ -2067,7 +2075,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs)
20672075
/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
20682076
cluster_rel(relid,InvalidOid,&cluster_params);
20692077
}
2070-
else
2078+
elseif (params->options&VACOPT_PROCESS_MAIN)
20712079
table_relation_vacuum(rel,params,vac_strategy);
20722080

20732081
/* Roll back any GUC changes executed by index functions */
@@ -2094,7 +2102,15 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs)
20942102
* totally unimportant for toast relations.
20952103
*/
20962104
if (toast_relid!=InvalidOid)
2097-
vacuum_rel(toast_relid,NULL,params, true);
2105+
{
2106+
VacuumParamstoast_vacuum_params;
2107+
2108+
/* force VACOPT_PROCESS_MAIN so vacuum_rel() processes it */
2109+
memcpy(&toast_vacuum_params,params,sizeof(VacuumParams));
2110+
toast_vacuum_params.options |=VACOPT_PROCESS_MAIN;
2111+
2112+
vacuum_rel(toast_relid,NULL,&toast_vacuum_params, true);
2113+
}
20982114

20992115
/*
21002116
* Now release the session-level lock on the main table.

‎src/backend/postmaster/autovacuum.c‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2860,7 +2860,9 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
28602860
* skip vac_update_datfrozenxid(); we'll do that separately.
28612861
*/
28622862
tab->at_params.options=
2863-
(dovacuum ? (VACOPT_VACUUM |VACOPT_SKIP_DATABASE_STATS) :0) |
2863+
(dovacuum ? (VACOPT_VACUUM |
2864+
VACOPT_PROCESS_MAIN |
2865+
VACOPT_SKIP_DATABASE_STATS) :0) |
28642866
(doanalyze ?VACOPT_ANALYZE :0) |
28652867
(!wraparound ?VACOPT_SKIP_LOCKED :0);
28662868

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4618,10 +4618,10 @@ psql_completion(const char *text, int start, int end)
46184618
if (ends_with(prev_wd,'(')||ends_with(prev_wd,','))
46194619
COMPLETE_WITH("FULL","FREEZE","ANALYZE","VERBOSE",
46204620
"DISABLE_PAGE_SKIPPING","SKIP_LOCKED",
4621-
"INDEX_CLEANUP","PROCESS_TOAST",
4621+
"INDEX_CLEANUP","PROCESS_MAIN","PROCESS_TOAST",
46224622
"TRUNCATE","PARALLEL","SKIP_DATABASE_STATS",
46234623
"ONLY_DATABASE_STATS");
4624-
elseif (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_TOAST|TRUNCATE|SKIP_DATABASE_STATS|ONLY_DATABASE_STATS"))
4624+
elseif (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_MAIN|PROCESS_TOAST|TRUNCATE|SKIP_DATABASE_STATS|ONLY_DATABASE_STATS"))
46254625
COMPLETE_WITH("ON","OFF");
46264626
elseif (TailMatches("INDEX_CLEANUP"))
46274627
COMPLETE_WITH("AUTO","ON","OFF");

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -65,6 +65,13 @@
6565
$node->command_fails(
6666
['vacuumdb','--analyze-only','--no-truncate','postgres' ],
6767
'--analyze-only and --no-truncate specified together');
68+
$node->issues_sql_like(
69+
['vacuumdb','--no-process-main','postgres' ],
70+
qr/statement: VACUUM\(PROCESS_MAIN FALSE, SKIP_DATABASE_STATS\).*;/,
71+
'vacuumdb --no-process-main');
72+
$node->command_fails(
73+
['vacuumdb','--analyze-only','--no-process-main','postgres' ],
74+
'--analyze-only and --no-process_main specified together');
6875
$node->issues_sql_like(
6976
['vacuumdb','--no-process-toast','postgres' ],
7077
qr/statement: VACUUM\(PROCESS_TOAST FALSE, SKIP_DATABASE_STATS\).*;/,

‎src/bin/scripts/vacuumdb.c‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -43,6 +43,7 @@ typedef struct vacuumingOptions
4343
boolno_index_cleanup;
4444
boolforce_index_cleanup;
4545
booldo_truncate;
46+
boolprocess_main;
4647
boolprocess_toast;
4748
boolskip_database_stats;
4849
}vacuumingOptions;
@@ -121,6 +122,7 @@ main(int argc, char *argv[])
121122
{"force-index-cleanup",no_argument,NULL,9},
122123
{"no-truncate",no_argument,NULL,10},
123124
{"no-process-toast",no_argument,NULL,11},
125+
{"no-process-main",no_argument,NULL,12},
124126
{NULL,0,NULL,0}
125127
};
126128

@@ -148,6 +150,7 @@ main(int argc, char *argv[])
148150
vacopts.no_index_cleanup= false;
149151
vacopts.force_index_cleanup= false;
150152
vacopts.do_truncate= true;
153+
vacopts.process_main= true;
151154
vacopts.process_toast= true;
152155

153156
pg_logging_init(argv[0]);
@@ -260,6 +263,9 @@ main(int argc, char *argv[])
260263
case11:
261264
vacopts.process_toast= false;
262265
break;
266+
case12:
267+
vacopts.process_main= false;
268+
break;
263269
default:
264270
/* getopt_long already emitted a complaint */
265271
pg_log_error_hint("Try \"%s --help\" for more information.",progname);
@@ -312,6 +318,9 @@ main(int argc, char *argv[])
312318
if (!vacopts.do_truncate)
313319
pg_fatal("cannot use the \"%s\" option when performing only analyze",
314320
"no-truncate");
321+
if (!vacopts.process_main)
322+
pg_fatal("cannot use the \"%s\" option when performing only analyze",
323+
"no-process-main");
315324
if (!vacopts.process_toast)
316325
pg_fatal("cannot use the \"%s\" option when performing only analyze",
317326
"no-process-toast");
@@ -508,6 +517,13 @@ vacuum_one_database(ConnParams *cparams,
508517
"no-truncate","12");
509518
}
510519

520+
if (!vacopts->process_main&&PQserverVersion(conn)<160000)
521+
{
522+
PQfinish(conn);
523+
pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
524+
"no-process-main","16");
525+
}
526+
511527
if (!vacopts->process_toast&&PQserverVersion(conn)<140000)
512528
{
513529
PQfinish(conn);
@@ -976,6 +992,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
976992
appendPQExpBuffer(sql,"%sTRUNCATE FALSE",sep);
977993
sep=comma;
978994
}
995+
if (!vacopts->process_main)
996+
{
997+
/* PROCESS_MAIN is supported since v16 */
998+
Assert(serverVersion >=160000);
999+
appendPQExpBuffer(sql,"%sPROCESS_MAIN FALSE",sep);
1000+
sep=comma;
1001+
}
9791002
if (!vacopts->process_toast)
9801003
{
9811004
/* PROCESS_TOAST is supported since v14 */
@@ -1090,6 +1113,7 @@ help(const char *progname)
10901113
printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
10911114
printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
10921115
printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
1116+
printf(_(" --no-process-main skip the main relation\n"));
10931117
printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
10941118
printf(_(" --no-truncate don't truncate empty pages at the end of the table\n"));
10951119
printf(_(" -n, --schema=PATTERN vacuum tables in the specified schema(s) only\n"));

‎src/include/commands/vacuum.h‎

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -186,10 +186,11 @@ typedef struct VacAttrStats
186186
#defineVACOPT_FREEZE 0x08/* FREEZE option */
187187
#defineVACOPT_FULL 0x10/* FULL (non-concurrent) vacuum */
188188
#defineVACOPT_SKIP_LOCKED 0x20/* skip if cannot get lock */
189-
#defineVACOPT_PROCESS_TOAST 0x40/* process the TOAST table, if any */
190-
#defineVACOPT_DISABLE_PAGE_SKIPPING 0x80/* don't skip any pages */
191-
#defineVACOPT_SKIP_DATABASE_STATS 0x100/* skip vac_update_datfrozenxid() */
192-
#defineVACOPT_ONLY_DATABASE_STATS 0x200/* only vac_update_datfrozenxid() */
189+
#defineVACOPT_PROCESS_MAIN 0x40/* process main relation */
190+
#defineVACOPT_PROCESS_TOAST 0x80/* process the TOAST table, if any */
191+
#defineVACOPT_DISABLE_PAGE_SKIPPING 0x100/* don't skip any pages */
192+
#defineVACOPT_SKIP_DATABASE_STATS 0x200/* skip vac_update_datfrozenxid() */
193+
#defineVACOPT_ONLY_DATABASE_STATS 0x400/* only vac_update_datfrozenxid() */
193194

194195
/*
195196
* Values used by index_cleanup and truncate params.

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

Lines changed: 43 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -281,7 +281,8 @@ CREATE TABLE vac_option_tab (a INT, t TEXT);
281281
INSERT INTO vac_option_tab SELECT a, 't' || a FROM generate_series(1, 10) AS a;
282282
ALTER TABLE vac_option_tab ALTER COLUMN t SET STORAGE EXTERNAL;
283283
-- Check the number of vacuums done on table vac_option_tab and on its
284-
-- toast relation, to check that PROCESS_TOAST works on what it should.
284+
-- toast relation, to check that PROCESS_TOAST and PROCESS_MAIN work on
285+
-- what they should.
285286
CREATE VIEW vac_option_tab_counts AS
286287
SELECT CASE WHEN c.relname IS NULL
287288
THEN 'main' ELSE 'toast' END as rel,
@@ -308,6 +309,47 @@ SELECT * FROM vac_option_tab_counts;
308309

309310
VACUUM (PROCESS_TOAST FALSE, FULL) vac_option_tab; -- error
310311
ERROR: PROCESS_TOAST required with VACUUM FULL
312+
-- PROCESS_MAIN option
313+
-- Only the toast table is processed.
314+
VACUUM (PROCESS_MAIN FALSE) vac_option_tab;
315+
SELECT * FROM vac_option_tab_counts;
316+
rel | vacuum_count
317+
-------+--------------
318+
main | 2
319+
toast | 2
320+
(2 rows)
321+
322+
-- Nothing is processed.
323+
VACUUM (PROCESS_MAIN FALSE, PROCESS_TOAST FALSE) vac_option_tab;
324+
SELECT * FROM vac_option_tab_counts;
325+
rel | vacuum_count
326+
-------+--------------
327+
main | 2
328+
toast | 2
329+
(2 rows)
330+
331+
-- Check if the filenodes nodes have been updated as wanted after FULL.
332+
SELECT relfilenode AS main_filenode FROM pg_class
333+
WHERE relname = 'vac_option_tab' \gset
334+
SELECT t.relfilenode AS toast_filenode FROM pg_class c, pg_class t
335+
WHERE c.reltoastrelid = t.oid AND c.relname = 'vac_option_tab' \gset
336+
-- Only the toast relation is processed.
337+
VACUUM (PROCESS_MAIN FALSE, FULL) vac_option_tab;
338+
SELECT relfilenode = :main_filenode AS is_same_main_filenode
339+
FROM pg_class WHERE relname = 'vac_option_tab';
340+
is_same_main_filenode
341+
-----------------------
342+
t
343+
(1 row)
344+
345+
SELECT t.relfilenode = :toast_filenode AS is_same_toast_filenode
346+
FROM pg_class c, pg_class t
347+
WHERE c.reltoastrelid = t.oid AND c.relname = 'vac_option_tab';
348+
is_same_toast_filenode
349+
------------------------
350+
f
351+
(1 row)
352+
311353
-- SKIP_DATABASE_STATS option
312354
VACUUM (SKIP_DATABASE_STATS) vactst;
313355
-- ONLY_DATABASE_STATS option

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

Lines changed: 22 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -236,7 +236,8 @@ CREATE TABLE vac_option_tab (a INT, t TEXT);
236236
INSERT INTO vac_option_tabSELECT a,'t'|| aFROM generate_series(1,10)AS a;
237237
ALTERTABLE vac_option_tab ALTER COLUMN tSET STORAGE EXTERNAL;
238238
-- Check the number of vacuums done on table vac_option_tab and on its
239-
-- toast relation, to check that PROCESS_TOAST works on what it should.
239+
-- toast relation, to check that PROCESS_TOAST and PROCESS_MAIN work on
240+
-- what they should.
240241
CREATEVIEWvac_option_tab_countsAS
241242
SELECT CASE WHENc.relname ISNULL
242243
THEN'main' ELSE'toast' ENDas rel,
@@ -251,6 +252,26 @@ VACUUM (PROCESS_TOAST FALSE) vac_option_tab;
251252
SELECT*FROM vac_option_tab_counts;
252253
VACUUM (PROCESS_TOAST FALSE, FULL) vac_option_tab;-- error
253254

255+
-- PROCESS_MAIN option
256+
-- Only the toast table is processed.
257+
VACUUM (PROCESS_MAIN FALSE) vac_option_tab;
258+
SELECT*FROM vac_option_tab_counts;
259+
-- Nothing is processed.
260+
VACUUM (PROCESS_MAIN FALSE, PROCESS_TOAST FALSE) vac_option_tab;
261+
SELECT*FROM vac_option_tab_counts;
262+
-- Check if the filenodes nodes have been updated as wanted after FULL.
263+
SELECT relfilenodeAS main_filenodeFROM pg_class
264+
WHERE relname='vac_option_tab' \gset
265+
SELECTt.relfilenodeAS toast_filenodeFROM pg_class c, pg_class t
266+
WHEREc.reltoastrelid=t.oidANDc.relname='vac_option_tab' \gset
267+
-- Only the toast relation is processed.
268+
VACUUM (PROCESS_MAIN FALSE, FULL) vac_option_tab;
269+
SELECT relfilenode= :main_filenodeAS is_same_main_filenode
270+
FROM pg_classWHERE relname='vac_option_tab';
271+
SELECTt.relfilenode= :toast_filenodeAS is_same_toast_filenode
272+
FROM pg_class c, pg_class t
273+
WHEREc.reltoastrelid=t.oidANDc.relname='vac_option_tab';
274+
254275
-- SKIP_DATABASE_STATS option
255276
VACUUM (SKIP_DATABASE_STATS) vactst;
256277

0 commit comments

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