vacuum_truncate_v1.patch

*** a/doc/src/sgml/ref/create_table.sgml
--- b/doc/src/sgml/ref/create_table.sgml
***************
*** 1414,1420 **** WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
        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>
--- 1414,1422 ----
        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. The <literal>TRUNCATE</literal> parameter
!       to <xref linkend="sql-vacuum"/>, if specified, overrides the value
!       of this option.
       </para>
      </listitem>
     </varlistentry>
*** a/doc/src/sgml/ref/vacuum.sgml
--- b/doc/src/sgml/ref/vacuum.sgml
***************
*** 33,38 **** VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
--- 33,39 ----
      DISABLE_PAGE_SKIPPING [ <replaceable class="parameter">boolean</replaceable> ]
      SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
      INDEX_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
+     TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
  
  <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
  
***************
*** 204,209 **** VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
--- 205,228 ----
      </listitem>
     </varlistentry>
  
+    <varlistentry>
+     <term><literal>TRUNCATE</literal></term>
+     <listitem>
+      <para>
+       Specifies that <command>VACUUM</command> 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 is normally the desired behavior
+       and is the default unless the <literal>vacuum_truncate</literal>
+       option has been set to false for the table to be vacuumed.
+       Setting this option to false may be useful to avoid
+       <literal>ACCESS EXCLUSIVE</literal> lock on the table that
+       the truncation requires. This option is ignored if the
+       <literal>FULL</literal> option is used.
+      </para>
+     </listitem>
+    </varlistentry>
+ 
     <varlistentry>
      <term><replaceable class="parameter">boolean</replaceable></term>
      <listitem>
*** a/src/backend/access/heap/vacuumlazy.c
--- b/src/backend/access/heap/vacuumlazy.c
***************
*** 165,171 **** 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 void lazy_truncate_heap(Relation onerel, LVRelStats *vacrelstats);
  static BlockNumber count_nondeletable_pages(Relation onerel,
  						 LVRelStats *vacrelstats);
--- 165,172 ----
  				   LVRelStats *vacrelstats);
  static int lazy_vacuum_page(Relation onerel, BlockNumber blkno, Buffer buffer,
  				 int tupindex, LVRelStats *vacrelstats, Buffer *vmbuffer);
! 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);
***************
*** 212,217 **** heap_vacuum_rel(Relation onerel, VacuumParams *params,
--- 213,219 ----
  
  	Assert(params != NULL);
  	Assert(params->index_cleanup != VACOPT_TERNARY_DEFAULT);
+ 	Assert(params->truncate != VACOPT_TERNARY_DEFAULT);
  
  	/* measure elapsed time iff autovacuum logging requires it */
  	if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0)
***************
*** 306,312 **** heap_vacuum_rel(Relation onerel, VacuumParams *params,
  	/*
  	 * Optionally truncate the relation.
  	 */
! 	if (should_attempt_truncation(onerel, vacrelstats))
  		lazy_truncate_heap(onerel, vacrelstats);
  
  	/* Report that we are now doing final cleanup */
--- 308,314 ----
  	/*
  	 * Optionally truncate the relation.
  	 */
! 	if (should_attempt_truncation(params, vacrelstats))
  		lazy_truncate_heap(onerel, vacrelstats);
  
  	/* Report that we are now doing final cleanup */
***************
*** 660,666 **** 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))
  
  		pgstat_progress_update_param(PROGRESS_VACUUM_HEAP_BLKS_SCANNED, blkno);
  
--- 662,668 ----
  
  		/* see note above about forcing scanning of last page */
  #define FORCE_CHECK_PAGE() \
! 		(blkno == nblocks - 1 && should_attempt_truncation(params, vacrelstats))
  
  		pgstat_progress_update_param(PROGRESS_VACUUM_HEAP_BLKS_SCANNED, blkno);
  
***************
*** 1869,1880 **** 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)
  {
  	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;
--- 1871,1881 ----
   * careful to depend only on fields that lazy_scan_heap updates on-the-fly.
   */
  static bool
! should_attempt_truncation(VacuumParams *params, LVRelStats *vacrelstats)
  {
  	BlockNumber possibly_freeable;
  
! 	if (params->truncate == VACOPT_TERNARY_DISABLED)
  		return false;
  
  	possibly_freeable = vacrelstats->rel_pages - vacrelstats->nonempty_pages;
*** a/src/backend/commands/vacuum.c
--- b/src/backend/commands/vacuum.c
***************
*** 98,103 **** ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
--- 98,104 ----
  
  	/* Set default value */
  	params.index_cleanup = VACOPT_TERNARY_DEFAULT;
+ 	params.truncate = VACOPT_TERNARY_DEFAULT;
  
  	/* Parse options list */
  	foreach(lc, vacstmt->options)
***************
*** 126,131 **** ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
--- 127,134 ----
  			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),
***************
*** 1735,1740 **** vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
--- 1738,1753 ----
  			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
*** a/src/backend/postmaster/autovacuum.c
--- b/src/backend/postmaster/autovacuum.c
***************
*** 2887,2892 **** table_recheck_autovac(Oid relid, HTAB *table_toast_map,
--- 2887,2893 ----
  			(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;
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
***************
*** 3466,3473 **** 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"))
  			COMPLETE_WITH("ON", "OFF");
  	}
  	else if (HeadMatches("VACUUM") && TailMatches("("))
--- 3466,3473 ----
  		if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
  			COMPLETE_WITH("FULL", "FREEZE", "ANALYZE", "VERBOSE",
  						  "DISABLE_PAGE_SKIPPING", "SKIP_LOCKED",
! 						  "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("("))
*** a/src/include/commands/vacuum.h
--- b/src/include/commands/vacuum.h
***************
*** 182,187 **** typedef struct VacuumParams
--- 182,189 ----
  									 * 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 */
*** a/src/test/regress/expected/vacuum.out
--- b/src/test/regress/expected/vacuum.out
***************
*** 88,93 **** VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster;
--- 88,115 ----
  -- 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);
*** a/src/test/regress/sql/vacuum.sql
--- b/src/test/regress/sql/vacuum.sql
***************
*** 71,76 **** VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster;
--- 71,87 ----
  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);