Re: How to avoid transaction ID wrap

On Thu, Jun 08, 2006 at 01:30:42AM +0300, Hannu Krosing wrote:
> ??hel kenal p??eval, T, 2006-06-06 kell 13:53, kirjutas Christopher
> Browne:
> > >> We have triggers that fire is something interesting is found on insert.
> > >> We want this thing to run for a log time.
> > >> From the numbers, you can see the PostgreSQL database is VERY loaded.
> > >> Running VACUUM may not always be possible without losing data.
> > >
> > > why ? just run it with very friendly delay settings.
> >
> > "Friendly delay settings" can have adverse effects; it is likely to
> > make vacuum run on the order of 3x as long, which means that if you
> > have a very large table that takes 12h to VACUUM, "vacuum delay" will
> > increase that to 36h, which means you'll have a transaction open for
> > 36h.
> >
> > That'll be very evil, to be sure...
>
> Not always. I know that it is evil in slony1 context, but often it *is*
> possible to design your system in a way where a superlong transaction is
> almost unnoticable.
>
> Long transactions are evil in case they cause some fast-changing table
> to grow its storage size several orders of magnitude, but if that is not
> the case then they just run there in backgroun with no ill effects,
> especially do-nothing transactions like vacuum.

Plus, if the only issue here is in fact the long-running transaction for
vacuum, there's other ways to address that which would be a lot less
intrusive than doing something like going to 64 bit XIDs. IIRC, in 8.2
vacuum will start a new transaction every time it fills up
maintenance_work_mem, so just setting that low could solve the problem
(at the expense of a heck of a lot of extra IO).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461