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@xxxxxxxxx> 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.
Attachment:
pgpNfpurDnzW9.pgp
Description: PGP signature
