Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:

> > Maybe we could forcibly activate the freeze mode on a template database?
> 
> Might not be a bad idea.  And even more to the point, forcibly disable
> analyze.

Patch implementing this (albeit untested!) attached.  I'll try to
reproduce the problem without the patch, and then test with the patch
applied.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Index: src/backend/postmaster/autovacuum.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.5.2.6
diff -c -p -r1.5.2.6 autovacuum.c
*** src/backend/postmaster/autovacuum.c	19 May 2006 15:15:38 -0000	1.5.2.6
--- src/backend/postmaster/autovacuum.c	12 Jan 2007 15:49:52 -0000
*************** autovac_get_database_list(void)
*** 482,511 ****
  }
  
  /*
!  * Process a whole database.  If it's a template database or is disallowing
!  * connection by means of datallowconn=false, then issue a VACUUM FREEZE.
!  * Else use a plain VACUUM.
   */
! static void
! process_whole_db(void)
  {
! 	Relation	dbRel;
  	ScanKeyData entry[1];
  	SysScanDesc scan;
  	HeapTuple	tup;
- 	Form_pg_database dbForm;
- 	bool		freeze;
- 
- 	/* Start a transaction so our commands have one to play into. */
- 	StartTransactionCommand();
- 
- 	 /* functions in indexes may want a snapshot set */
- 	ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
- 
- 	/*
- 	 * Clean up any dead statistics collector entries for this DB.
- 	 */
- 	pgstat_vacuum_tabstat();
  
  	dbRel = heap_open(DatabaseRelationId, AccessShareLock);
  
--- 482,500 ----
  }
  
  /*
!  * Return a palloc'ed copy of the pg_database entry for the given database.
!  * Note that no lock is retained on the entry whatsoever, so it may be stale by
!  * the time the caller inspects it.  This is sufficient for our purposes
!  * however.
   */
! static Form_pg_database
! get_pg_database_entry(Oid dbid)
  {
! 	Form_pg_database dbForm;
  	ScanKeyData entry[1];
+ 	Relation	dbRel;
  	SysScanDesc scan;
  	HeapTuple	tup;
  
  	dbRel = heap_open(DatabaseRelationId, AccessShareLock);
  
*************** process_whole_db(void)
*** 523,539 ****
  	if (!HeapTupleIsValid(tup))
  		elog(ERROR, "could not find tuple for database %u", MyDatabaseId);
  
! 	dbForm = (Form_pg_database) GETSTRUCT(tup);
! 
! 	if (!dbForm->datallowconn || dbForm->datistemplate)
! 		freeze = true;
! 	else
! 		freeze = false;
  
  	systable_endscan(scan);
  
  	heap_close(dbRel, AccessShareLock);
  
  	elog(DEBUG2, "autovacuum: VACUUM%s whole database",
  		 (freeze) ? " FREEZE" : "");
  
--- 512,555 ----
  	if (!HeapTupleIsValid(tup))
  		elog(ERROR, "could not find tuple for database %u", MyDatabaseId);
  
! 	dbForm = (Form_pg_database) palloc(sizeof(FormData_pg_database));
! 	memcpy(dbForm, GETSTRUCT(tup), sizeof(FormData_pg_database));
  
  	systable_endscan(scan);
  
  	heap_close(dbRel, AccessShareLock);
  
+ 	return dbForm;
+ }
+ 
+ /*
+  * Process a whole database.  If it's a template database or is disallowing
+  * connection by means of datallowconn=false, then issue a VACUUM FREEZE.
+  * Else use a plain VACUUM.
+  */
+ static void
+ process_whole_db(void)
+ {
+ 	Form_pg_database dbForm;
+ 	bool		freeze;
+ 
+ 	/* Start a transaction so our commands have one to play into. */
+ 	StartTransactionCommand();
+ 
+ 	 /* functions in indexes may want a snapshot set */
+ 	ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
+ 
+ 	/*
+ 	 * Clean up any dead statistics collector entries for this DB.
+ 	 */
+ 	pgstat_vacuum_tabstat();
+ 
+ 	dbForm = get_pg_database_entry(MyDatabaseId);
+ 
+ 	freeze = (!dbForm->datallowconn || dbForm->datistemplate);
+ 
+ 	pfree(dbForm);
+ 
  	elog(DEBUG2, "autovacuum: VACUUM%s whole database",
  		 (freeze) ? " FREEZE" : "");
  
*************** do_autovacuum(PgStat_StatDBEntry *dbentr
*** 564,569 ****
--- 580,587 ----
  	List	   *toast_table_ids = NIL;
  	ListCell   *cell;
  	PgStat_StatDBEntry *shared;
+ 	Form_pg_database dbForm;
+ 	bool		istemplate;
  
  	/* Start a transaction so our commands have one to play into. */
  	StartTransactionCommand();
*************** do_autovacuum(PgStat_StatDBEntry *dbentr
*** 579,584 ****
--- 597,610 ----
  	pgstat_vacuum_tabstat();
  
  	/*
+ 	 * In a template database, we need to avoid putting our Xid in any table,
+ 	 * so disallow analyzes and force use of VACUUM FREEZE.
+ 	 */
+ 	dbForm = get_pg_database_entry(MyDatabaseId);
+ 	istemplate = (!dbForm->datallowconn || dbForm->datistemplate);
+ 	pfree(dbForm);
+ 
+ 	/*
  	 * StartTransactionCommand and CommitTransactionCommand will automatically
  	 * switch to other contexts.  We need this one to keep the list of
  	 * relations to vacuum/analyze across transactions.
*************** do_autovacuum(PgStat_StatDBEntry *dbentr
*** 694,703 ****
  		VacuumCostDelay = tab->vacuum_cost_delay;
  		VacuumCostLimit = tab->vacuum_cost_limit;
  
  		autovacuum_do_vac_analyze(list_make1_oid(tab->relid),
  								  tab->dovacuum,
! 								  tab->doanalyze,
! 								  false);
  	}
  
  	/* Finally close out the last transaction. */
--- 720,730 ----
  		VacuumCostDelay = tab->vacuum_cost_delay;
  		VacuumCostLimit = tab->vacuum_cost_limit;
  
+ 		/* in a template database, we never analyze and force freezing */
  		autovacuum_do_vac_analyze(list_make1_oid(tab->relid),
  								  tab->dovacuum,
! 								  !istemplate && tab->doanalyze,
! 								  istemplate);
  	}
  
  	/* Finally close out the last transaction. */
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to