Re: [PATCHES] Re: [pgsql-patches] [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Bruce Momjian wrote: > What is the status of this patch? Alvaro is currently out of town. He should be able to provide more info next week. Sincerely, Joshua D. Drake > > --- > > Alvaro Herrera wrote: >> 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 Herrerahttp://www.CommandPrompt.com/ >> The PostgreSQL Company - Command Prompt, Inc. > > [ Attachment, skipping... ] > >> ---(end of broadcast)--- >> TIP 5: don't forget to increase your free space map settings > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] Re: [pgsql-patches] [GENERAL] Corrupt database? 8.1/FreeBSD6.0
What is the status of this patch? --- Alvaro Herrera wrote: > 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 Herrerahttp://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [pgsql-patches] [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Alvaro Herrera <[EMAIL PROTECTED]> writes: >>> Ok, it does what it's intended to do. But in testing it I also >>> confirmed that a database-wide vacuum creates a pgstat entry for it and >>> for all tables in it. Is this something we want to prevent? >> >> That's odd, because I didn't see any such thing when I tested in CVS tip >> the other day. Or did you have stats_block_level turned on? > Yes, I turned it on for this test. Well, the vacuums certainly accounted for I/O, so I suppose this is reasonable behavior. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [pgsql-patches] [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Ok, it does what it's intended to do. But in testing it I also > > confirmed that a database-wide vacuum creates a pgstat entry for it and > > for all tables in it. Is this something we want to prevent? > > That's odd, because I didn't see any such thing when I tested in CVS tip > the other day. Or did you have stats_block_level turned on? Yes, I turned it on for this test. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [pgsql-patches] [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Ok, it does what it's intended to do. But in testing it I also > confirmed that a database-wide vacuum creates a pgstat entry for it and > for all tables in it. Is this something we want to prevent? That's odd, because I didn't see any such thing when I tested in CVS tip the other day. Or did you have stats_block_level turned on? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [pgsql-patches] [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Alvaro Herrera wrote: > 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. Ok, it does what it's intended to do. But in testing it I also confirmed that a database-wide vacuum creates a pgstat entry for it and for all tables in it. Is this something we want to prevent? I'll apply this patch later today to the 8.1 branch unless somebody objects. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [pgsql-patches] [GENERAL] Corrupt database? 8.1/FreeBSD6.0
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 Herrerahttp://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 - 1.5.2.6
--- src/backend/postmaster/autovacuum.c 12 Jan 2007 15:49:52 -
*** 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;
Vacu
