From: Arctic Toucan <arctic_toucan@hotmail.com>
To: tgl@sss.pgh.pa.us
To: alvherre@commandprompt.com
Cc: pgsql-admin@postgresql.org
Subject: Re: Is it safe to reset relfrozenxid without using vacuum?
Date: Tue, 18 Nov 2008 16:33:52 -0500
Message-ID: <BAY126-W106E7C8FDCF0368538EEBD9C120@phx.gbl> (raw)
In-Reply-To: <22056.1227031636@sss.pgh.pa.us>
References: <BAY126-W258E8EA30779618A479BFE9C120@phx.gbl>
<20081118162406.GD4141@alvh.no-ip.org>
<22056.1227031636@sss.pgh.pa.us>
On a related note...
When I put this change in place, it should handle tables properly going forward and on a new install, but it looks like I have several instances with "clones" of this DB where there are 1000's of tables all with the same relfrozenxid and within just a few million transactions of the autovacuum_freeze_age. So the vast majority of those tables are still going to need to be vacuumed at the same time since the change to the truncation logic won't have been in place long enough to fix the relfrozenxids.
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)?
> To: alvherre@commandprompt.com> CC: arctic_toucan@hotmail.com; pgsql-admin@postgresql.org> Subject: Re: [ADMIN] Is it safe to reset relfrozenxid without using vacuum? > Date: Tue, 18 Nov 2008 13:07:16 -0500> From: tgl@sss.pgh.pa.us> > Alvaro Herrera <alvherre@commandprompt.com> writes:> > AFAICS this should be safe. In fact, in 8.3 TRUNCATE advances> > relfrozenxid. (Perhaps you should consider upgrading if possible.)> > ...> > Tou could just obtain the xid of the transaction that's going to do the> > import (for example by creating a temp table and getting it's xmin from> > pg_class)> > That seems a bit risky. 8.3 resets relfrozenxid to RecentXmin, not the> current transaction's XID. The OP's thought of taking the max existing> relfrozenxid should be safe though.> > Or I guess you could make a temp table and take the relfrozenxid, rather> than the xmin, from its pg_class entry.> > regards, tom lane
_________________________________________________________________
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: arctic_toucan@hotmail.com, tgl@sss.pgh.pa.us, alvherre@commandprompt.com
Subject: Re: Is it safe to reset relfrozenxid without using vacuum?
In-Reply-To: <BAY126-W106E7C8FDCF0368538EEBD9C120@phx.gbl>
References: <BAY126-W258E8EA30779618A479BFE9C120@phx.gbl> <20081118162406.GD4141@alvh.no-ip.org> <22056.1227031636@sss.pgh.pa.us> <BAY126-W106E7C8FDCF0368538EEBD9C120@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox