Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 685696501B7 for ; Tue, 18 Nov 2008 17:33:57 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 77138-06 for ; Tue, 18 Nov 2008 17:33:54 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from bay0-omc3-s7.bay0.hotmail.com (bay0-omc3-s7.bay0.hotmail.com [65.54.246.207]) by mail.postgresql.org (Postfix) with ESMTP id 1687B6501B3 for ; Tue, 18 Nov 2008 17:33:53 -0400 (AST) Received: from BAY126-W10 ([65.55.131.45]) by bay0-omc3-s7.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Tue, 18 Nov 2008 13:33:52 -0800 Message-ID: Content-Type: multipart/alternative; boundary="_f31c0749-af80-49f9-8556-91158914ce67_" X-Originating-IP: [64.235.97.59] From: Arctic Toucan To: , CC: Subject: Re: Is it safe to reset relfrozenxid without using vacuum? Date: Tue, 18 Nov 2008 16:33:52 -0500 Importance: Normal In-Reply-To: <22056.1227031636@sss.pgh.pa.us> References: <20081118162406.GD4141@alvh.no-ip.org> <22056.1227031636@sss.pgh.pa.us> MIME-Version: 1.0 X-OriginalArrivalTime: 18 Nov 2008 21:33:52.0313 (UTC) FILETIME=[59F12290:01C949C5] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200811/107 X-Sequence-Number: 30761 --_f31c0749-af80-49f9-8556-91158914ce67_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable On a related note... =20 When I put this change in place=2C it should handle tables properly going f= orward and on a new install=2C but it looks like I have several instances w= ith "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_f= reeze_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. =20 If I "know" that there are no more inserts going into those partitioned tab= les=2C can I do a bulk change of their relfrozenxids setting them back 500m= illion transactions without causing problems? This will mean that the relfr= ozenxid is not representative of the row versions=2C but does that matter i= n this case(Essentially static stables)? =20 =20 > To: alvherre@commandprompt.com> CC: arctic_toucan@hotmail.com=3B pgsql-a= dmin@postgresql.org> Subject: Re: [ADMIN] Is it safe to reset relfrozenxid = without using vacuum? > Date: Tue=2C 18 Nov 2008 13:07:16 -0500> From: tgl@= sss.pgh.pa.us> > Alvaro Herrera writes:> > AFA= ICS this should be safe. In fact=2C in 8.3 TRUNCATE advances> > relfrozenxi= d. (Perhaps you should consider upgrading if possible.)> > ...> > Tou could= just obtain the xid of the transaction that's going to do the> > import (f= or 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=2C not th= e> 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 tab= le and take the relfrozenxid=2C rather> than the xmin=2C from its pg_class = entry.> > regards=2C tom lane =20 =20 _________________________________________________________________ --_f31c0749-af80-49f9-8556-91158914ce67_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable On a related note...
 =3B
When I put this change in place=2C it should handle tables properly going f= orward and on a new install=2C but it looks like I have several instances w= ith "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_f= reeze_age. So =3Bthe vast majority of =3Bthose tables are still goi= ng to need to be vacuumed at the same time =3Bsince the change to the t= runcation logic won't have been in place long enough =3Bto fix the relf= rozenxids.
 =3B
If I "know" that there are no more inserts going into those partitioned tab= les=2C can I do a bulk change of their =3Brelfrozenxids setting them ba= ck 500million transactions without causing problems? =3BThis will mean = that the relfrozenxid is not representative of the row versions=2C but does= that matter in this case(Essentially static stables)? =3B =3B = =3B
 =3B
 =3B
>=3B To: alvherre@commandprompt.com
>=3B CC: arctic_touc= an@hotmail.com=3B pgsql-admin@postgresql.org
>=3B Subject: Re: [ADMIN]= Is it safe to reset relfrozenxid without using vacuum?
>=3B Date: Tu= e=2C 18 Nov 2008 13:07:16 -0500
>=3B From: tgl@sss.pgh.pa.us
>=3B=
>=3B Alvaro Herrera <=3Balvherre@commandprompt.com>=3B writes:>=3B >=3B AFAICS this should be safe. In fact=2C in 8.3 TRUNCATE adva= nces
>=3B >=3B relfrozenxid. (Perhaps you should consider upgrading = if possible.)
>=3B >=3B ...
>=3B >=3B Tou could just obtain t= he xid of the transaction that's going to do the
>=3B >=3B import (f= or example by creating a temp table and getting it's xmin from
>=3B &g= t=3B pg_class)
>=3B
>=3B That seems a bit risky. 8.3 resets relf= rozenxid to RecentXmin=2C not the
>=3B current transaction's XID. The = OP's thought of taking the max existing
>=3B relfrozenxid should be sa= fe though.
>=3B
>=3B Or I guess you could make a temp table and = take the relfrozenxid=2C rather
>=3B than the xmin=2C from its pg_clas= s entry.
>=3B
>=3B regards=2C tom lane
 =3B

 =3B


= --_f31c0749-af80-49f9-8556-91158914ce67_--