Add vacuum_truncate reloption.

MasaoFujii · MasaoFujii · commit 119dcfad988d · 2019-04-08T16:43:57.000+09:00
vacuum_truncate controls whether vacuum tries to truncate off any empty pages at the end of the table. Previously vacuum always tried to do the truncation. However, the truncation could cause some problems; for example, ACCESS EXCLUSIVE lock needs to be taken on the table during the truncation and can cause the query cancellation on the standby even if hot_standby_feedback is true. Setting this reloption to false can be helpful to avoid such problems. Author: Tsunakawa Takayuki Reviewed-By: Julien Rouhaud, Masahiko Sawada, Michael Paquier, Kirk Jamison and Fujii Masao Discussion: https://postgr.es/m/CAHGQGwE5UqFqSq1=kV3QtTUtXphTdyHA-8rAj4A=Y+e4kyp3BQ@mail.gmail.com
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
@@ -1404,6 +1404,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>vacuum_truncate</literal>, <literal>toast.vacuum_truncate</literal> (<type>boolean</type>)</term>
+    <listitem>
+     <para>
+      Enables or disables vacuum to try to truncate off any empty pages
+      at the end of this table. The default value is <literal>true</literal>.
+      If <literal>true</literal>, <command>VACUUM</command> and
+      autovacuum do the truncation and the disk space for
+      the truncated pages is returned to the operating system.
+      Note that the truncation requires <literal>ACCESS EXCLUSIVE</literal>
+      lock on the table.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>autovacuum_vacuum_threshold</literal>, <literal>toast.autovacuum_vacuum_threshold</literal> (<type>integer</type>)</term>
     <listitem>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
@@ -89,6 +89,11 @@
  * Setting parallel_workers is safe, since it acts the same as
  * max_parallel_workers_per_gather which is a USERSET parameter that doesn't
  * affect existing plans or queries.
+ *
+ * vacuum_truncate can be set at ShareUpdateExclusiveLock because it
+ * is only used during VACUUM, which uses a ShareUpdateExclusiveLock,
+ * so the VACUUM will not be affected by in-flight changes. Changing its
+ * value has no affect until the next VACUUM, so no need for stronger lock.
  */
 
 static relopt_bool boolRelOpts[] =
@@ -147,6 +152,15 @@ static relopt_bool boolRelOpts[] =
 		},
 		true
 	},
+	{
+		{
+			"vacuum_truncate",
+			"Enables vacuum to truncate empty pages at the end of this table",
+			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+			ShareUpdateExclusiveLock
+		},
+		true
+	},
 	/* list terminator */
 	{{NULL}}
 };
@@ -1399,7 +1413,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		{"vacuum_cleanup_index_scale_factor", RELOPT_TYPE_REAL,
 		offsetof(StdRdOptions, vacuum_cleanup_index_scale_factor)},
 		{"vacuum_index_cleanup", RELOPT_TYPE_BOOL,
-		offsetof(StdRdOptions, vacuum_index_cleanup)}
+		offsetof(StdRdOptions, vacuum_index_cleanup)},
+		{"vacuum_truncate", RELOPT_TYPE_BOOL,
+		offsetof(StdRdOptions, vacuum_truncate)}
 	};
 
 	options = parseRelOptions(reloptions, validate, kind, &numoptions);
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
@@ -165,7 +165,7 @@ 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(LVRelStats *vacrelstats);
+static bool should_attempt_truncation(Relation rel, LVRelStats *vacrelstats);
 static void lazy_truncate_heap(Relation onerel, LVRelStats *vacrelstats);
 static BlockNumber count_nondeletable_pages(Relation onerel,
 						 LVRelStats *vacrelstats);
@@ -306,7 +306,7 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
 	/*
 	 * Optionally truncate the relation.
 	 */
-	if (should_attempt_truncation(vacrelstats))
+	if (should_attempt_truncation(onerel, vacrelstats))
 		lazy_truncate_heap(onerel, vacrelstats);
 
 	/* Report that we are now doing final cleanup */
@@ -660,7 +660,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(vacrelstats))
+		(blkno == nblocks - 1 && should_attempt_truncation(onerel, vacrelstats))
 
 		pgstat_progress_update_param(PROGRESS_VACUUM_HEAP_BLKS_SCANNED, blkno);
 
@@ -1869,10 +1869,14 @@ lazy_cleanup_index(Relation indrel,
  * careful to depend only on fields that lazy_scan_heap updates on-the-fly.
  */
 static bool
-should_attempt_truncation(LVRelStats *vacrelstats)
+should_attempt_truncation(Relation rel, LVRelStats *vacrelstats)
 {
 	BlockNumber possibly_freeable;
 
+	if (rel->rd_options != NULL &&
+		((StdRdOptions *) rel->rd_options)->vacuum_truncate == false)
+		return false;
+
 	possibly_freeable = vacrelstats->rel_pages - vacrelstats->nonempty_pages;
 	if (possibly_freeable > 0 &&
 		(possibly_freeable >= REL_TRUNCATE_MINIMUM ||
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
@@ -1056,9 +1056,11 @@ static const char *const table_storage_parameters[] = {
 	"toast.autovacuum_vacuum_scale_factor",
 	"toast.autovacuum_vacuum_threshold",
 	"toast.log_autovacuum_min_duration",
+	"toast.vacuum_truncate",
 	"toast_tuple_target",
 	"user_catalog_table",
 	"vacuum_index_cleanup",
+	"vacuum_truncate",
 	NULL
 };
 
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
@@ -267,6 +267,7 @@ typedef struct StdRdOptions
 	bool		user_catalog_table; /* use as an additional catalog relation */
 	int			parallel_workers;	/* max number of parallel workers */
 	bool		vacuum_index_cleanup;	/* enables index vacuuming and cleanup */
+	bool		vacuum_truncate;	/* enables vacuum to truncate a relation */
 } StdRdOptions;
 
 #define HEAP_MIN_FILLFACTOR			10
diff --git a/src/test/regress/expected/reloptions.out b/src/test/regress/expected/reloptions.out
@@ -87,6 +87,53 @@ SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND
 -- RESET fails if a value is specified
 ALTER TABLE reloptions_test RESET (fillfactor=12);
 ERROR:  RESET must not include values for parameters
+-- Test vacuum_truncate option
+DROP TABLE reloptions_test;
+CREATE TABLE reloptions_test(i INT NOT NULL, j text)
+	WITH (vacuum_truncate=false,
+	toast.vacuum_truncate=false,
+	autovacuum_enabled=false);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+                    reloptions                    
+--------------------------------------------------
+ {vacuum_truncate=false,autovacuum_enabled=false}
+(1 row)
+
+INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL);
+ERROR:  null value in column "i" violates not-null constraint
+DETAIL:  Failing row contains (null, null).
+VACUUM reloptions_test;
+SELECT pg_relation_size('reloptions_test') > 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT reloptions FROM pg_class WHERE oid =
+	(SELECT reltoastrelid FROM pg_class
+	WHERE oid = 'reloptions_test'::regclass);
+       reloptions        
+-------------------------
+ {vacuum_truncate=false}
+(1 row)
+
+ALTER TABLE reloptions_test RESET (vacuum_truncate);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+         reloptions         
+----------------------------
+ {autovacuum_enabled=false}
+(1 row)
+
+INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL);
+ERROR:  null value in column "i" violates not-null constraint
+DETAIL:  Failing row contains (null, null).
+VACUUM reloptions_test;
+SELECT pg_relation_size('reloptions_test') = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
 -- Test toast.* options
 DROP TABLE reloptions_test;
 CREATE TABLE reloptions_test (s VARCHAR)
diff --git a/src/test/regress/sql/reloptions.sql b/src/test/regress/sql/reloptions.sql
@@ -52,6 +52,28 @@ SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND
 -- RESET fails if a value is specified
 ALTER TABLE reloptions_test RESET (fillfactor=12);
 
+-- Test vacuum_truncate option
+DROP TABLE reloptions_test;
+
+CREATE TABLE reloptions_test(i INT NOT NULL, j text)
+	WITH (vacuum_truncate=false,
+	toast.vacuum_truncate=false,
+	autovacuum_enabled=false);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL);
+VACUUM reloptions_test;
+SELECT pg_relation_size('reloptions_test') > 0;
+
+SELECT reloptions FROM pg_class WHERE oid =
+	(SELECT reltoastrelid FROM pg_class
+	WHERE oid = 'reloptions_test'::regclass);
+
+ALTER TABLE reloptions_test RESET (vacuum_truncate);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL);
+VACUUM reloptions_test;
+SELECT pg_relation_size('reloptions_test') = 0;
+
 -- Test toast.* options
 DROP TABLE reloptions_test;
 
