Re: How about a option to disable autovacuum cancellation on lock conflict?

On 12/2/14, 2:22 PM, Jeff Janes wrote:
> Or maybe I overestimate how hard it would be to make vacuum restartable.  You would have to save a massive amount of
state(upto maintenance_work_mem tid list, the block you left off on both the table and all of the indexes in that
table),and you would somehow have to validate that saved state against any changes that might have occurred to the
tableor the indexes while it was saved and you were not holding the lock, which seems like it would almost as full of
cornercases as weakening the lock in the first place.  Aren't they logically the same thing?  If we could drop the lock
andtake it up again later, maybe the answer is not to save the state, but just to pause the vacuum until the lock
becomesfree again, in effect saving the state in situ.  That would allow autovac worker to be held hostage to anyone
takinga lock, though.
 

Yeah, rather than messing with any of that, I think it would make a lot more sense to split vacuum into smaller
operationsthat don't require such a huge chunk of time.
 

> The only easy way to do it that I see is to have it only stop at the end of a index-cleaning cycle, which probably
takestoo long to block for.  Or record a restart point at the end of each index-cleaning cycle, and then when it yields
thelock it abandons all work since the last cycle end, rather than since the beginning.  That would be better than what
wehave, but seems like a far cry from actual restarting from any point.
 

Now that's not a bad idea. This would basically mean just saving a block number in pg_class after every intermediate
indexclean and then setting that back to zero when we're done with that relation, right?
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com