Waiting for 9.5 – Support ALTER SYSTEM RESET command.

On 2nd of September, Fujii Masao committed patch:

Support ALTER SYSTEM RESET command.
 
This patch allows us to execute ALTER SYSTEM RESET command to
remove the configuration entry from postgresql.auto.conf.
 
Vik Fearing, reviewed by Amit Kapila and me.

Only this year we got “ALTER SYSTEM" command, that lets you change config values for postgresql, from within postgresql, without touching config files.

It worked, and was great (or will be, depending on whether you played with, so far unreleased, 9.4 version), but now we got important addition – ability to reset value to default from normal config.

Let's see how it works. In my PostgreSQL, I have log_min_duration_statement set to 0 (it's test machine, so it can log everything):

=$ grep log_min_duration_statement $PGDATA/*.conf
/home/pgdba/data/postgresql.conf:log_min_duration_statement = 0

When I connect with psql, I can see that it's 0, and I can change system config:

$ SHOW log_min_duration_statement ;
 log_min_duration_statement
----------------------------
 0
(1 ROW)
 
$ ALTER system SET log_min_duration_statement = 100;
ALTER SYSTEM
 
$ SHOW log_min_duration_statement ;
 log_min_duration_statement
----------------------------
 0
(1 ROW)
 
$ SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 ROW)
 
$ SHOW log_min_duration_statement ;
 log_min_duration_statement
----------------------------
 100ms
(1 ROW)

After such change, my grep on config files shows:

=$ grep log_min_duration_statement $PGDATA/*.conf
/home/pgdba/data/postgresql.auto.conf:log_min_duration_statement = '100'
/home/pgdba/data/postgresql.conf:log_min_duration_statement = 0

Before we got “RESET", the only way to revert to default, would be to find out what was set in postgresql.conf, and set the log_min_duration_statement to the same value, or remove line from postgresql.auto.conf.

Now, we can:

$ ALTER system reset log_min_duration_statement;
ALTER SYSTEM
 
$ SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 ROW)
 
$ SHOW log_min_duration_statement ;
 log_min_duration_statement
----------------------------
 0
(1 ROW)

And now, postgresql.auto.conf lost the line:

=$ grep log_min_duration_statement $PGDATA/*.conf
/home/pgdba/data/postgresql.conf:log_min_duration_statement = 0

I really, really like it. Can't wait to get 9.5 on my production servers, but it will take some time. Thanks, Vik.

2 thoughts on “Waiting for 9.5 – Support ALTER SYSTEM RESET command.”

  1. If it doesn’t get backpatched, you can do:

    ALTER SYSTEM SET log_min_duration_statement TO DEFAULT;

Comments are closed.