On Thu, 22 Jun 2017 14:08:30 +0900 Michael Paquier <michael.paqu...@gmail.com> wrote:
> On Thu, Jun 22, 2017 at 1:52 PM, Yugo Nagata <nag...@sraoss.co.jp> wrote: > > On Thu, 22 Jun 2017 12:05:19 +0900 > > Michael Paquier <michael.paqu...@gmail.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 <nag...@sraoss.co.jp>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 0fdad0c..d79faf7 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1128,6 +1128,7 @@ REVOKE EXECUTE ON FUNCTION pg_wal_replay_pause() FROM public; REVOKE EXECUTE ON FUNCTION pg_wal_replay_resume() FROM public; REVOKE EXECUTE ON FUNCTION pg_rotate_logfile() FROM public; REVOKE EXECUTE ON FUNCTION pg_reload_conf() FROM public; +REVOKE EXECUTE ON FUNCTION pg_reload_conf(int) FROM public; REVOKE EXECUTE ON FUNCTION pg_current_logfile() FROM public; REVOKE EXECUTE ON FUNCTION pg_current_logfile(text) FROM public; diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c index 62341b8..cc173ba 100644 --- a/src/backend/utils/adt/misc.c +++ b/src/backend/utils/adt/misc.c @@ -339,6 +339,36 @@ pg_reload_conf(PG_FUNCTION_ARGS) PG_RETURN_BOOL(true); } +/* + * Signal to reload the database configuration for a specified backend + * + * Permission checking for this function is managed through the normal + * GRANT system. + */ +Datum +pg_reload_conf_pid(PG_FUNCTION_ARGS) +{ + int pid = PG_GETARG_INT32(0); + int r = pg_signal_backend(pid, SIGHUP); + + if (r == SIGNAL_BACKEND_NOSUPERUSER) + ereport(WARNING, + (errmsg("must be a superuser to terminate superuser process"))); + + if (r == SIGNAL_BACKEND_NOPERMISSION) + ereport(WARNING, + (errmsg("must be a member of the role whose process is being terminated or member of pg_signal_backend"))); + + if (r) + { + ereport(WARNING, + (errmsg("failed to send signal to backend: %d", pid))); + PG_RETURN_BOOL(false); + } + + PG_RETURN_BOOL(true); +} + /* * Rotate log file diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 1191b4a..7258f15 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -3255,6 +3255,8 @@ DESCR("true if wal replay is paused"); DATA(insert OID = 2621 ( pg_reload_conf PGNSP PGUID 12 1 0 0 0 f f f f t f v s 0 0 16 "" _null_ _null_ _null_ _null_ _null_ pg_reload_conf _null_ _null_ _null_ )); DESCR("reload configuration files"); +DATA(insert OID = 3409 ( pg_reload_conf PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 16 "23" _null_ _null_ _null_ _null_ _null_ pg_reload_conf_pid _null_ _null_ _null_ )); +DESCR("reload configuration files"); DATA(insert OID = 2622 ( pg_rotate_logfile PGNSP PGUID 12 1 0 0 0 f f f f t f v s 0 0 16 "" _null_ _null_ _null_ _null_ _null_ pg_rotate_logfile _null_ _null_ _null_ )); DESCR("rotate log file"); DATA(insert OID = 3800 ( pg_current_logfile PGNSP PGUID 12 1 0 0 0 f f f f f f v s 0 0 25 "" _null_ _null_ _null_ _null_ _null_ pg_current_logfile _null_ _null_ _null_ ));
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers