Re: [PATCHES] Re: [pgsql-patches] [GENERAL] Corrupt database? 8.1/FreeBSD6.0

2007-02-02 Thread Joshua D. Drake
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

2007-02-02 Thread Bruce Momjian

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

2007-01-12 Thread Tom Lane
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

2007-01-12 Thread Alvaro Herrera
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

2007-01-12 Thread Tom Lane
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

2007-01-12 Thread Alvaro Herrera
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

2007-01-12 Thread Alvaro Herrera
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