Re: pg_terminate_backend can terminate background workers and autovacuum launchers

On Fri, 23 Jun 2017 19:43:35 -0400
Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Alvaro, all,
>
> * Alvaro Herrera (alvherre(at)2ndquadrant(dot)com) wrote:
> > Yugo Nagata wrote:
> >
> > > I tried to make it. Patch attached.
> > >
> > > It is easy and simple. Although I haven't tried for autovacuum worker,
> > > I confirmed I can change other process' parameters without affecting others.
> > > Do you want this in PG?
> >
> > One advantage of this gadget is that you can signal backends that you
> > own without being superuser, so +1 for the general idea. (Please do
> > create a fresh thread so that this can be appropriately linked to in
> > commitfest app, though.)
>
> Well, that wouldn't quite work with the proposed patch because the
> proposed patch REVOKE's execute from public...

Yes. It is intentional to revoke execute from public because we need
to change configuration before signaling other backend and it needs
superuser privilege.

> I'm trying to figure out how it's actually useful to be able to signal a
> backend that you own about a config change that you *aren't* able to
> make without being a superuser.. Now, if you could tell the other
> backend to use a new value for some USERSET GUC, then *that* would be
> useful and interesting.
>
> I haven't got any particularly great ideas for how to make that happen
> though.
>
> > You need a much bigger patch for the autovacuum worker. The use case I
> > had in mind is that you have a maintenance window and can run fast
> > vacuuming during it, but if the table is large and vacuum can't finish
> > within that window then you want vacuum to slow down, without stopping
> > it completely. But implementing this requires juggling the
> > stdVacuumCostDelay/Limit values during the reload, which are currently
> > read at start of vacuuming only; the working values are overwritten from
> > those during a rebalance.
>
> Being able to change those values during a vacuuming run would certainly
> be useful, I've had cases where I would have liked to have been able to
> do just exactly that. That's largely independent of this though.
>
> Thanks!
>
> Stephen

--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>