Allow the pgstat views to show toast tables as well as regular tables

tglsfdc · tglsfdc · commit 87808aef05c9 · 2005-08-15T16:25:19.000Z
(the stats system has always collected this info, but the views were
filtering it out).  Modify autovacuum so that over-threshold activity
in a toast table can trigger a VACUUM of the parent table, even if the
parent didn't appear to need vacuuming itself.  Per discussion a month
or so back about "short, wide tables".
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 1996-2005, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.19 2005/08/13 19:02:33 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.20 2005/08/15 16:25:17 tgl Exp $
  */
 
 CREATE VIEW pg_roles AS 
@@ -190,7 +190,7 @@ CREATE VIEW pg_stat_all_tables AS
     FROM pg_class C LEFT JOIN 
          pg_index I ON C.oid = I.indrelid 
          LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
-    WHERE C.relkind = 'r' 
+    WHERE C.relkind IN ('r', 't')
     GROUP BY C.oid, N.nspname, C.relname;
 
 CREATE VIEW pg_stat_sys_tables AS 
@@ -223,7 +223,7 @@ CREATE VIEW pg_statio_all_tables AS
             pg_class T ON C.reltoastrelid = T.oid LEFT JOIN 
             pg_class X ON T.reltoastidxid = X.oid 
             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
-    WHERE C.relkind = 'r' 
+    WHERE C.relkind IN ('r', 't')
     GROUP BY C.oid, N.nspname, C.relname, T.oid, X.oid;
 
 CREATE VIEW pg_statio_sys_tables AS 
@@ -248,7 +248,7 @@ CREATE VIEW pg_stat_all_indexes AS
             pg_index X ON C.oid = X.indrelid JOIN 
             pg_class I ON I.oid = X.indexrelid 
             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
-    WHERE C.relkind = 'r';
+    WHERE C.relkind IN ('r', 't');
 
 CREATE VIEW pg_stat_sys_indexes AS 
     SELECT * FROM pg_stat_all_indexes 
@@ -272,7 +272,7 @@ CREATE VIEW pg_statio_all_indexes AS
             pg_index X ON C.oid = X.indrelid JOIN 
             pg_class I ON I.oid = X.indexrelid 
             LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
-    WHERE C.relkind = 'r';
+    WHERE C.relkind IN ('r', 't');
 
 CREATE VIEW pg_statio_sys_indexes AS 
     SELECT * FROM pg_statio_all_indexes 
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
@@ -10,15 +10,15 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/postmaster/autovacuum.c,v 1.3 2005/08/11 21:11:44 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/postmaster/autovacuum.c,v 1.4 2005/08/15 16:25:17 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
 #include "postgres.h"
 
 #include <signal.h>
-#include <time.h>
 #include <sys/types.h>
+#include <time.h>
 #include <unistd.h>
 
 #include "access/genam.h"
@@ -86,6 +86,7 @@ typedef struct autovac_dbase
 typedef struct autovac_table
 {
 	Oid			relid;
+	Oid			toastrelid;
 	bool		dovacuum;
 	bool		doanalyze;
 	int			vacuum_cost_delay;
@@ -101,8 +102,10 @@ static void process_whole_db(void);
 static void do_autovacuum(PgStat_StatDBEntry *dbentry);
 static List *autovac_get_database_list(void);
 static void test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
-			 Form_pg_class classForm, Form_pg_autovacuum avForm,
-			 List **vacuum_tables);
+								 Form_pg_class classForm,
+								 Form_pg_autovacuum avForm,
+								 List **vacuum_tables,
+								 List **toast_table_ids);
 static void autovacuum_do_vac_analyze(List *relids, bool dovacuum,
 									  bool doanalyze, bool freeze);
 
