|
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 |
