Skip to content

Commit 4211fbd

Browse files
michaelpqmichaelpq
committed
Add PROCESS_MAIN to VACUUM
Disabling this option is useful to run VACUUM (with or without FULL) on only the toast table of a relation, bypassing the main relation. This option is enabled by default. Running directly VACUUM on a toast table was already possible without this 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 parent relation) in the VACUUM command, and it required a scan of pg_class to know the name of the toast table. So this feature is basically a shortcut 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 as an equivalent of PROCESS_MAIN. Regression tests are added to cover VACUUM and VACUUM FULL, looking at pg_stat_all_tables.vacuum_count to see how many vacuums have run on each table, main or toast. Author: Nathan Bossart Reviewed-by: Masahiko Sawada Discussion: https://postgr.es/m/20221230000028.GA435655@nathanxps13
1 parent 46d490a commit 4211fbd

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
bool freeze = false;
116116
bool full = false;
117117
bool disable_page_skipping = false;
118+
bool process_main = true;
118119
bool process_toast = true;
119120
bool skip_database_stats = false;
120121
bool only_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+
else if (strcmp(opt->defname, "process_main") == 0)
173+
process_main = defGetBoolean(opt);
171174
else if (strcmp(opt->defname, "process_toast") == 0)
172175
process_toast = defGetBoolean(opt);
173176
else if (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
ClusterParams cluster_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+
else if (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+
VacuumParams toast_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-
else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_TOAST|TRUNCATE|SKIP_DATABASE_STATS|ONLY_DATABASE_STATS"))
4624+
else if (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
else if (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
bool no_index_cleanup;
4444
bool force_index_cleanup;
4545
bool do_truncate;
46+
bool process_main;
4647
bool process_toast;
4748
bool skip_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
case 11:
261264
vacopts.process_toast = false;
262265
break;
266+
case 12:
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
#define VACOPT_FREEZE 0x08 /* FREEZE option */
187187
#define VACOPT_FULL 0x10 /* FULL (non-concurrent) vacuum */
188188
#define VACOPT_SKIP_LOCKED 0x20 /* skip if cannot get lock */
189-
#define VACOPT_PROCESS_TOAST 0x40 /* process the TOAST table, if any */
190-
#define VACOPT_DISABLE_PAGE_SKIPPING 0x80 /* don't skip any pages */
191-
#define VACOPT_SKIP_DATABASE_STATS 0x100 /* skip vac_update_datfrozenxid() */
192-
#define VACOPT_ONLY_DATABASE_STATS 0x200 /* only vac_update_datfrozenxid() */
189+
#define VACOPT_PROCESS_MAIN 0x40 /* process main relation */
190+
#define VACOPT_PROCESS_TOAST 0x80 /* process the TOAST table, if any */
191+
#define VACOPT_DISABLE_PAGE_SKIPPING 0x100 /* don't skip any pages */
192+
#define VACOPT_SKIP_DATABASE_STATS 0x200 /* skip vac_update_datfrozenxid() */
193+
#define VACOPT_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_tab SELECT a, 't' || a FROM generate_series(1, 10) AS a;
237237
ALTER TABLE vac_option_tab ALTER COLUMN t SET 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
CREATE VIEW vac_option_tab_counts AS
241242
SELECT CASE WHEN c.relname IS NULL
242243
THEN 'main' ELSE 'toast' END as 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 relfilenode AS main_filenode FROM pg_class
264+
WHERE relname = 'vac_option_tab' \gset
265+
SELECT t.relfilenode AS toast_filenode FROM pg_class c, pg_class t
266+
WHERE c.reltoastrelid = t.oid AND c.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_filenode AS is_same_main_filenode
270+
FROM pg_class WHERE relname = 'vac_option_tab';
271+
SELECT t.relfilenode = :toast_filenode AS is_same_toast_filenode
272+
FROM pg_class c, pg_class t
273+
WHERE c.reltoastrelid = t.oid AND c.relname = 'vac_option_tab';
274+
254275
-- SKIP_DATABASE_STATS option
255276
VACUUM (SKIP_DATABASE_STATS) vactst;
256277

0 commit comments

Comments
 (0)