Martijn van Oosterhout wrote:
> On Mon, Oct 24, 2005 at 11:25:00AM -0600, Michael Fuhr wrote:
>
>>PostgreSQL 8.1 makes checks to avoid data loss due to transaction
>>ID wraparound, but there's one situation I'm not sure how it handles:
>>when a transaction is so long-lived that it would appear to be in
>>the future of newly-created transactions due to wraparound. I'd
>>have to dig into the source code to find out if that's possible,
>>and if so, what happens. Maybe one of the developers will comment.
>
>
> To avoid this you need to do a VACUUM FULL over the database at least
> once every two billion transactions (not statements or tuples,
> transactions). To that end, the server begins complaining after one
> billion. I've never seen this in practice. Perhaps you could calculate
> how long it would take to do that many transactions. Most systems will
> never see it...
>
> Hope this helps,
Docs section 21.1.3 (in 8.0 docs anyway, it seems to have moved to
22.1.3 for 8.1 docs) makes no mention of VACUUM FULL, only VACUUM is
required.
VACUUM FULL is much more expensive, because it actually moves tuples
on-disk rather than just marking them. So it's fortunate that a VACUUM
FULL is not required. As far as I can tell, VACUUM FULL is never
required for normal database activity, even a 24/7/365.25 system.
I think Michael Fuhr was asking something a little different. The same
section of the docs appears to clear it up a little:
"...the normal XID space is circular with no endpoint. Therefore, once a
row version has been created with a particular normal XID, the row
version will appear to be "in the past" for the next two billion
transactions,..."
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..."
The word that stands out in that sentence to me is "new". So,
presumably, it would continue that one long transaction indefinitely,
refusing new transactions. That isn't great, but it's hard to imagine
leaving a transaction open for 2 billion other transactions. And at
least it doesn't cause data loss.
Is there any easy way to see what transactions are currently open, how
old the XID is, and what PID is executing it?
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?
Regards,
Jeff Davis