Re: admin control over cancelling autovacuum when blocked by a lock

On Tue, 30 Apr 2019 at 02:44, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> John Lumby <johnlumby(at)hotmail(dot)com> writes:
> > On 04/29/2019 07:15 PM, Simon Riggs wrote:
> >> Why would you want this?
>
> > Because it greatly reduces rate of growth of certain indexes in some
> > workloads
> > (based on test in which I modified the code to change the cancel to a
> > plain WARNING)
>
> [ raised eyebrow... ] Shouldn't do so, I think. What exactly are you
> doing that's causing the cancel?
>
> > I *think* when an autovacuum worker is cancelled, all or most of the
> work
> > it did on its last table is undone -- true?
>

...

> If you're losing index cleanup
> work, it suggests that you're repeatedly executing DDL that requires
> exclusive lock on the table.

Exactly the point of my question.

But further than this, if we changed things as requested the DDL being
executed would be forced to wait behind the autovacuum, frustrating the
timely execution of the DDL.

Hence: Why would you want this?

> Maybe you could avoid doing that?
>

Seems like the only way out of this dilemma.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services