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

Commit119dcfa

Browse files
committed
Add vacuum_truncate reloption.
vacuum_truncate controls whether vacuum tries to truncate offany empty pages at the end of the table. Previously vacuum alwaystried to do the truncation. However, the truncation could causesome problems; for example, ACCESS EXCLUSIVE lock needs tobe taken on the table during the truncation and can causethe query cancellation on the standby even if hot_standby_feedbackis true. Setting this reloption to false can be helpful to avoidsuch problems.Author: Tsunakawa TakayukiReviewed-By: Julien Rouhaud, Masahiko Sawada, Michael Paquier, Kirk Jamison and Fujii MasaoDiscussion:https://postgr.es/m/CAHGQGwE5UqFqSq1=kV3QtTUtXphTdyHA-8rAj4A=Y+e4kyp3BQ@mail.gmail.com
1 parente3865c3 commit119dcfa

File tree

7 files changed

+112
-5
lines changed

7 files changed

+112
-5
lines changed

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1404,6 +1404,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
14041404
</listitem>
14051405
</varlistentry>
14061406

1407+
<varlistentry>
1408+
<term><literal>vacuum_truncate</literal>, <literal>toast.vacuum_truncate</literal> (<type>boolean</type>)</term>
1409+
<listitem>
1410+
<para>
1411+
Enables or disables vacuum to try to truncate off any empty pages
1412+
at the end of this table. The default value is <literal>true</literal>.
1413+
If <literal>true</literal>, <command>VACUUM</command> and
1414+
autovacuum do the truncation and the disk space for
1415+
the truncated pages is returned to the operating system.
1416+
Note that the truncation requires <literal>ACCESS EXCLUSIVE</literal>
1417+
lock on the table.
1418+
</para>
1419+
</listitem>
1420+
</varlistentry>
1421+
14071422
<varlistentry>
14081423
<term><literal>autovacuum_vacuum_threshold</literal>, <literal>toast.autovacuum_vacuum_threshold</literal> (<type>integer</type>)</term>
14091424
<listitem>

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

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -89,6 +89,11 @@
8989
* Setting parallel_workers is safe, since it acts the same as
9090
* max_parallel_workers_per_gather which is a USERSET parameter that doesn't
9191
* affect existing plans or queries.
92+
*
93+
* vacuum_truncate can be set at ShareUpdateExclusiveLock because it
94+
* is only used during VACUUM, which uses a ShareUpdateExclusiveLock,
95+
* so the VACUUM will not be affected by in-flight changes. Changing its
96+
* value has no affect until the next VACUUM, so no need for stronger lock.
9297
*/
9398

9499
staticrelopt_boolboolRelOpts[]=
@@ -147,6 +152,15 @@ static relopt_bool boolRelOpts[] =
147152
},
148153
true
149154
},
155+
{
156+
{
157+
"vacuum_truncate",
158+
"Enables vacuum to truncate empty pages at the end of this table",
159+
RELOPT_KIND_HEAP |RELOPT_KIND_TOAST,
160+
ShareUpdateExclusiveLock
161+
},
162+
true
163+
},
150164
/* list terminator */
151165
{{NULL}}
152166
};
@@ -1399,7 +1413,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
13991413
{"vacuum_cleanup_index_scale_factor",RELOPT_TYPE_REAL,
14001414
offsetof(StdRdOptions,vacuum_cleanup_index_scale_factor)},
14011415
{"vacuum_index_cleanup",RELOPT_TYPE_BOOL,
1402-
offsetof(StdRdOptions,vacuum_index_cleanup)}
1416+
offsetof(StdRdOptions,vacuum_index_cleanup)},
1417+
{"vacuum_truncate",RELOPT_TYPE_BOOL,
1418+
offsetof(StdRdOptions,vacuum_truncate)}
14031419
};
14041420

14051421
options=parseRelOptions(reloptions,validate,kind,&numoptions);

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

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -165,7 +165,7 @@ static void lazy_cleanup_index(Relation indrel,
165165
LVRelStats*vacrelstats);
166166
staticintlazy_vacuum_page(Relationonerel,BlockNumberblkno,Bufferbuffer,
167167
inttupindex,LVRelStats*vacrelstats,Buffer*vmbuffer);
168-
staticboolshould_attempt_truncation(LVRelStats*vacrelstats);
168+
staticboolshould_attempt_truncation(Relationrel,LVRelStats*vacrelstats);
169169
staticvoidlazy_truncate_heap(Relationonerel,LVRelStats*vacrelstats);
170170
staticBlockNumbercount_nondeletable_pages(Relationonerel,
171171
LVRelStats*vacrelstats);
@@ -306,7 +306,7 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
306306
/*
307307
* Optionally truncate the relation.
308308
*/
309-
if (should_attempt_truncation(vacrelstats))
309+
if (should_attempt_truncation(onerel,vacrelstats))
310310
lazy_truncate_heap(onerel,vacrelstats);
311311

312312
/* Report that we are now doing final cleanup */
@@ -660,7 +660,7 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
660660

661661
/* see note above about forcing scanning of last page */
662662
#defineFORCE_CHECK_PAGE() \
663-
(blkno == nblocks - 1 && should_attempt_truncation(vacrelstats))
663+
(blkno == nblocks - 1 && should_attempt_truncation(onerel,vacrelstats))
664664

665665
pgstat_progress_update_param(PROGRESS_VACUUM_HEAP_BLKS_SCANNED,blkno);
666666

