Add vacuum_freeze_table_age GUC option, to control when VACUUM should

ignore the visibility map and scan the whole table, to advance
relfrozenxid.

13 files changed, 105 insertions, 35 deletions

diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 3e54d49dec5..e5bff5cb3ef 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -11,7 +11,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/cluster.c,v 1.180 2009/01/01 17:23:37 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/cluster.c,v 1.181 2009/01/16 13:27:23 heikki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -789,8 +789,8 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex)
 	 * freeze_min_age to avoid having CLUSTER freeze tuples earlier than a
 	 * plain VACUUM would.
 	 */
-	vacuum_set_xid_limits(-1, OldHeap->rd_rel->relisshared,
-						  &OldestXmin, &FreezeXid);
+	vacuum_set_xid_limits(-1, -1, OldHeap->rd_rel->relisshared,
+						  &OldestXmin, &FreezeXid, NULL);
 
 	/*
 	 * FreezeXid will become the table's new relfrozenxid, and that mustn't
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 755eec11e02..4020bf1b294 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -13,7 +13,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/vacuum.c,v 1.384 2009/01/01 17:23:40 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/vacuum.c,v 1.385 2009/01/16 13:27:23 heikki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -62,6 +62,7 @@
  * GUC parameters
  */
 int			vacuum_freeze_min_age;
+int			vacuum_freeze_table_age;
 
 /*
  * VacPage structures keep track of each page on which we find useful
@@ -590,9 +591,12 @@ get_rel_oids(Oid relid, const RangeVar *vacrel, const char *stmttype)
  * vacuum_set_xid_limits() -- compute oldest-Xmin and freeze cutoff points
  */
 void
-vacuum_set_xid_limits(int freeze_min_age, bool sharedRel,
+vacuum_set_xid_limits(int freeze_min_age,
+					  int freeze_table_age,
+					  bool sharedRel,
 					  TransactionId *oldestXmin,
-					  TransactionId *freezeLimit)
+					  TransactionId *freezeLimit,
+					  TransactionId *freezeTableLimit)
 {
 	int			freezemin;
 	TransactionId limit;
@@ -648,6 +652,34 @@ vacuum_set_xid_limits(int freeze_min_age, bool sharedRel,
 	}
 
 	*freezeLimit = limit;
+
+	if (freezeTableLimit != NULL)
+	{
+		int freezetable;
+
+		/*
+		 * Determine the table freeze age to use: as specified by the caller,
+		 * or vacuum_freeze_table_age, but in any case not more than
+		 * autovacuum_freeze_max_age * 0.95, so that if you have e.g nightly
+		 * VACUUM schedule, the nightly VACUUM gets a chance to freeze tuples
+		 * before anti-wraparound autovacuum is launched.
+		 */
+		freezetable = freeze_min_age;
+		if (freezetable < 0)
+			freezetable = vacuum_freeze_table_age;
+		freezetable = Min(freezetable, autovacuum_freeze_max_age * 0.95);
+		Assert(freezetable >= 0);
+
+		/*
+		 * Compute the cutoff XID, being careful not to generate a
+		 * "permanent" XID.
+		 */
+		limit = ReadNewTransactionId() - freezetable;
+		if (!TransactionIdIsNormal(limit))
+			limit = FirstNormalTransactionId;
+
+		*freezeTableLimit = limit;
+	}
 }
 
 
@@ -1219,8 +1251,9 @@ full_vacuum_rel(Relation onerel, VacuumStmt *vacstmt)
 				i;
 	VRelStats  *vacrelstats;
 
-	vacuum_set_xid_limits(vacstmt->freeze_min_age, onerel->rd_rel->relisshared,
-						  &OldestXmin, &FreezeLimit);
+	vacuum_set_xid_limits(vacstmt->freeze_min_age, vacstmt->freeze_table_age,
+						  onerel->rd_rel->relisshared,
+						  &OldestXmin, &FreezeLimit, NULL);
 
 	/*
 	 * Flush any previous async-commit transactions.  This does not guarantee
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index 4a009a62e70..63a6ffb440a 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -29,7 +29,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/vacuumlazy.c,v 1.116 2009/01/06 14:55:37 heikki Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/vacuumlazy.c,v 1.117 2009/01/16 13:27:23 heikki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -144,6 +144,8 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
 	BlockNumber possibly_freeable;
 	PGRUsage	ru0;
 	TimestampTz starttime = 0;
+	bool		scan_all;
+	TransactionId freezeTableLimit;
 
 	pg_rusage_init(&ru0);
 
@@ -158,8 +160,11 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
 
 	vac_strategy = bstrategy;
 
-	vacuum_set_xid_limits(vacstmt->freeze_min_age, onerel->rd_rel->relisshared,
-						  &OldestXmin, &FreezeLimit);
+	vacuum_set_xid_limits(vacstmt->freeze_min_age, vacstmt->freeze_table_age,
+						  onerel->rd_rel->relisshared,
+						  &OldestXmin, &FreezeLimit, &freezeTableLimit);
+	scan_all = TransactionIdPrecedesOrEquals(onerel->rd_rel->relfrozenxid,
+											 freezeTableLimit);
 
 	vacrelstats = (LVRelStats *) palloc0(sizeof(LVRelStats));
 
@@ -171,7 +176,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
 	vacrelstats->hasindex = (nindexes > 0);
  
 	/* Do the vacuuming */
