On 2014-12-02 11:23:31 -0800, Jeff Janes wrote: > I think it would be more promising to work on downgrading lock strengths so > that fewer things conflict, and it would be not much more work than what > you propose.
I think you *massively* underestimate the effort required to to lower lock levels. There's some very ugly corners you have to think about to do so. Just look at how long it took to implement the lock level reductions for ALTER TABLE - and those were the simpler cases.
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.
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.
> What operations are people doing on a regular basis that take locks > which cancel vacuum? create index?
Locking tables against modifications in this case.
So in "share mode", then? I don't think there is any reason that there can't be a lock mode that conflicts with "ROW EXCLUSIVE" but not "SHARE UPDATE EXCLUSIVE". Basically something that conflicts with logical changes, but not with physical changes.
