Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Now , I read the document about "Transaction ID Wraparound " , but there are something that I really don't understand, the document is the following url http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

23.1.4. Preventing Transaction ID Wraparound Failures

PostgreSQL's MVCC transaction semantics depend on being able to compare transaction ID (XID) numbers: a row version with an insertion XID greater than the current transaction's XID is "in the future" and should not be visible to the current transaction. But since transaction IDs have limited size (32 bits) a cluster that runs for a long time (more than 4 billion transactions) would suffer transaction ID wraparound: the XID counter wraps around to zero, and all of a sudden transactions that were in the past appear to be in the future — which means their output become invisible. In short, catastrophic data loss. (Actually the data is still there, but that's cold comfort if you cannot get at it.) To avoid this, it is necessary to vacuum every table in every database at least once every two billion transactions.

I don't understand the statements "would suffer transaction ID wraparound: the XID counter wraps around to zero, and all of a sudden transactions that were in the past appear to be in the future — which means their output become invisible"

Can someone explain this ? Why after the database suffers transaction ID wraparound would transactions that were in the past appear to be in the future ? In short, I want to know if the PostgreSQL will in the " data loss" situation after transaction ID wraparound by autovacuum。

For my personal views, we can get the current transaction ID by using txid_current() function whoes output is 64 bit and will not be cycled.So I think the Insertion transaction ID of tuples which knows as xmin will nerver greater than the xid which get by txid_current() function. Except that you will use pg_resetxlog reset reset transaction ID after shuting down PostgreSQL Server. Am I right ? Thanks

share|improve this question
    
I think your latest edit should probably be a new question if possible –  Jack Douglas Oct 3 '11 at 14:35

2 Answers 2

up vote 6 down vote accepted

Why after the database suffers transaction ID wraparound would transactions that were in the past appear to be in the future ?

They don't. The quoted text just explains why postgres needs to use modulo 231 arithmatic (which means transactions can wrap around as long as old transactions are 'frozen' early enough):

Normal XIDs are compared using modulo-2^31 arithmetic. This means that for every normal XID, there are two billion XIDs that are "older" and two billion that are "newer"

to be specific:

old row versions must be reassigned the XID FrozenXID sometime before they reach the two-billion-transactions-old mark

or wrapping the XID around would cause things to break. To prevent that, postgres will start to emit warnings, and eventually shut down and refuse to start new transactons if necessary:

If for some reason autovacuum fails to clear old XIDs from a table, the system will begin to emit warning messages like this when the database's oldest XIDs reach ten million transactions from the wraparound point:

WARNING:  database "mydb" must be vacuumed within 177009986 transactions 
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in "mydb". 

(A manual VACUUM should fix the problem, as suggested by the hint; but note that the VACUUM must be performed by a superuser, else it will fail to process system catalogs and thus not be able to advance the database's datfrozenxid.) If these warnings are ignored, the system will shut down and refuse to start any new transactions once there are fewer than 1 million transactions left until wraparound

In other words "transactions that were in the past appear to be in the future" and "data loss" are entirely theoretical and will not be caused by transaction ID wraparound in practice.

share|improve this answer
    
Actually I want to know if the PostgreSQL will in the " data loss" situation after transaction ID wraparound by autovacuum。 –  francs Oct 2 '11 at 1:39
    
"data loss" will never happen - postgres will shut down if necessary to prevent it (see edit). –  Jack Douglas Oct 3 '11 at 8:38
    
thanks you for your replay ,I edited my question again. –  francs Oct 3 '11 at 13:33
    
not since my edit though? I'm not certain I understand your edit anyway - what exactly are you worried about? –  Jack Douglas Oct 3 '11 at 13:38
    
Recentaly Our PostgreSQL DB get into the situation "ERROR: database is not accepting commands to avoid wraparound data loss in database xxx", And we have no other choice, Later we shutdown the db and vacuum all the dbs in singlle mode ,after that it's ok. –  francs Oct 3 '11 at 13:43

The block you pasted seems to answer the question. It all depends on the logic that is used to hide 'future' transactions.

The transaction ID (XID) counter is limited to 32 bits and if it ever reaches that next number, instead of replacing the old max transaction, it starts fresh at zero.

Well, now it's zero, so PostgreSQL is hiding all of transactions > 0 from it. So even though Transaction #2,147,483,633 happened 20 seconds ago, PostgreSQL thinks it won't happen for another 2,147,483,633 transactions.

share|improve this answer
    
thanks ,actually I want to know if the PostgreSQL will in the " data loss" situation after transaction ID wraparound by autovacuum。 –  francs Oct 2 '11 at 1:41
    
And PostgreSQL is hiding all of transactions > 0 from it, you mean after transaction ID wapround, ther are data lose because PostreSQL is hiding transcations > 0. ? –  francs Oct 2 '11 at 3:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.