Re: pg_subtrans keeps bloating up in the standby

On Mon, 2010-08-30 at 09:59 +0300, Heikki Linnakangas wrote:
> On 27/08/10 20:17, Fujii Masao wrote:
> > Yes. StartupXLOG calls that before bgwriter is invoked. That is, we can
> > ensure that StartupSUBTRANS has always been done before bgwriter
> > performs a restartpoint.
> 
> Hmm, the comment in CreateCheckpoint() isn't totally accurate either:
> 
> >  * Truncate pg_subtrans if possible.  We can throw away all data before
> >  * the oldest XMIN of any running transaction.    No future transaction will
> >  * attempt to reference any pg_subtrans entry older than that (see Asserts
> >  * in subtrans.c).    During recovery, though, we mustn't do this because
> >  * StartupSUBTRANS hasn't been called yet.

Yep.

> because in Hot Standby mode, StartSUBTRANS has been called already. We 
> could truncate pg_subtrans there too when hot standby is enabled. But 
> this is only about the startup checkpoint at the end of recovery, so I'm 
> inclined to not change that, not right now just before release anyway, 
> just in case we're missing something...
> 
> However, is it safe to use GetOldestXMin() during recovery? Or to put it 
> other way, is GetOldestXMin() functioning correctly during hot standby? 
> It only scans through the ProcArray, but not the known-assigned xids 
> array. That seems like an oversight that needs to be fixed.

Yes, thats correct. Otherwise the patch is fine.

I'm working on this now and will commit something shortly.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services