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

On Tue, Dec 2, 2014 at 11:41 AM, Andres Freund <andres(at)2ndquadrant(dot)com>
wrote:

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

Cheers,

Jeff