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

Commit0164a0f

Browse files
Add vacuum_truncate configuration parameter.
This new parameter works just like the storage parameter of thesame name: if set to true (which is the default), autovacuum andVACUUM attempt to truncate any empty pages at the end of the table.It is primarily intended to help users avoid locking issues on hotstandbys. The setting can be overridden with the storage parameteror VACUUM's TRUNCATE option.Since there's presently no way to determine whether a Booleanstorage parameter is explicitly set or has just picked up thedefault value, this commit also introduces an isset_offset memberto relopt_parse_elt.Suggested-by: Will Storey <will@summercat.com>Author: Nathan Bossart <nathandbossart@gmail.com>Co-authored-by: Gurjeet Singh <gurjeet@singh.im>Reviewed-by: Laurenz Albe <laurenz.albe@cybertec.at>Reviewed-by: Fujii Masao <masao.fujii@oss.nttdata.com>Reviewed-by: Robert Treat <rob@xzilla.net>Discussion:https://postgr.es/m/Z2DE4lDX4tHqNGZt%40dev.null
1 parent618c64f commit0164a0f

File tree

13 files changed

+116
-15
lines changed

13 files changed

+116
-15
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9311,6 +9311,35 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
93119311
</note>
93129312
</sect2>
93139313

9314+
<sect2 id="runtime-config-vacuum-default">
9315+
<title>Default Behavior</title>
9316+
9317+
<variablelist>
9318+
<varlistentry id="guc-vacuum-truncate" xreflabel="vacuum_truncate">
9319+
<term><varname>vacuum_truncate</varname> (<type>boolean</type>)
9320+
<indexterm>
9321+
<primary><varname>vacuum_truncate</varname> configuration parameter</primary>
9322+
</indexterm>
9323+
</term>
9324+
<listitem>
9325+
<para>
9326+
Enables or disables vacuum to try to truncate off any empty pages at
9327+
the end of the table. The default value is <literal>true</literal>.
9328+
If <literal>true</literal>, <command>VACUUM</command> and autovacuum
9329+
do the truncation and the disk space for the truncated pages is
9330+
returned to the operating system. Note that the truncation requires
9331+
an <literal>ACCESS EXCLUSIVE</literal> lock on the table. The
9332+
<literal>TRUNCATE</literal> parameter of
9333+
<link linkend="sql-vacuum"><command>VACUUM</command></link>, if
9334+
specified, overrides the value of this parameter. The setting can
9335+
also be overridden for individual tables by changing table storage
9336+
parameters.
9337+
</para>
9338+
</listitem>
9339+
</varlistentry>
9340+
</variablelist>
9341+
</sect2>
9342+
93149343
<sect2 id="runtime-config-vacuum-freezing">
93159344
<title>Freezing</title>
93169345

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

Lines changed: 4 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1692,15 +1692,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
16921692
</term>
16931693
<listitem>
16941694
<para>
1695-
Enables or disables vacuum to try to truncate off any empty pages
1696-
at the end of this table. The default value is <literal>true</literal>.
1697-
If <literal>true</literal>, <command>VACUUM</command> and
1698-
autovacuum do the truncation and the disk space for
1699-
the truncated pages is returned to the operating system.
1700-
Note that the truncation requires <literal>ACCESS EXCLUSIVE</literal>
1701-
lock on the table. The <literal>TRUNCATE</literal> parameter
1702-
of <link linkend="sql-vacuum"><command>VACUUM</command></link>, if specified, overrides the value
1703-
of this option.
1695+
Per-table value for <xref linkend="guc-vacuum-truncate"/> parameter. The
1696+
<literal>TRUNCATE</literal> parameter of
1697+
<link linkend="sql-vacuum"><command>VACUUM</command></link>, if
1698+
specified, overrides the value of this option.
17041699
</para>
17051700
</listitem>
17061701
</varlistentry>

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -265,7 +265,8 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
265265
truncate off any empty pages at the end of the table and allow
266266
the disk space for the truncated pages to be returned to
267267
the operating system. This is normally the desired behavior
268-
and is the default unless the <literal>vacuum_truncate</literal>
268+
and is the default unless <xref linkend="guc-vacuum-truncate"/>
269+
is set to false or the <literal>vacuum_truncate</literal>
269270
option has been set to false for the table to be vacuumed.
270271
Setting this option to false may be useful to avoid
271272
<literal>ACCESS EXCLUSIVE</literal> lock on the table that

