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

Commitb84dbc8

Browse files
committed
Add TRUNCATE parameter to VACUUM.
This commit adds new parameter to VACUUM command, TRUNCATE,which specifies that VACUUM should attempt to truncate offany empty pages at the end of the table and allow the disk spacefor the truncated pages to be returned to the operating system.This parameter, if specified, overrides the vacuum_truncatereloption. If neither the reloption nor the VACUUM option isused, the default is true, as before.Author: Fujii MasaoReviewed-by: Julien Rouhaud, Masahiko SawadaDiscussion:https://postgr.es/m/CAD21AoD+qtrSDL=GSma4Wd3kLYLeRC0hPna-YAdkDeV4z156vg@mail.gmail.com
1 parent98719af commitb84dbc8

File tree

9 files changed

+81
-10
lines changed

9 files changed

+81
-10
lines changed

‎doc/src/sgml/ref/create_table.sgml‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1418,7 +1418,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
14181418
Disabling index cleanup can speed up <command>VACUUM</command> very
14191419
significantly, but may also lead to severely bloated indexes if table
14201420
modifications are frequent. The <literal>INDEX_CLEANUP</literal>
1421-
parameterto <xref linkend="sql-vacuum"/>, if specified, overrides
1421+
parameterof <xref linkend="sql-vacuum"/>, if specified, overrides
14221422
the value of this option.
14231423
</para>
14241424
</listitem>
@@ -1438,7 +1438,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
14381438
autovacuum do the truncation and the disk space for
14391439
the truncated pages is returned to the operating system.
14401440
Note that the truncation requires <literal>ACCESS EXCLUSIVE</literal>
1441-
lock on the table.
1441+
lock on the table. The <literal>TRUNCATE</literal> parameter
1442+
of <xref linkend="sql-vacuum"/>, if specified, overrides the value
1443+
of this option.
14421444
</para>
14431445
</listitem>
14441446
</varlistentry>

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

Lines changed: 19 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+
TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
3637

3738
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
3839

@@ -204,6 +205,24 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
204205
</listitem>
205206
</varlistentry>
206207

208+
<varlistentry>
209+
<term><literal>TRUNCATE</literal></term>
210+
<listitem>
211+
<para>
212+
Specifies that <command>VACUUM</command> should attempt to
213+
truncate off any empty pages at the end of the table and allow
214+
the disk space for the truncated pages to be returned to
215+
the operating system. This is normally the desired behavior
216+
and is the default unless the <literal>vacuum_truncate</literal>
217+
option has been set to false for the table to be vacuumed.
218+
Setting this option to false may be useful to avoid
219+
<literal>ACCESS EXCLUSIVE</literal> lock on the table that
220+
the truncation requires. This option is ignored if the
221+
<literal>FULL</literal> option is used.
222+
</para>
223+
</listitem>
224+
</varlistentry>
225+
207226
<varlistentry>
208227
<term><replaceable class="parameter">boolean</replaceable></term>
209228
<listitem>

‎src/backend/access/heap/vacuumlazy.c‎

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -163,7 +163,8 @@ static void lazy_cleanup_index(Relation indrel,
163163
LVRelStats*vacrelstats);
164164
staticintlazy_vacuum_page(Relationonerel,BlockNumberblkno,Bufferbuffer,
165165
inttupindex,LVRelStats*vacrelstats,Buffer*vmbuffer);
166-
staticboolshould_attempt_truncation(Relationrel,LVRelStats*vacrelstats);
166+
staticboolshould_attempt_truncation(VacuumParams*params,
167+
LVRelStats*vacrelstats);
167168
staticvoidlazy_truncate_heap(Relationonerel,LVRelStats*vacrelstats);
168169
staticBlockNumbercount_nondeletable_pages(Relationonerel,
169170
LVRelStats*vacrelstats);
@@ -210,6 +211,7 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
210211

