Skip to content

Navigation Menu

Sign in

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
Appearance settings

Commit 4211fbd

Browse filesBrowse 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
Copy full SHA for 4211fbd

File tree

10 files changed

+156
-15
lines changed
Filter options

10 files changed

+156
-15
lines changed

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/vacuum.sgml
+13
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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/vacuumdb.sgml
+15
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

Copy file name to clipboardExpand all lines: src/backend/commands/vacuum.c
+22-6
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

Copy file name to clipboardExpand all lines: src/backend/postmaster/autovacuum.c
+3-1
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

Copy file name to clipboardExpand all lines: src/bin/psql/tab-complete.c
+2-2
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

Copy file name to clipboardExpand all lines: src/bin/scripts/t/100_vacuumdb.pl
+7
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

Copy file name to clipboardExpand all lines: src/bin/scripts/vacuumdb.c
+24
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

Copy file name to clipboardExpand all lines: src/include/commands/vacuum.h
+5-4
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

Copy file name to clipboardExpand all lines: src/test/regress/expected/vacuum.out
+43-1
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

Copy file name to clipboardExpand all lines: src/test/regress/sql/vacuum.sql
+22-1
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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.