Re: Too frequent warnings for wraparound failure

On Fri, Mar 11, 2005 at 12:06:16PM +0200, Milen A. Radev wrote:

> Tom Lane wrote:
>
> > So you managed to execute about 6000 transactions in those two seconds.
> > Doing what?
>
> Well that could be the problem - I'm 99.99% positive we do not execute
> so many transactions - may be 30-40 thousand per day and that's only for
> one relatively busy DB.

One of your earlier messages said 30-40 thousand *inserts*. Is the
database also receiving a lot of queries? Selects increment the
transaction counter as well if they're not part of an explicit
transaction (i.e., one started with BEGIN or its equivalent) -- in
other words if you're in the so-called "autocommit" mode.

> The example database ("xxx1") you've chosen is not used at all. As
> most of the other databases in that cluster.

The transaction counter is global, so "the number of transactions
from the cutoff XID to the current transaction's XID" increments
for every database as the counter increments.

> And another observation (closely related IMO): when I login on another
> development server/cluster and execute the same query I get an increment
> for every database for every query execution. But there the version is
> 8.0.1.

Right -- see above about selects. If you're in autocommit mode
then each statement is a separate transaction.

If you really are executing that many transactions, then you might
need to vacuum more than once a day to avoid the warnings. Another
possibility would be to see if multiple SELECT statements can be
wrapped in a single transaction. For example, if one application
query requires many database queries, then you might be able to do
them all in one transaction. Sometimes it's desirable to do so for
other reasons: if you use the SERIALIZABLE isolation level, for
example, then all queries in a transaction will see the same snapshot
of the database. That is, two SELECTs will see the same thing even
if another transaction commits changes in between. That isolation
can be an important part of producing output that doesn't contain
inconsistencies.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/