Transaction Id wraparounds

On Thu, Sep 05, 2002 at 11:27:38AM -0400, Amin Abdulghani wrote:

Hi,

What fast, efficient options are available for preventing
transaction Id wraparounds for a growing database (mainly
inserts). From the administrator's guide I understand a
system wide VACUUM or VACUUM FREEZE would perform the job
but for a large database (>10GB and growing) this takes a
long time to run.

As I understand it, _only_ vacuum or dump and initdb will solve this
problem.

But who cares if vacuum takes a long time? The transaction
wraparound stuff is only available in 7.2 and later, so oyu can use
the non-blocking vacuum in 7.2, and let it run for a week if it takes
that long.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Amin Abdulghani <amin@quantiva.com> writes:

From the administrator's guide I understand a
system wide VACUUM or VACUUM FREEZE would perform the job
but for a large database (>10GB and growing) this takes a
long time to run.

So? You don't have to do it often, and it doesn't lock your
tables against normal operations ... so I don't really see the
objection. I would not bother with FREEZE unless you are trying
to make a template database.

what should be the criteria to check when this issue needs
to be addressed.

When age(datfrozenxid) approaches 2 billion, it's time. I believe
vacuum will start warning you well before that.

regards, tom lane

Thanks for the replies. As for the warning message,
probably
it may be better to put it at the DB connection time.
Another option might be to possibly do a forced automatic
VACUUM in such cases and thus avoid any potential
integrity problem. This could possibly be a configuration
option FORCE_VACUUM if age(FrozenXID)>threshold.

Thanks..
Amin
On Thu, 05 Sep 2002 12:08:04 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote: