On Mon, Oct 24, 2005 at 02:42:09PM -0700, Jeff Davis wrote:
> So it seems that, in order for the wraparound to be a problem, the
> transaction would have to last longer than 2 billion other transactions.
>
> And if a transaction did last that long, according to the 8.1 docs (22.1.3):
>
> "...the system will shut down and refuse to execute any new transactions
> once there are fewer than 1 million transactions left until wraparound..."
I've thought about this and it seems to me that if you have a
transaction open for the duration of 2 billion other transactions, you
likely have a real problem. If even a substantial fraction of those
transactions altered any data, your tables and indexes are going to be
incredibly bloated because any VACUUM can't remove anything newer than
the oldest transaction. A billion uncleaned tuples will translate to
tens of gigabytes of useless space that can't be cleaned.
At 100 transactions per second, 2 billion transactions is eight months.
that's a very long running query (running on very out of date data).
Actually, there's some discussion in the archives about a "buffer death
spiral" [1]. If you have more dead tuples in your update chains than
number of buffers, you'll completely thrash the system just to access
them. Nasty.
> Is there any easy way to see what transactions are currently open, how
> old the XID is, and what PID is executing it?
Well, the XID info can be found in pg_database. The PID info from
pg_stat_activity which gives you the time the query started...
> And what about a transaction left open for 2PC? Does a transaction get a
> new XID if it's PREPAREd now and COMMIT PREPAREd in a year?
That I don't know. Perhaps that's why it's stops a million before
overflow. So important transactions like that or VACUUM or even just
logging in can complete.
[1] http://archives.postgresql.org/pgsql-performance/2005-10/msg00227.php
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.