‎src/backend/access/common/reloptions.c

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1779,6 +1779,17 @@ fillRelOptions(void *rdopts, Size basesize,
17791779
char*itempos= ((char*)rdopts)+elems[j].offset;
17801780
char*string_val;
17811781

1782+
/*
1783+
* If isset_offset is provided, store whether the reloption is
1784+
* set there.
1785+
*/
1786+
if (elems[j].isset_offset>0)
1787+
{
1788+
char*setpos= ((char*)rdopts)+elems[j].isset_offset;
1789+
1790+
*(bool*)setpos=options[i].isset;
1791+
}
1792+
17821793
switch (options[i].gen->type)
17831794
{
17841795
caseRELOPT_TYPE_BOOL:
@@ -1901,7 +1912,7 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
19011912
{"vacuum_index_cleanup",RELOPT_TYPE_ENUM,
19021913
offsetof(StdRdOptions,vacuum_index_cleanup)},
19031914
{"vacuum_truncate",RELOPT_TYPE_BOOL,
1904-
offsetof(StdRdOptions,vacuum_truncate)},
1915+
offsetof(StdRdOptions,vacuum_truncate), offsetof(StdRdOptions,vacuum_truncate_set)},
19051916
{"vacuum_max_eager_freeze_failure_rate",RELOPT_TYPE_REAL,
19061917
offsetof(StdRdOptions,vacuum_max_eager_freeze_failure_rate)}
19071918
};
@@ -1981,6 +1992,7 @@ build_local_reloptions(local_relopts *relopts, Datum options, bool validate)
19811992
elems[i].optname=opt->option->name;
19821993
elems[i].opttype=opt->option->type;
19831994
elems[i].offset=opt->offset;
1995+
elems[i].isset_offset=0;/* not supported for local relopts yet */
19841996

19851997
i++;
19861998
}

‎src/backend/commands/vacuum.c

Lines changed: 13 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,7 @@ intvacuum_failsafe_age;
7878
intvacuum_multixact_failsafe_age;
7979
doublevacuum_max_eager_freeze_failure_rate;
8080
booltrack_cost_delay_timing;
81+
boolvacuum_truncate;
8182

8283
/*
8384
* Variables for cost-based vacuum delay. The defaults differ between
@@ -2198,13 +2199,21 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params,
21982199
((StdRdOptions*)rel->rd_options)->vacuum_max_eager_freeze_failure_rate;
21992200

22002201
/*
2201-
* Set truncate option based on truncate reloption if it wasn't specified
2202-
* in VACUUM command, or when running in an autovacuum worker
2202+
* Set truncate option based on truncate reloptionor GUCif it wasn't
2203+
*specifiedin VACUUM command, or when running in an autovacuum worker
22032204
*/
22042205
if (params->truncate==VACOPTVALUE_UNSPECIFIED)
22052206
{
2206-
if (rel->rd_options==NULL||
2207-
((StdRdOptions*)rel->rd_options)->vacuum_truncate)
2207+
StdRdOptions*opts= (StdRdOptions*)rel->rd_options;
2208+
2209+
if (opts&&opts->vacuum_truncate_set)
2210+
{
2211+
if (opts->vacuum_truncate)
2212+
params->truncate=VACOPTVALUE_ENABLED;
2213+
else
2214+
params->truncate=VACOPTVALUE_DISABLED;
2215+
}
2216+
elseif (vacuum_truncate)
22082217
params->truncate=VACOPTVALUE_ENABLED;
22092218
else
22102219
params->truncate=VACOPTVALUE_DISABLED;

‎src/backend/utils/misc/guc_tables.c

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -712,6 +712,7 @@ const char *const config_group_names[] =
712712
[STATS_CUMULATIVE]=gettext_noop("Statistics / Cumulative Query and Index Statistics"),
713713
[VACUUM_AUTOVACUUM]=gettext_noop("Vacuuming / Automatic Vacuuming"),
714714
[VACUUM_COST_DELAY]=gettext_noop("Vacuuming / Cost-Based Vacuum Delay"),
715+
[VACUUM_DEFAULT]=gettext_noop("Vacuuming / Default Behavior"),
715716
[VACUUM_FREEZING]=gettext_noop("Vacuuming / Freezing"),
716717
[CLIENT_CONN_STATEMENT]=gettext_noop("Client Connection Defaults / Statement Behavior"),
717718
[CLIENT_CONN_LOCALE]=gettext_noop("Client Connection Defaults / Locale and Formatting"),
@@ -2131,6 +2132,15 @@ struct config_bool ConfigureNamesBool[] =
21312132
NULL,NULL,NULL
21322133
},
21332134

2135+
{
2136+
{"vacuum_truncate",PGC_USERSET,VACUUM_DEFAULT,
2137+
gettext_noop("Enables vacuum to truncate empty pages at the end of the table."),
2138+
},
2139+
&vacuum_truncate,
2140+
true,
2141+
NULL,NULL,NULL
2142+
},
2143+
21342144
/* End-of-list marker */
21352145
{
21362146
{NULL,0,0,NULL,NULL},NULL, false,NULL,NULL,NULL

‎src/backend/utils/misc/postgresql.conf.sample

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -714,6 +714,10 @@ autovacuum_worker_slots = 16# autovacuum worker slots to allocate
714714
#vacuum_cost_page_dirty = 20# 0-10000 credits
715715
#vacuum_cost_limit = 200# 1-10000 credits
716716

717+
# - Default Behavior -
718+
719+
#vacuum_truncate = on# enable truncation after vacuum
720+
717721
# - Freezing -
718722

719723
#vacuum_freeze_table_age = 150000000

‎src/include/access/reloptions.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -152,6 +152,7 @@ typedef struct
152152
constchar*optname;/* option's name */
153153
relopt_typeopttype;/* option's datatype */
154154
intoffset;/* offset of field in result struct */
155+
intisset_offset;/* if > 0, offset of "is set" field */
155156
}relopt_parse_elt;
156157