-	lazy_scan_heap(onerel, vacrelstats, Irel, nindexes, vacstmt->scan_all);
+	lazy_scan_heap(onerel, vacrelstats, Irel, nindexes, scan_all);
 
 	/* Done with indexes */
 	vac_close_indexes(nindexes, Irel, NoLock);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index e88108c53f5..da1c65cfcca 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -15,7 +15,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.419 2009/01/01 17:23:43 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.420 2009/01/16 13:27:23 heikki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -2865,7 +2865,7 @@ _copyVacuumStmt(VacuumStmt *from)
 	COPY_SCALAR_FIELD(analyze);
 	COPY_SCALAR_FIELD(verbose);
 	COPY_SCALAR_FIELD(freeze_min_age);
-	COPY_SCALAR_FIELD(scan_all);
+	COPY_SCALAR_FIELD(freeze_table_age);
 	COPY_NODE_FIELD(relation);
 	COPY_NODE_FIELD(va_cols);
 
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 1c6d5b0c9bc..190750f2e19 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -22,7 +22,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.344 2009/01/01 17:23:43 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.345 2009/01/16 13:27:23 heikki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1454,7 +1454,7 @@ _equalVacuumStmt(VacuumStmt *a, VacuumStmt *b)
 	COMPARE_SCALAR_FIELD(analyze);
 	COMPARE_SCALAR_FIELD(verbose);
 	COMPARE_SCALAR_FIELD(freeze_min_age);
-	COMPARE_SCALAR_FIELD(scan_all);
+	COMPARE_SCALAR_FIELD(freeze_table_age);
 	COMPARE_NODE_FIELD(relation);
 	COMPARE_NODE_FIELD(va_cols);
 
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 448b7615988..cc1f812bd98 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11,7 +11,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.654 2009/01/12 09:38:30 petere Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.655 2009/01/16 13:27:23 heikki Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -6263,7 +6263,7 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
 					n->analyze = false;
 					n->full = $2;
 					n->freeze_min_age = $3 ? 0 : -1;
-					n->scan_all = $2 || $3;
+					n->freeze_table_age = $3 ? 0 : -1;
 					n->verbose = $4;
 					n->relation = NULL;
 					n->va_cols = NIL;
@@ -6276,7 +6276,7 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
 					n->analyze = false;
 					n->full = $2;
 					n->freeze_min_age = $3 ? 0 : -1;
-					n->scan_all = $2 || $3;
+					n->freeze_table_age = $3 ? 0 : -1;
 					n->verbose = $4;
 					n->relation = $5;
 					n->va_cols = NIL;
@@ -6288,7 +6288,7 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
 					n->vacuum = true;
 					n->full = $2;
 					n->freeze_min_age = $3 ? 0 : -1;
-					n->scan_all = $2 || $3;
+					n->freeze_table_age = $3 ? 0 : -1;
 					n->verbose |= $4;
 					$$ = (Node *)n;
 				}
@@ -6302,6 +6302,7 @@ AnalyzeStmt:
 					n->analyze = true;
 					n->full = false;
 					n->freeze_min_age = -1;
+					n->freeze_table_age = -1;
 					n->verbose = $2;
 					n->relation = NULL;
 					n->va_cols = NIL;
@@ -6314,6 +6315,7 @@ AnalyzeStmt:
 					n->analyze = true;
 					n->full = false;
 					n->freeze_min_age = -1;