@@ -386,12 +389,21 @@ AutoVacMain(int argc, char *argv[])
 
 	if (db)
 	{
+		/*
+		 * Report autovac startup to the stats collector.  We deliberately
+		 * do this before InitPostgres, so that the last_autovac_time will
+		 * get updated even if the connection attempt fails.  This is to
+		 * prevent autovac from getting "stuck" repeatedly selecting an
+		 * unopenable database, rather than making any progress on stuff
+		 * it can connect to.
+		 */
+		pgstat_report_autovac(db->oid);
+
 		/*
 		 * Connect to the selected database
 		 */
 		InitPostgres(db->name, NULL);
 		SetProcessingMode(NormalProcessing);
-		pgstat_report_autovac();
 		set_ps_display(db->name);
 		ereport(LOG,
 				(errmsg("autovacuum: processing database \"%s\"", db->name)));
@@ -538,6 +550,7 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
 	HeapTuple		tuple;
 	HeapScanDesc	relScan;
 	List		   *vacuum_tables = NIL;
+	List		   *toast_table_ids = NIL;
 	ListCell *cell;
 	PgStat_StatDBEntry *shared;
 
@@ -558,9 +571,25 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
 	classRel = heap_open(RelationRelationId, AccessShareLock);
 	avRel = heap_open(AutovacuumRelationId, AccessShareLock);
 
+	/*
+	 * Scan pg_class and determine which tables to vacuum.
+	 *
+	 * The stats subsystem collects stats for toast tables independently
+	 * of the stats for their parent tables.  We need to check those stats
+	 * since in cases with short, wide tables there might be proportionally
+	 * much more activity in the toast table than in its parent.
+	 *
+	 * Since we can only issue VACUUM against the parent table, we need to
+	 * transpose a decision to vacuum a toast table into a decision to vacuum
+	 * its parent.  There's no point in considering ANALYZE on a toast table,
+	 * either.  To support this, we keep a list of OIDs of toast tables that
+	 * need vacuuming alongside the list of regular tables.  Regular tables
+	 * will be entered into the table list even if they appear not to need
+	 * vacuuming; we go back and re-mark them after finding all the
+	 * vacuumable toast tables.
+	 */
 	relScan = heap_beginscan(classRel, SnapshotNow, 0, NULL);
 
-	/* Scan pg_class looking for tables to vacuum */
 	while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
 	{
 		Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
@@ -571,9 +600,9 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
 		ScanKeyData	entry[1];
 		Oid			relid;
 
-		/* Skip non-table entries. */
-		/* XXX possibly allow RELKIND_TOASTVALUE entries here too? */
-		if (classForm->relkind != RELKIND_RELATION)
+		/* Consider only regular and toast tables. */
+		if (classForm->relkind != RELKIND_RELATION &&
+			classForm->relkind != RELKIND_TOASTVALUE)
 			continue;
 
 		/*
@@ -607,7 +636,7 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
 								   HASH_FIND, NULL);
 
 		test_rel_for_autovac(relid, tabentry, classForm, avForm,
-							 &vacuum_tables);
+							 &vacuum_tables, &toast_table_ids);
 
 		systable_endscan(avScan);
 	}
@@ -625,6 +654,22 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
 
 		CHECK_FOR_INTERRUPTS();
 
+		/*
+		 * Check to see if we need to force vacuuming of this table because
+		 * its toast table needs it.
+		 */
+		if (OidIsValid(tab->toastrelid) && !tab->dovacuum &&
+			list_member_oid(toast_table_ids, tab->toastrelid))
+		{
+			tab->dovacuum = true;
+			elog(DEBUG2, "autovac: VACUUM %u because of TOAST table",
+				 tab->relid);
+		}
+
+		/* Otherwise, ignore table if it needs no work */
+		if (!tab->dovacuum && !tab->doanalyze)
+			continue;
+
 		/* Set the vacuum cost parameters for this table */
 		VacuumCostDelay = tab->vacuum_cost_delay;
 		VacuumCostLimit = tab->vacuum_cost_limit;
@@ -643,7 +688,7 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
  * test_rel_for_autovac
  *
  * Check whether a table needs to be vacuumed or analyzed.  Add it to the
- * output list if so.
+ * appropriate output list if so.
  *
  * A table needs to be vacuumed if the number of dead tuples exceeds a
  * threshold.  This threshold is calculated as
@@ -670,7 +715,8 @@ static void
 test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
 					 Form_pg_class classForm,
 					 Form_pg_autovacuum avForm,
-					 List **vacuum_tables)
+					 List **vacuum_tables,
+					 List **toast_table_ids)
 {
 	Relation		rel;
 	float4			reltuples;	/* pg_class.reltuples */
@@ -764,12 +810,10 @@ test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
 	 * will be reset too.
 	 */
 
-	elog(DEBUG2, "%s: vac: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)",
+	elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)",
 		 RelationGetRelationName(rel),
 		 vactuples, vacthresh, anltuples, anlthresh);
 
-	Assert(CurrentMemoryContext == AutovacMemCxt);
-
 	/* Determine if this table needs vacuum or analyze. */
 	dovacuum = (vactuples > vacthresh);
 	doanalyze = (anltuples > anlthresh);
@@ -778,23 +822,40 @@ test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
 	if (relid == StatisticRelationId)
 		doanalyze = false;
 