157158
/* Local reloption definition */

‎src/include/commands/vacuum.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -304,6 +304,7 @@ extern PGDLLIMPORT int vacuum_multixact_freeze_table_age;
304304
externPGDLLIMPORTintvacuum_failsafe_age;
305305
externPGDLLIMPORTintvacuum_multixact_failsafe_age;
306306
externPGDLLIMPORTbooltrack_cost_delay_timing;
307+
externPGDLLIMPORTboolvacuum_truncate;
307308

308309
/*
309310
* Relevant for vacuums implementing eager scanning. Normal vacuums may

‎src/include/utils/guc_tables.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -89,6 +89,7 @@ enum config_group
8989
STATS_CUMULATIVE,
9090
VACUUM_AUTOVACUUM,
9191
VACUUM_COST_DELAY,
92+
VACUUM_DEFAULT,
9293
VACUUM_FREEZING,
9394
CLIENT_CONN_STATEMENT,
9495
CLIENT_CONN_LOCALE,

‎src/include/utils/rel.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -344,6 +344,7 @@ typedef struct StdRdOptions
344344
intparallel_workers;/* max number of parallel workers */
345345
StdRdOptIndexCleanupvacuum_index_cleanup;/* controls index vacuuming */
346346
boolvacuum_truncate;/* enables vacuum to truncate a relation */
347+
boolvacuum_truncate_set;/* whether vacuum_truncate is set */
347348

348349
/*
349350
* Fraction of pages in a relation that vacuum can eagerly scan and fail

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

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -236,6 +236,7 @@ SELECT pg_relation_size('vac_truncate_test') > 0;
236236
t
237237
(1 row)
238238

239+
SET vacuum_truncate = false;
239240
VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
240241
SELECT pg_relation_size('vac_truncate_test') = 0;
241242
?column?
@@ -244,6 +245,32 @@ SELECT pg_relation_size('vac_truncate_test') = 0;
244245
(1 row)
245246

246247
VACUUM (TRUNCATE FALSE, FULL TRUE) vac_truncate_test;
248+
ALTER TABLE vac_truncate_test RESET (vacuum_truncate);
249+
INSERT INTO vac_truncate_test VALUES (1, NULL), (NULL, NULL);
250+
ERROR: null value in column "i" of relation "vac_truncate_test" violates not-null constraint
251+
DETAIL: Failing row contains (null, null).
252+
VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
253+
SELECT pg_relation_size('vac_truncate_test') > 0;
254+
?column?
255+
----------
256+
t
257+
(1 row)
258+
259+
RESET vacuum_truncate;
260+
VACUUM (TRUNCATE FALSE, DISABLE_PAGE_SKIPPING) vac_truncate_test;
261+
SELECT pg_relation_size('vac_truncate_test') > 0;
262+
?column?
263+
----------
264+
t
265+
(1 row)
266+
267+
VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
268+
SELECT pg_relation_size('vac_truncate_test') = 0;
269+
?column?
270+
----------
271+
t
272+
(1 row)
273+
247274
DROP TABLE vac_truncate_test;
248275
-- partitioned table
249276
CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a);

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -194,9 +194,19 @@ CREATE TEMP TABLE vac_truncate_test(i INT NOT NULL, j text)
194194
INSERT INTO vac_truncate_testVALUES (1,NULL), (NULL,NULL);
195195
VACUUM (TRUNCATE FALSE, DISABLE_PAGE_SKIPPING) vac_truncate_test;
196196
SELECT pg_relation_size('vac_truncate_test')>0;
197+
SET vacuum_truncate= false;
197198
VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
198199
SELECT pg_relation_size('vac_truncate_test')=0;
199200
VACUUM (TRUNCATE FALSE, FULL TRUE) vac_truncate_test;
201+
ALTERTABLE vac_truncate_test RESET (vacuum_truncate);
202+
INSERT INTO vac_truncate_testVALUES (1,NULL), (NULL,NULL);
203+
VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
204+
SELECT pg_relation_size('vac_truncate_test')>0;
205+
RESET vacuum_truncate;
206+
VACUUM (TRUNCATE FALSE, DISABLE_PAGE_SKIPPING) vac_truncate_test;
207+
SELECT pg_relation_size('vac_truncate_test')>0;
208+
VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
209+
SELECT pg_relation_size('vac_truncate_test')=0;
200210
DROPTABLE vac_truncate_test;
201211

202212
-- partitioned table

0 commit comments

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