We have migrated to new Google sites!
Greenplum’s MVCC transaction semantics depend on comparing transaction ID (XID) numbers to determine visibility to other transactions. Because transaction ID numbers have an upper limit, a Greenplum system that runs more than 4 billion transactions experiences transaction ID wraparound: the XID counter reverts to zero, and past transactions appear to be in the future. This means past transactions’ outputs become invisible. Therefore, it is necessary to VACUUM every table in every database at least once per two billion transactions.
Important: Greenplum Database monitors transaction IDs. If you do not vacuum the database regularly, Greenplum Database will generate a warning and error.
WARNING: database "database_name" must be vacuumed within number_of_transactions transactions
When the warning is issued, a VACUUM operation is required. If a VACUUM operation is not performed, Greenplum Database stops creating transactions when it reaches a limit prior to when transaction ID wraparound occurs.
FATAL: database is not accepting commands to avoid wraparound data loss in database "database_name"
Greenplum Database issues above error when it stops creating transactions to avoid possible data loss
The Greenplum Database configuration parameter xid_warn_limit controls when the warning is displayed. The parameter xid_stop_limit controls when Greenplum Database stops creating transactions.
Recovering from a Transaction ID Limit Error
When Greenplum Database reaches the xid_stop_limit transaction ID limit due to infrequent VACUUM maintenance, it becomes unresponsive. To recover from this situation, perform the following steps as database administrator:
1.Shut down Greenplum Database.
2.Temporarily lower the xid_stop_limit by 10,000,000.
3.Start Greenplum Database.
4.Run VACUUM FREEZE on all affected databases.
5.Reset the xid_stop_limit to its original value.
6.Restart Greenplum Database.