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

<p dir="ltr"><br /> On Nov 29, 2014 9:23 AM, "Andres Freund" <<a
href="mailto:andres@2ndquadrant.com">andres@2ndquadrant.com</a>>wrote:<br /> ><br /> > Hi,<br /> ><br />
>I've more than once seen that autovacuums on certain tables never<br /> > succeed because regular exclusive (or
similar)lockers cause it to give<br /> > way/up before finishing.  Usually if some part of the application uses<br
/>> explicit exclusive locks.<br /> ><br /> > In general I think it's quite imortant that autovacuum bheaves
that<br/> > way. But I think it might be worhtwile to offer an option to disable<br /> > that behaviour. If some
pieceof application logic requires exclusive<br /> > locks and that leads to complete starvation of autovacuum,
there's<br/> > really nothing that can be done but to manually schedule vacuums right<br /> > now.<br /> ><br
/>> I can see two possible solutions:<br /> ><br /> > 1) Add a reloption that allows to configure whether
autovacuumgives way<br /> >    to locks acquired by user backends.<br /> > 2) Add a second set of
autovacuum_*_scale_factorvariables that governs<br /> >    a threshhold after which autovacuum doesn't get cancelled
anymore.<br/> ><br /> > Opinions?<p dir="ltr">I definitely think having such a tunable would be very useful, in
edgecases (so as you say the default should definitely be what it is today). <p dir="ltr">Another "trigger option"
couldbe to say "you may terminate autovaccum this many times before forcing one through", rather than triggers on tuple
count.But tuples is probably a better choice, as it gives more dynamics - unless we want to do both.<p
dir="ltr">/Magnus<br />