+					n->freeze_table_age = -1;
 					n->verbose = $2;
 					n->relation = $3;
 					n->va_cols = $4;
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index ce90fa45abe..ed77c51c9ff 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -55,7 +55,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/postmaster/autovacuum.c,v 1.91 2009/01/01 17:23:46 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/postmaster/autovacuum.c,v 1.92 2009/01/16 13:27:24 heikki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -136,8 +136,9 @@ static volatile sig_atomic_t got_SIGTERM = false;
 /* Comparison point for determining whether freeze_max_age is exceeded */
 static TransactionId recentXid;
 
-/* Default freeze_min_age to use for autovacuum (varies by database) */
+/* Default freeze ages to use for autovacuum (varies by database) */
 static int	default_freeze_min_age;
+static int	default_freeze_table_age;
 
 /* Memory context for long-lived data */
 static MemoryContext AutovacMemCxt;
@@ -174,6 +175,7 @@ typedef struct autovac_table
 	bool		at_dovacuum;
 	bool		at_doanalyze;
 	int			at_freeze_min_age;
+	int			at_freeze_table_age;
 	int			at_vacuum_cost_delay;
 	int			at_vacuum_cost_limit;
 	bool		at_wraparound;
@@ -1857,7 +1859,7 @@ do_autovacuum(void)
 	pgstat_vacuum_stat();
 
 	/*
-	 * Find the pg_database entry and select the default freeze_min_age. We
+	 * Find the pg_database entry and select the default freeze ages. We
 	 * use zero in template and nonconnectable databases, else the system-wide
 	 * default.
 	 */
@@ -1869,9 +1871,15 @@ do_autovacuum(void)
 	dbForm = (Form_pg_database) GETSTRUCT(tuple);
 
 	if (dbForm->datistemplate || !dbForm->datallowconn)
+	{
 		default_freeze_min_age = 0;
+		default_freeze_table_age = 0;
+	}
 	else
+	{
 		default_freeze_min_age = vacuum_freeze_min_age;
+		default_freeze_table_age = vacuum_freeze_table_age;
+	}
 
 	ReleaseSysCache(tuple);
 
@@ -2418,6 +2426,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map)
 	if (doanalyze || dovacuum)
 	{
 		int			freeze_min_age;
+		int			freeze_table_age;
 		int			vac_cost_limit;
 		int			vac_cost_delay;
 
@@ -2443,6 +2452,9 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map)
 
 			freeze_min_age = (avForm->freeze_min_age >= 0) ?
 				avForm->freeze_min_age : default_freeze_min_age;
+
+			freeze_table_age = (avForm->freeze_table_age >= 0) ?
+				avForm->freeze_table_age : default_freeze_table_age;
 		}
 		else
 		{
@@ -2453,6 +2465,8 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map)
 				autovacuum_vac_cost_delay : VacuumCostDelay;
 
 			freeze_min_age = default_freeze_min_age;
+
+			freeze_table_age = default_freeze_table_age;
 		}
 
 		tab = palloc(sizeof(autovac_table));
@@ -2460,6 +2474,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map)
 		tab->at_dovacuum = dovacuum;
 		tab->at_doanalyze = doanalyze;
 		tab->at_freeze_min_age = freeze_min_age;
+		tab->at_freeze_table_age = freeze_table_age;
 		tab->at_vacuum_cost_limit = vac_cost_limit;
 		tab->at_vacuum_cost_delay = vac_cost_delay;
 		tab->at_wraparound = wraparound;
@@ -2649,7 +2664,7 @@ autovacuum_do_vac_analyze(autovac_table *tab,
 	vacstmt.full = false;
 	vacstmt.analyze = tab->at_doanalyze;
 	vacstmt.freeze_min_age = tab->at_freeze_min_age;
-	vacstmt.scan_all = tab->at_wraparound;
+	vacstmt.freeze_table_age = tab->at_freeze_table_age;
 	vacstmt.verbose = false;
 	vacstmt.relation = NULL;	/* not used since we pass a relid */
 	vacstmt.va_cols = NIL;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 63e9628a5dc..fbba0557d29 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -10,7 +10,7 @@
  * Written by Peter Eisentraut <peter_e@gmx.net>.
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.493 2009/01/12 05:10:44 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.494 2009/01/16 13:27:24 heikki Exp $
  *
  *--------------------------------------------------------------------
  */
@@ -1544,7 +1544,16 @@ static struct config_int ConfigureNamesInt[] =
 			NULL
 		},
 		&vacuum_freeze_min_age,
