On Sunday, May 25, 2014, Heikki Linnakangas <
hlinnakangas@vmware.com> wrote:
While debugging the B-tree bug that Jeff Janes reported (http://www.postgresql.org/message-id/CAMkU=1y=VwF07Ay+Cpqk_7FpiHRctmssV9y99SBGhitkXPbf8g@mail.gmail.com), a new issue came up:
If you reach the xidStopLimit, and try to run VACUUM, it fails with error:
jjanes=# vacuum;
ERROR: database is not accepting commands to avoid wraparound data loss in database "jjanes"
HINT: Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions.
This problem also afflicted me in 9.3 and 9.2 (and probably existed further back too). I figured it was mostly a barrier to more effective testing, but it would be nice to have it fixed.
But I don't understand how you encountered this. I only ran into it when the vacuum had already been started, but not yet completed, by the time the limit was reached. Once it is already reached, how do you even get the vacuum to start? Doesn't it error out right at the beginning.
Jeff's database seems to have wrapped around already, because after fixing the above, I get this:
Do you have the patch to fix this?
jjanes=# vacuum;
WARNING: some databases have not been vacuumed in over 2 billion transactions
DETAIL: You might have already suffered transaction-wraparound data loss.
VACUUM
This is odd. When I apply your patch from the other thread to fix the vacuum, and then start up in single-user mode, I can run vacuum to completion and re-open the database. When I first start it up, it says it needs to be vacuumed within 999,935 transactions. There is no indication that it has already suffered a wrap around, just that it was about to do so.
We do not truncate clog when wraparound has already happened, so we never get past that point. Jeff advanced XID counter aggressively with some custom C code, so hitting the actual wrap-around is a case of "don't do that". Still, the case is quite peculiar: pg_controldata says that nextXid is 4/1593661139. The oldest datfrozenxid is equal to that, 1593661139. So ISTM he managed to not just wrap around, but execute 2 billion more transactions after the wraparound and reach datfrozenxid again. I'm not sure how that happened.
If it had actually undergone an undetected wraparound, wouldn't data be disappearing and appearing inappropriately? I think the testing harness should have detected that inconsistency.
(Also, the max setting for JJ_xid during the test run was 40, so I don't think it could have blown right past the 1,000,000 safety margin and out the other side without triggering a shutdown).
Cheers,
Jeff