Re: pg_dump, MVCC and consistency

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(at)svana(dot)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.