Waiting for 9.5 – Add pg_settings.pending_restart column

On 15th of May, Peter Eisentraut committed patch:

Add pg_settings.pending_restart column
 
with input from David G. Johnston, Robert Haas, Michael Paquier

This is, for me, great. It might not sound like much, but it is actually something that I'm very excited about.

Basically – let's assume you change system settings. Either by modifying postgresql.conf, or by doing ALTER SYSTEM.

Some of the settings will work after SELECT pg_reload_conf() (or pg_ctl reload), but some require restart.

When dealing with servers, it is relatively common (for me) to request change of multiple parameters. And then it gets tedious – checking which parameters require reload, and which restart.

Now, we have it solved.

In normal state, the pending_restart column is always false:

$ SELECT pending_restart, COUNT(*) FROM pg_settings GROUP BY 1;
 pending_restart | COUNT
-----------------+-------
 f               |   248
(1 ROW)

Changing something that requires just reload, doesn't change it:

$ ALTER system SET log_min_duration_statement = 10;
ALTER SYSTEM
 
$ SELECT pending_restart, COUNT(*) FROM pg_settings GROUP BY 1;
 pending_restart | COUNT
-----------------+-------
 f               |   248
(1 ROW)

But if I'd change something more “critical":

$ ALTER system SET wal_log_hints = TRUE;
ALTER SYSTEM
 
$ SELECT pending_restart, COUNT(*) FROM pg_settings GROUP BY 1;
 pending_restart | COUNT 
-----------------+-------
 f               |   248
(1 ROW)
 
$ SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 ROW)
 
$ SELECT pending_restart, COUNT(*) FROM pg_settings GROUP BY 1;
 pending_restart | COUNT 
-----------------+-------
 f               |   247
 t               |     1
(2 ROWS)
 
$ SELECT * FROM pg_settings WHERE pending_restart;
-[ RECORD 1 ]---+--------------------------------------------------------------------------------------------------------
name            | wal_log_hints
setting         | off
unit            | [NULL]
category        | Write-Ahead Log / Settings
short_desc      | Writes FULL pages TO WAL WHEN FIRST modified after a checkpoint, even FOR a non-critical modifications.
extra_desc      | [NULL]
context         | postmaster
vartype         | bool
SOURCE          | DEFAULT
min_val         | [NULL]
max_val         | [NULL]
enumvals        | [NULL]
boot_val        | off
reset_val       | off
sourcefile      | [NULL]
sourceline      | [NULL]
pending_restart | t

Great. Not only I know that I have to restart, but I know exactly why. It probably would be a bit better if I saw “pending_restart" set before pg_reload_conf(), but still, it's just a minor thing. Thanks guys.