-	if (dovacuum || doanalyze)
+	Assert(CurrentMemoryContext == AutovacMemCxt);
+
+	if (classForm->relkind == RELKIND_RELATION)
 	{
-		autovac_table *tab;
+		if (dovacuum || doanalyze)
+			elog(DEBUG2, "autovac: will%s%s %s",
+				 (dovacuum ? " VACUUM" : ""),
+				 (doanalyze ? " ANALYZE" : ""),
+				 RelationGetRelationName(rel));
 
-		elog(DEBUG2, "will%s%s %s",
-			 (dovacuum ? " VACUUM" : ""),
-			 (doanalyze ? " ANALYZE" : ""),
-			 RelationGetRelationName(rel));
+		/*
+		 * we must record tables that have a toast table, even if we currently
+		 * don't think they need vacuuming.
+		 */
+		if (dovacuum || doanalyze || OidIsValid(classForm->reltoastrelid))
+		{
+			autovac_table *tab;
 
-		tab = (autovac_table *) palloc(sizeof(autovac_table));
-		tab->relid = relid;
-		tab->dovacuum = dovacuum;
-		tab->doanalyze = doanalyze;
-		tab->vacuum_cost_limit = vac_cost_limit;
-		tab->vacuum_cost_delay = vac_cost_delay;
+			tab = (autovac_table *) palloc(sizeof(autovac_table));
+			tab->relid = relid;
+			tab->toastrelid = classForm->reltoastrelid;
+			tab->dovacuum = dovacuum;
+			tab->doanalyze = doanalyze;
+			tab->vacuum_cost_limit = vac_cost_limit;
+			tab->vacuum_cost_delay = vac_cost_delay;
 
-		*vacuum_tables = lappend(*vacuum_tables, tab);
+			*vacuum_tables = lappend(*vacuum_tables, tab);
+		}
+	}
+	else
+	{
+		Assert(classForm->relkind == RELKIND_TOASTVALUE);
+		if (dovacuum)
+			*toast_table_ids = lappend_oid(*toast_table_ids, relid);
 	}
 
 	RelationClose(rel);
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
@@ -13,7 +13,7 @@
  *
  *	Copyright (c) 2001-2005, PostgreSQL Global Development Group
  *
- *	$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.105 2005/08/11 21:11:44 tgl Exp $
+ *	$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.106 2005/08/15 16:25:17 tgl Exp $
  * ----------
  */
 #include "postgres.h"
@@ -651,18 +651,20 @@ pgstat_beterm(int pid)
  * pgstat_report_autovac() -
  *
  * 	Called from autovacuum.c to report startup of an autovacuum process.
+ *	We are called before InitPostgres is done, so can't rely on MyDatabaseId;
+ *	the db OID must be passed in, instead.
  * ----------
  */
 void
-pgstat_report_autovac(void)
+pgstat_report_autovac(Oid dboid)
 {
 	PgStat_MsgAutovacStart msg;
 
 	if (pgStatSock < 0)
 		return;
 
 	pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_AUTOVAC_START);
-	msg.m_databaseid = MyDatabaseId;
+	msg.m_databaseid = dboid;
 	msg.m_start_time = GetCurrentTimestamp();
 
 	pgstat_send(&msg, sizeof(msg));
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.298 2005/08/13 19:02:34 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.299 2005/08/15 16:25:18 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	200508131
+#define CATALOG_VERSION_NO	200508151
 
 #endif
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
@@ -5,7 +5,7 @@
  *
  *	Copyright (c) 2001-2005, PostgreSQL Global Development Group
  *
- *	$PostgreSQL: pgsql/src/include/pgstat.h,v 1.35 2005/08/11 21:11:49 tgl Exp $
+ *	$PostgreSQL: pgsql/src/include/pgstat.h,v 1.36 2005/08/15 16:25:18 tgl Exp $
  * ----------
  */
 #ifndef PGSTAT_H
@@ -384,7 +384,7 @@ extern void pgstat_bestart(void);
 extern void pgstat_ping(void);
 extern void pgstat_report_activity(const char *what);
 extern void pgstat_report_tabstat(void);
-extern void pgstat_report_autovac(void);
+extern void pgstat_report_autovac(Oid dboid);
 extern void pgstat_report_vacuum(Oid tableoid, bool shared,
 								 bool analyze, PgStat_Counter tuples);
 extern void pgstat_report_analyze(Oid tableoid, bool shared,
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out

Original file line number	Diff line number	Diff line change
`@@ -37,7 +37,7 @@`
`37`	`37`	`* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group`
`38`	`38`	`* Portions Copyright (c) 1994, Regents of the University of California`
`39`	`39`	`*`
`40`		`- * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.298 2005/08/13 19:02:34 tgl Exp $`
	`40`	`+ * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.299 2005/08/15 16:25:18 tgl Exp $`
`41`	`41`	`*`
`42`	`42`	`*-------------------------------------------------------------------------`
`43`	`43`	`*/`
`@@ -53,6 +53,6 @@`
`53`	`53`	`*/`
`54`	`54`
`55`	`55`	`/* yyyymmddN */`
`56`		`-#define CATALOG_VERSION_NO 200508131`
	`56`	`+#define CATALOG_VERSION_NO 200508151`
`57`	`57`
`58`	`58`	`#endif`