-		100000000, 0, 1000000000, NULL, NULL
+		50000000, 0, 1000000000, NULL, NULL
+	},
+
+	{
+		{"vacuum_freeze_table_age", PGC_USERSET, CLIENT_CONN_STATEMENT,
+			gettext_noop("Age at which VACUUM should scan whole table to freeze tuples."),
+			NULL
+		},
+		&vacuum_freeze_table_age,
+		150000000, 0, 2000000000, NULL, NULL
 	},
 
 	{
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 977e13e0aff..a874b4541db 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -413,7 +413,8 @@
 #default_transaction_read_only = off
 #session_replication_role = 'origin'
 #statement_timeout = 0			# 0 is disabled
-#vacuum_freeze_min_age = 100000000
+#vacuum_freeze_min_age = 50000000
+#vacuum_freeze_table_age = 150000000
 #xmlbinary = 'base64'
 #xmloption = 'content'
 
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 13e47f0399c..64a05e738d5 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.517 2009/01/05 17:14:28 alvherre Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.518 2009/01/16 13:27:24 heikki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	200901051
+#define CATALOG_VERSION_NO	200901161
 
 #endif
diff --git a/src/include/catalog/pg_autovacuum.h b/src/include/catalog/pg_autovacuum.h
index 215af4c7398..94eb05b9250 100644
--- a/src/include/catalog/pg_autovacuum.h
+++ b/src/include/catalog/pg_autovacuum.h
@@ -6,7 +6,7 @@
  * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/pg_autovacuum.h,v 1.10 2009/01/01 17:23:56 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/pg_autovacuum.h,v 1.11 2009/01/16 13:27:24 heikki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -34,6 +34,7 @@ CATALOG(pg_autovacuum,1248) BKI_WITHOUT_OIDS
 	int4		vac_cost_limit; /* vacuum cost limit */
 	int4		freeze_min_age; /* vacuum min freeze age */
 	int4		freeze_max_age; /* max age before forcing vacuum */
+	int4		freeze_table_age; /* age at which vacuum scans whole table */
 } FormData_pg_autovacuum;
 
 /* ----------------
@@ -58,6 +59,7 @@ typedef FormData_pg_autovacuum *Form_pg_autovacuum;
 #define Anum_pg_autovacuum_vac_cost_limit			8
 #define Anum_pg_autovacuum_freeze_min_age			9
 #define Anum_pg_autovacuum_freeze_max_age			10
+#define Anum_pg_autovacuum_freeze_table_age			11
 
 /* There are no preloaded tuples in pg_autovacuum.h */
 
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index c891dc5c867..f5f6cf4aa8c 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/commands/vacuum.h,v 1.83 2009/01/01 17:23:58 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/commands/vacuum.h,v 1.84 2009/01/16 13:27:24 heikki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -122,6 +122,7 @@ typedef struct VacAttrStats
 extern PGDLLIMPORT int default_statistics_target;		/* PGDLLIMPORT for
 														 * PostGIS */
 extern int	vacuum_freeze_min_age;
+extern int	vacuum_freeze_table_age;
 
 
 /* in commands/vacuum.c */
@@ -135,9 +136,11 @@ extern void vac_update_relstats(Relation relation,
 					double num_tuples,
 					bool hasindex,
 					TransactionId frozenxid);
-extern void vacuum_set_xid_limits(int freeze_min_age, bool sharedRel,
+extern void vacuum_set_xid_limits(int freeze_min_age, int freeze_table_age,
+					  bool sharedRel,
 					  TransactionId *oldestXmin,
-					  TransactionId *freezeLimit);
+					  TransactionId *freezeLimit,
+					  TransactionId *freezeTableLimit);
 extern void vac_update_datfrozenxid(void);
 extern bool vac_is_partial_index(Relation indrel);
 extern void vacuum_delay_point(void);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ebba76fbc36..ce225f801eb 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -13,7 +13,7 @@
  * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.387 2009/01/01 17:24:00 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.388 2009/01/16 13:27:24 heikki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -2155,8 +2155,8 @@ typedef struct VacuumStmt
 	bool		full;			/* do FULL (non-concurrent) vacuum */
 	bool		analyze;		/* do ANALYZE step */
 	bool		verbose;		/* print progress info */
-	bool		scan_all;		/* force scan of all pages */
 	int			freeze_min_age; /* min freeze age, or -1 to use default */
+	int			freeze_table_age; /* age at which to scan whole table */
 	RangeVar   *relation;		/* single table to process, or NULL */
 	List	   *va_cols;		/* list of column names, or NIL for all */
 } VacuumStmt;