I know questions like this have been asked before, but I hadn’t seen one quite from the same perspective (although I’m sure it’s out there somewhere)…
We have a database which has one long involved procedure early in the morning that updates all sorts of things, moves data around, deletes some stuff, alters some DDL - you name it, it does it. The rest of the day, the database is read only. Autovacuum is not on, it was killing performance for it to kick on in the middle of the proc. Vacuum is run right before the long procedure. We typically wait until the guy onsite verifies the procedure went smoothly and then vacuum (Why? Because we’ve read enough of “well, you would have been able to restore that if the autovacuum wasn’t running”. We have a backup so this is just a checking before vacuuming is technically unneeded.). Don’t get me wrong, I’m a big autovacuum fan, just not for this specific case.
The transaction itself works flawlessly, but every once and awhile the data the it uploads from comes in flawed and we have to find a way to reset it. This reset involves restoring a backup that was taken right before the proc started. If we had the xid of the long running transaction, is there a better way to reset it right before that transaction happened? Restoring the backup is a lengthy process because several of the tables that are affected are rather large.
Chris Spotts