Re: pg_terminate_backend can terminate background workers and autovacuum launchers

On Thu, 22 Jun 2017 14:08:30 +0900
Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:

> On Thu, Jun 22, 2017 at 1:52 PM, Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:
> > On Thu, 22 Jun 2017 12:05:19 +0900
> > Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:
> >> 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;
>
> No need to reset the parameter afterwards as this makes it sensible to
> updates afterwards, but you have the idea. Note that this is rather
> recent, as autovacuum listens to SIGHUP only since a75fb9b3.

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?

[session A (PID:18375)]
=# show autovacuum_vacuum_cost_delay;
autovacuum_vacuum_cost_delay
------------------------------
20ms
(1 row)

[session B]
postgres=# alter system set autovacuum_vacuum_cost_delay to 10;
ALTER SYSTEM
postgres=# select pg_reload_conf(18375);
pg_reload_conf
----------------
t
(1 row)

postgres=# show autovacuum_vacuum_cost_delay;
autovacuum_vacuum_cost_delay
------------------------------
20ms
(1 row)

[session A again]
postgres=# show autovacuum_vacuum_cost_delay;
autovacuum_vacuum_cost_delay
------------------------------
10ms
(1 row)

> --
> Michael

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