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

On 11/29/14, 2:22 AM, Andres Freund wrote:
> Hi,
>
> I've more than once seen that autovacuums on certain tables never
> succeed because regular exclusive (or similar) lockers cause it to give
> way/up before finishing. Usually if some part of the application uses
> explicit exclusive locks.
>
> In general I think it's quite imortant that autovacuum bheaves that
> way. But I think it might be worhtwile to offer an option to disable
> that behaviour. If some piece of application logic requires exclusive
> locks and that leads to complete starvation of autovacuum, there's
> really nothing that can be done but to manually schedule vacuums right
> now.
>
> I can see two possible solutions:
>
> 1) Add a reloption that allows to configure whether autovacuum gives way
> to locks acquired by user backends.
> 2) Add a second set of autovacuum_*_scale_factor variables that governs
> a threshhold after which autovacuum doesn't get cancelled anymore.
>
> Opinions?

What do you mean by "never succeed"? Is it skipping a large number of pages? Might re-trying the locks within the same vacuum help, or are the user locks too persistent?
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com