Re: pg_terminate_backend can terminate background workers and autovacuum launchers

On Thu, 22 Jun 2017 12:05:19 +0900
Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:

> On Thu, Jun 22, 2017 at 11:52 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > On 2017-06-22 11:49:47 +0900, Yugo Nagata wrote:
> >> I agree that we can kill theses processes by the OS command. However,
> >> It seems to me that pg_{cancel,terminate}_backend don't need to be able to
> >> kill processes except for client backends because we can do same thing by
> >> the OS command if necessary, and acutually these functions cannot kill
> >> most other processes, for example, background writer. Are the autovacuum
> >> launcher and background worker special for these functions?
> >
> > I strongly disagree with this - I think it's quite useful to be able to
> > kill things via SQL that can hold lock on database objects. I'm not
> > seeing which problem would be solved by prohibiting this?
>
> +1. Controlling them as of now is useful, particularly now that all
> processes show wait events, even auxiliary ones (though not

I got it. I agree that the current behaviour and it isn't worth changint it.
In my understand, processes that the functions can kill (client backends,
background workers, autovacuum processes) are ones that can hold lock
on database objects.

> signal-able). Different thought, but I'd love to see a SQL function
> that allows triggering SIGHUP on a specific process, like an
> autovacuum worker to change its cost parameters. There is
> pg_reload_conf() to do so but that's system-wide.

For example, is that like this?

=# alter system set autovacuum_vacuum_cost_delay to 10;
=# select pg_reload_conf(<PID of autovacuum worker)>);
=# alter system reset autovacuum_vacuum_cost_delay;

> --
> Michael

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