@@ -1869,10 +1869,14 @@ lazy_cleanup_index(Relation indrel,
18691869
* careful to depend only on fields that lazy_scan_heap updates on-the-fly.
18701870
*/
18711871
staticbool
1872-
should_attempt_truncation(LVRelStats*vacrelstats)
1872+
should_attempt_truncation(Relationrel,LVRelStats*vacrelstats)
18731873
{
18741874
BlockNumberpossibly_freeable;
18751875

1876+
if (rel->rd_options!=NULL&&
1877+
((StdRdOptions*)rel->rd_options)->vacuum_truncate== false)
1878+
return false;
1879+
18761880
possibly_freeable=vacrelstats->rel_pages-vacrelstats->nonempty_pages;
18771881
if (possibly_freeable>0&&
18781882
(possibly_freeable >=REL_TRUNCATE_MINIMUM||

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1056,9 +1056,11 @@ static const char *const table_storage_parameters[] = {
10561056
"toast.autovacuum_vacuum_scale_factor",
10571057
"toast.autovacuum_vacuum_threshold",
10581058
"toast.log_autovacuum_min_duration",
1059+
"toast.vacuum_truncate",
10591060
"toast_tuple_target",
10601061
"user_catalog_table",
10611062
"vacuum_index_cleanup",
1063+
"vacuum_truncate",
10621064
NULL
10631065
};
10641066

‎src/include/utils/rel.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -267,6 +267,7 @@ typedef struct StdRdOptions
267267
booluser_catalog_table;/* use as an additional catalog relation */
268268
intparallel_workers;/* max number of parallel workers */
269269
boolvacuum_index_cleanup;/* enables index vacuuming and cleanup */
270+
boolvacuum_truncate;/* enables vacuum to truncate a relation */
270271
}StdRdOptions;
271272

272273
#defineHEAP_MIN_FILLFACTOR10

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

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -87,6 +87,53 @@ SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND
8787
-- RESET fails if a value is specified
8888
ALTER TABLE reloptions_test RESET (fillfactor=12);
8989
ERROR: RESET must not include values for parameters
90+
-- Test vacuum_truncate option
91+
DROP TABLE reloptions_test;
92+
CREATE TABLE reloptions_test(i INT NOT NULL, j text)
93+
WITH (vacuum_truncate=false,
94+
toast.vacuum_truncate=false,
95+
autovacuum_enabled=false);
96+
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
97+
reloptions
98+
--------------------------------------------------
99+
{vacuum_truncate=false,autovacuum_enabled=false}
100+
(1 row)
101+
102+
INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL);
103+
ERROR: null value in column "i" violates not-null constraint
104+
DETAIL: Failing row contains (null, null).
105+
VACUUM reloptions_test;
106+
SELECT pg_relation_size('reloptions_test') > 0;
107+
?column?
108+
----------
109+
t
110+
(1 row)
111+
112+
SELECT reloptions FROM pg_class WHERE oid =
113+
(SELECT reltoastrelid FROM pg_class
114+
WHERE oid = 'reloptions_test'::regclass);
115+
reloptions
116+
-------------------------
117+
{vacuum_truncate=false}
118+
(1 row)
119+
120+
ALTER TABLE reloptions_test RESET (vacuum_truncate);
121+
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
122+
reloptions
123+
----------------------------
124+
{autovacuum_enabled=false}
125+
(1 row)
126+
127+
INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL);
128+
ERROR: null value in column "i" violates not-null constraint
129+
DETAIL: Failing row contains (null, null).
130+
VACUUM reloptions_test;
131+
SELECT pg_relation_size('reloptions_test') = 0;
132+
?column?
133+
----------
134+
t
135+
(1 row)
136+
90137
-- Test toast.* options
91138
DROP TABLE reloptions_test;
92139
CREATE TABLE reloptions_test (s VARCHAR)

‎src/test/regress/sql/reloptions.sql

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -52,6 +52,28 @@ SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND
5252
-- RESET fails if a value is specified
5353
ALTERTABLE reloptions_test RESET (fillfactor=12);
5454

55+
-- Test vacuum_truncate option
56+
DROPTABLE reloptions_test;
57+
58+
CREATETABLEreloptions_test(iINTNOT NULL, jtext)
59+
WITH (vacuum_truncate=false,
60+
toast.vacuum_truncate=false,
61+
autovacuum_enabled=false);
62+
SELECT reloptionsFROM pg_classWHEREoid='reloptions_test'::regclass;
63+
INSERT INTO reloptions_testVALUES (1,NULL), (NULL,NULL);
64+
VACUUM reloptions_test;
65+
SELECT pg_relation_size('reloptions_test')>0;
66+
67+
SELECT reloptionsFROM pg_classWHEREoid=
68+
(SELECT reltoastrelidFROM pg_class
69+
WHEREoid='reloptions_test'::regclass);
70+
71+
ALTERTABLE reloptions_test RESET (vacuum_truncate);
72+
SELECT reloptionsFROM pg_classWHEREoid='reloptions_test'::regclass;
73+
INSERT INTO reloptions_testVALUES (1,NULL), (NULL,NULL);
74+
VACUUM reloptions_test;
75+
SELECT pg_relation_size('reloptions_test')=0;
76+
5577
-- Test toast.* options
5678
DROPTABLE reloptions_test;
5779

0 commit comments

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