Re: Unsafe GUCs and ALTER SYSTEM WAS: Re: ALTER SYSTEM SET

Stephen, all:

(forked from main ALTER SYSTEM discussion. this thread is meant to
discuss only this question:

E) whether "unsafe" settings or "restart" settings should be allowed in
ALTER SYSTEM SET.)

On 08/02/2013 01:48 PM, Stephen Frost wrote:
> Reflecting on this a bit more, I'm curious what your list-of-15 is.

Based on serving literally hundreds of clients, the below are the
settings we change on client servers 50% or more of the time. Other
settings I touch maybe 10% of the time. THese are also, in general, the
settings which I modify when we create Puppet/Chef/Salt scripts for clients.

listen_addresses*@
shared_buffers*@
work_mem
maintenance_work_mem
effective_cache_size
synchronous_commit (because of amazon)
wal_buffers*@
checkpoint_segments*@
checkpoint_completion_target* (because of ext3)
autovacuum* (turn off for data warehouses,
turn back on for very mistaken users)
stats_file_directory*@

replication/archiving settings as a set*@
wal_level, max_wal_senders, wal_keep_segments, hot_standby, archive_mode
and archive_command

logging settings as a set
logging_collector*
everything else

* = requires a cold start to change
@ potentially can cause failure to restart

Note that two of the settings, shared_buffers and wal_buffers, become
much less of an issue for restarting the system in 9.3. Also, it's
possible that Heikki's automated WAL log management might deal with
out-of-disk-space better than currently, which makes that less of a risk.

However, you'll see that among the 11 core settings, 7 require a full
restart, and 5 could "potentially cause failure to restart". That means
that from my perspective, ALTER SYSTEM SET is at least 45% useless if it
can't touch unsafe settngs, and 63% useless if it can't touch any
setting which requires a restart. Adding the replication settings into
things makes stuff significantly worse that way, although ALTER SYSTEM
SET would be very useful for logging options provided that
logging_collector was turned on.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com