211212
Assert(params!=NULL);
212213
Assert(params->index_cleanup!=VACOPT_TERNARY_DEFAULT);
214+
Assert(params->truncate!=VACOPT_TERNARY_DEFAULT);
213215

214216
/* not every AM requires these to be valid, but heap does */
215217
Assert(TransactionIdIsNormal(onerel->rd_rel->relfrozenxid));
@@ -308,7 +310,7 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
308310
/*
309311
* Optionally truncate the relation.
310312
*/
311-
if (should_attempt_truncation(onerel,vacrelstats))
313+
if (should_attempt_truncation(params,vacrelstats))
312314
lazy_truncate_heap(onerel,vacrelstats);
313315

314316
/* Report that we are now doing final cleanup */
@@ -652,7 +654,7 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
652654

653655
/* see note above about forcing scanning of last page */
654656
#defineFORCE_CHECK_PAGE() \
655-
(blkno == nblocks - 1 && should_attempt_truncation(onerel, vacrelstats))
657+
(blkno == nblocks - 1 && should_attempt_truncation(params, vacrelstats))
656658

657659
pgstat_progress_update_param(PROGRESS_VACUUM_HEAP_BLKS_SCANNED,blkno);
658660

@@ -1845,12 +1847,11 @@ lazy_cleanup_index(Relation indrel,
18451847
* careful to depend only on fields that lazy_scan_heap updates on-the-fly.
18461848
*/
18471849
staticbool
1848-
should_attempt_truncation(Relationrel,LVRelStats*vacrelstats)
1850+
should_attempt_truncation(VacuumParams*params,LVRelStats*vacrelstats)
18491851
{
18501852
BlockNumberpossibly_freeable;
18511853

1852-
if (rel->rd_options!=NULL&&
1853-
((StdRdOptions*)rel->rd_options)->vacuum_truncate== false)
1854+
if (params->truncate==VACOPT_TERNARY_DISABLED)
18541855
return false;
18551856

18561857
possibly_freeable=vacrelstats->rel_pages-vacrelstats->nonempty_pages;

‎src/backend/commands/vacuum.c‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -98,6 +98,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
9898

9999
/* Set default value */
100100
params.index_cleanup=VACOPT_TERNARY_DEFAULT;
101+
params.truncate=VACOPT_TERNARY_DEFAULT;
101102

102103
/* Parse options list */
103104
foreach(lc,vacstmt->options)
@@ -126,6 +127,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
126127
disable_page_skipping=defGetBoolean(opt);
127128
elseif (strcmp(opt->defname,"index_cleanup")==0)
128129
params.index_cleanup=get_vacopt_ternary_value(opt);
130+
elseif (strcmp(opt->defname,"truncate")==0)
131+
params.truncate=get_vacopt_ternary_value(opt);
129132
else
130133
ereport(ERROR,
131134
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -1760,6 +1763,16 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
17601763
params->index_cleanup=VACOPT_TERNARY_DISABLED;
17611764
}
17621765

1766+
/* Set truncate option based on reloptions if not yet */
1767+
if (params->truncate==VACOPT_TERNARY_DEFAULT)
1768+
{
1769+
if (onerel->rd_options==NULL||
1770+
((StdRdOptions*)onerel->rd_options)->vacuum_truncate)
1771+
params->truncate=VACOPT_TERNARY_ENABLED;
1772+
else
1773+
params->truncate=VACOPT_TERNARY_DISABLED;
1774+
}
1775+
17631776
/*
17641777
* Remember the relation's TOAST relation for later, if the caller asked
17651778
* us to process it. In VACUUM FULL, though, the toast table is

‎src/backend/postmaster/autovacuum.c‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2887,6 +2887,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
28872887
(doanalyze ?VACOPT_ANALYZE :0) |
28882888
(!wraparound ?VACOPT_SKIP_LOCKED :0);
28892889
tab->at_params.index_cleanup=VACOPT_TERNARY_DEFAULT;
2890+
tab->at_params.truncate=VACOPT_TERNARY_DEFAULT;
28902891
tab->at_params.freeze_min_age=freeze_min_age;
28912892
tab->at_params.freeze_table_age=freeze_table_age;
28922893
tab->at_params.multixact_freeze_min_age=multixact_freeze_min_age;

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3466,8 +3466,8 @@ psql_completion(const char *text, int start, int end)
34663466
if (ends_with(prev_wd,'(')||ends_with(prev_wd,','))
34673467
COMPLETE_WITH("FULL","FREEZE","ANALYZE","VERBOSE",
34683468
"DISABLE_PAGE_SKIPPING","SKIP_LOCKED",
3469-
"INDEX_CLEANUP");
3470-
elseif (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP"))
3469+
"INDEX_CLEANUP","TRUNCATE");
3470+
elseif (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE"))
34713471
COMPLETE_WITH("ON","OFF");
34723472
}
34733473
elseif (HeadMatches("VACUUM")&&TailMatches("("))

‎src/include/commands/vacuum.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -182,6 +182,8 @@ typedef struct VacuumParams
182182
* to use default */
183183
VacOptTernaryValueindex_cleanup;/* Do index vacuum and cleanup,
184184
* default value depends on reloptions */
185+
VacOptTernaryValuetruncate;/* Truncate empty pages at the end,
186+
* default value depends on reloptions */
185187
}VacuumParams;
186188

187189
/* GUC parameters */

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -88,6 +88,28 @@ VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster;
8888
-- index cleanup option is ignored if VACUUM FULL
8989
VACUUM (INDEX_CLEANUP TRUE, FULL TRUE) no_index_cleanup;
9090
VACUUM (FULL TRUE) no_index_cleanup;
91+
-- TRUNCATE option
92+
CREATE TABLE vac_truncate_test(i INT NOT NULL, j text)
93+
WITH (vacuum_truncate=true, autovacuum_enabled=false);
94+
INSERT INTO vac_truncate_test VALUES (1, NULL), (NULL, NULL);
95+
ERROR: null value in column "i" violates not-null constraint
96+
DETAIL: Failing row contains (null, null).
97+
VACUUM (TRUNCATE FALSE) vac_truncate_test;
98+
SELECT pg_relation_size('vac_truncate_test') > 0;
99+
?column?
100+
----------
101+
t
102+
(1 row)
103+
104+
VACUUM vac_truncate_test;
105+
SELECT pg_relation_size('vac_truncate_test') = 0;
106+
?column?
107+
----------
108+
t
109+
(1 row)
110+
111+
VACUUM (TRUNCATE FALSE, FULL TRUE) vac_truncate_test;
112+
DROP TABLE vac_truncate_test;
91113
-- partitioned table
92114
CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a);
93115
CREATE TABLE vacparted1 PARTITION OF vacparted FOR VALUES IN (1);

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

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -71,6 +71,17 @@ VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster;
7171
VACUUM (INDEX_CLEANUP TRUE, FULL TRUE) no_index_cleanup;
7272
VACUUM (FULL TRUE) no_index_cleanup;
7373

74+
-- TRUNCATE option
75+
CREATETABLEvac_truncate_test(iINTNOT NULL, jtext)
76+
WITH (vacuum_truncate=true, autovacuum_enabled=false);
77+
INSERT INTO vac_truncate_testVALUES (1,NULL), (NULL,NULL);
78+
VACUUM (TRUNCATE FALSE) vac_truncate_test;
79+
SELECT pg_relation_size('vac_truncate_test')>0;
80+
VACUUM vac_truncate_test;
81+
SELECT pg_relation_size('vac_truncate_test')=0;
82+
VACUUM (TRUNCATE FALSE, FULL TRUE) vac_truncate_test;
83+
DROPTABLE vac_truncate_test;
84+
7485
-- partitioned table
7586
CREATETABLEvacparted (aint, bchar) PARTITION BY LIST (a);
7687
CREATETABLEvacparted1 PARTITION OF vacparted FORVALUESIN (1);

0 commit comments

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