From b84dbc8eb80b43e554891c459a19969d9fbdefe5 Mon Sep 17 00:00:00 2001 From: Fujii Masao Date: Wed, 8 May 2019 02:10:33 +0900 Subject: [PATCH] Add TRUNCATE parameter to VACUUM. This commit adds new parameter to VACUUM command, TRUNCATE, which specifies that VACUUM should attempt to truncate off any empty pages at the end of the table and allow the disk space for the truncated pages to be returned to the operating system. This parameter, if specified, overrides the vacuum_truncate reloption. If neither the reloption nor the VACUUM option is used, the default is true, as before. Author: Fujii Masao Reviewed-by: Julien Rouhaud, Masahiko Sawada Discussion: https://postgr.es/m/CAD21AoD+qtrSDL=GSma4Wd3kLYLeRC0hPna-YAdkDeV4z156vg@mail.gmail.com --- doc/src/sgml/ref/create_table.sgml | 6 ++++-- doc/src/sgml/ref/vacuum.sgml | 19 +++++++++++++++++++ src/backend/access/heap/vacuumlazy.c | 13 +++++++------ src/backend/commands/vacuum.c | 13 +++++++++++++ src/backend/postmaster/autovacuum.c | 1 + src/bin/psql/tab-complete.c | 4 ++-- src/include/commands/vacuum.h | 2 ++ src/test/regress/expected/vacuum.out | 22 ++++++++++++++++++++++ src/test/regress/sql/vacuum.sql | 11 +++++++++++ 9 files changed, 81 insertions(+), 10 deletions(-) diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 786510f94c..19af4eed9c 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1418,7 +1418,7 @@ WITH ( MODULUS numeric_literal, REM Disabling index cleanup can speed up VACUUM very significantly, but may also lead to severely bloated indexes if table modifications are frequent. The INDEX_CLEANUP - parameter to , if specified, overrides + parameter of , if specified, overrides the value of this option. @@ -1438,7 +1438,9 @@ WITH ( MODULUS numeric_literal, REM autovacuum do the truncation and the disk space for the truncated pages is returned to the operating system. Note that the truncation requires ACCESS EXCLUSIVE - lock on the table. + lock on the table. The TRUNCATE parameter + of , if specified, overrides the value + of this option. diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index c652f8b0bc..f9b0fb8794 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -33,6 +33,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ boolean ] SKIP_LOCKED [ boolean ] INDEX_CLEANUP [ boolean ] + TRUNCATE [ boolean ] and table_and_columns is: @@ -204,6 +205,24 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ boolean diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 0f70dc883d..f1a79059cd 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -163,7 +163,8 @@ static void lazy_cleanup_index(Relation indrel, LVRelStats *vacrelstats); static int lazy_vacuum_page(Relation onerel, BlockNumber blkno, Buffer buffer, int tupindex, LVRelStats *vacrelstats, Buffer *vmbuffer); -static bool should_attempt_truncation(Relation rel, LVRelStats *vacrelstats); +static bool should_attempt_truncation(VacuumParams *params, + LVRelStats *vacrelstats); static void lazy_truncate_heap(Relation onerel, LVRelStats *vacrelstats); static BlockNumber count_nondeletable_pages(Relation onerel, LVRelStats *vacrelstats); @@ -210,6 +211,7 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params, Assert(params != NULL); Assert(params->index_cleanup != VACOPT_TERNARY_DEFAULT); + Assert(params->truncate != VACOPT_TERNARY_DEFAULT); /* not every AM requires these to be valid, but heap does */ Assert(TransactionIdIsNormal(onerel->rd_rel->relfrozenxid)); @@ -308,7 +310,7 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params, /* * Optionally truncate the relation. */ - if (should_attempt_truncation(onerel, vacrelstats)) + if (should_attempt_truncation(params, vacrelstats)) lazy_truncate_heap(onerel, vacrelstats); /* Report that we are now doing final cleanup */ @@ -652,7 +654,7 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, /* see note above about forcing scanning of last page */ #define FORCE_CHECK_PAGE() \ - (blkno == nblocks - 1 && should_attempt_truncation(onerel, vacrelstats)) + (blkno == nblocks - 1 && should_attempt_truncation(params, vacrelstats)) pgstat_progress_update_param(PROGRESS_VACUUM_HEAP_BLKS_SCANNED, blkno); @@ -1845,12 +1847,11 @@ lazy_cleanup_index(Relation indrel, * careful to depend only on fields that lazy_scan_heap updates on-the-fly. */ static bool -should_attempt_truncation(Relation rel, LVRelStats *vacrelstats) +should_attempt_truncation(VacuumParams *params, LVRelStats *vacrelstats) { BlockNumber possibly_freeable; - if (rel->rd_options != NULL && - ((StdRdOptions *) rel->rd_options)->vacuum_truncate == false) + if (params->truncate == VACOPT_TERNARY_DISABLED) return false; possibly_freeable = vacrelstats->rel_pages - vacrelstats->nonempty_pages; diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 94fb6f2606..afdd3307ac 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -98,6 +98,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) /* Set default value */ params.index_cleanup = VACOPT_TERNARY_DEFAULT; + params.truncate = VACOPT_TERNARY_DEFAULT; /* Parse options list */ foreach(lc, vacstmt->options) @@ -126,6 +127,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) disable_page_skipping = defGetBoolean(opt); else if (strcmp(opt->defname, "index_cleanup") == 0) params.index_cleanup = get_vacopt_ternary_value(opt); + else if (strcmp(opt->defname, "truncate") == 0) + params.truncate = get_vacopt_ternary_value(opt); else ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), @@ -1760,6 +1763,16 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) params->index_cleanup = VACOPT_TERNARY_DISABLED; } + /* Set truncate option based on reloptions if not yet */ + if (params->truncate == VACOPT_TERNARY_DEFAULT) + { + if (onerel->rd_options == NULL || + ((StdRdOptions *) onerel->rd_options)->vacuum_truncate) + params->truncate = VACOPT_TERNARY_ENABLED; + else + params->truncate = VACOPT_TERNARY_DISABLED; + } + /* * Remember the relation's TOAST relation for later, if the caller asked * us to process it. In VACUUM FULL, though, the toast table is diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 53c91d9277..acd8a9280b 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -2887,6 +2887,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, (doanalyze ? VACOPT_ANALYZE : 0) | (!wraparound ? VACOPT_SKIP_LOCKED : 0); tab->at_params.index_cleanup = VACOPT_TERNARY_DEFAULT; + tab->at_params.truncate = VACOPT_TERNARY_DEFAULT; tab->at_params.freeze_min_age = freeze_min_age; tab->at_params.freeze_table_age = freeze_table_age; tab->at_params.multixact_freeze_min_age = multixact_freeze_min_age; diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index bcddc7601e..e4c03de221 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -3466,8 +3466,8 @@ psql_completion(const char *text, int start, int end) if (ends_with(prev_wd, '(') || ends_with(prev_wd, ',')) COMPLETE_WITH("FULL", "FREEZE", "ANALYZE", "VERBOSE", "DISABLE_PAGE_SKIPPING", "SKIP_LOCKED", - "INDEX_CLEANUP"); - else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP")) + "INDEX_CLEANUP", "TRUNCATE"); + else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE")) COMPLETE_WITH("ON", "OFF"); } else if (HeadMatches("VACUUM") && TailMatches("(")) diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 9cc6e0d023..270f61b083 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -182,6 +182,8 @@ typedef struct VacuumParams * to use default */ VacOptTernaryValue index_cleanup; /* Do index vacuum and cleanup, * default value depends on reloptions */ + VacOptTernaryValue truncate; /* Truncate empty pages at the end, + * default value depends on reloptions */ } VacuumParams; /* GUC parameters */ diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index 6ba7cd726b..e6657a675e 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -88,6 +88,28 @@ VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster; -- index cleanup option is ignored if VACUUM FULL VACUUM (INDEX_CLEANUP TRUE, FULL TRUE) no_index_cleanup; VACUUM (FULL TRUE) no_index_cleanup; +-- TRUNCATE option +CREATE TABLE vac_truncate_test(i INT NOT NULL, j text) + WITH (vacuum_truncate=true, autovacuum_enabled=false); +INSERT INTO vac_truncate_test VALUES (1, NULL), (NULL, NULL); +ERROR: null value in column "i" violates not-null constraint +DETAIL: Failing row contains (null, null). +VACUUM (TRUNCATE FALSE) vac_truncate_test; +SELECT pg_relation_size('vac_truncate_test') > 0; + ?column? +---------- + t +(1 row) + +VACUUM vac_truncate_test; +SELECT pg_relation_size('vac_truncate_test') = 0; + ?column? +---------- + t +(1 row) + +VACUUM (TRUNCATE FALSE, FULL TRUE) vac_truncate_test; +DROP TABLE vac_truncate_test; -- partitioned table CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a); CREATE TABLE vacparted1 PARTITION OF vacparted FOR VALUES IN (1); diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql index 57e0f354dd..4fa90940dc 100644 --- a/src/test/regress/sql/vacuum.sql +++ b/src/test/regress/sql/vacuum.sql @@ -71,6 +71,17 @@ VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster; VACUUM (INDEX_CLEANUP TRUE, FULL TRUE) no_index_cleanup; VACUUM (FULL TRUE) no_index_cleanup; +-- TRUNCATE option +CREATE TABLE vac_truncate_test(i INT NOT NULL, j text) + WITH (vacuum_truncate=true, autovacuum_enabled=false); +INSERT INTO vac_truncate_test VALUES (1, NULL), (NULL, NULL); +VACUUM (TRUNCATE FALSE) vac_truncate_test; +SELECT pg_relation_size('vac_truncate_test') > 0; +VACUUM vac_truncate_test; +SELECT pg_relation_size('vac_truncate_test') = 0; +VACUUM (TRUNCATE FALSE, FULL TRUE) vac_truncate_test; +DROP TABLE vac_truncate_test; + -- partitioned table CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a); CREATE TABLE vacparted1 PARTITION OF vacparted FOR VALUES IN (1); -- 2.49.0