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 table or the indexes while it was saved and you were not holding the lock, which seems like it would almost as full of corner cases as weakening the lock in the first place. Aren't they logically the same thing? If we could drop the lock and take it up again later, maybe the answer is not to save the state, but just to pause the vacuum until the lock becomes free again, in effect saving the state in situ. That would allow autovac worker to be held hostage to anyone taking a lock, though.

Yeah, rather than messing with any of that, I think it would make a lot more sense to split vacuum into smaller operations that 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 takes too long to block for. Or record a restart point at the end of each index-cleaning cycle, and then when it yields the lock it abandons all work since the last cycle end, rather than since the beginning. That would be better than what we have, 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 index clean 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