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
