Date: Thu, 20 Nov 2008 14:37:06 -0500
From: Robert Treat <xzilla@users.sourceforge.net>
Subject: Re: Is it safe to reset relfrozenxid without using vacuum?
In-reply-to: <20081118214623.GT4141@alvh.no-ip.org>
To: pgsql-admin@postgresql.org
Cc: Alvaro Herrera <alvherre@commandprompt.com>,
	Arctic Toucan <arctic_toucan@hotmail.com>, tgl@sss.pgh.pa.us
Message-id: <200811201437.06925.xzilla@users.sourceforge.net>
MIME-version: 1.0
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7bit
Content-disposition: inline
References: <BAY126-W258E8EA30779618A479BFE9C120@phx.gbl>
	<BAY126-W106E7C8FDCF0368538EEBD9C120@phx.gbl>
	<20081118214623.GT4141@alvh.no-ip.org>
User-Agent: KMail/1.9.10

On Tuesday 18 November 2008 16:46:23 Alvaro Herrera wrote:
> Arctic Toucan wrote:
> > If I "know" that there are no more inserts going into those
> > partitioned tables, can I do a bulk change of their relfrozenxids
> > setting them back 500million transactions without causing problems?
> > This will mean that the relfrozenxid is not representative of the row
> > versions, but does that matter in this case(Essentially static
> > stables)?
>
> The safest most current value you can use is that of the oldest
> transaction currently running (also known as RecentXmin in the code).
> If you choose anything older than that you're safe too.
>
> I don't think you can obtain RecentXmin in SQL (short of writing a C
> function)
>

Hmm, I have a very similar problem on some of our larger dbs with years of 
archival data. I'm of the thought that someday I will have a problem that my 
db would grow large enough that it takes longer to vacuum the archival data 
(for purposes of advancing relfrozenxid, the data is never modified) than it 
takes to run through 2 billion transactions. Sounds like the solution might 
be to write such a C function to just update this directly and run that in 
cron, and avoid the vacuum mess.  

-- 
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com