Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

On Fri, Aug 30, 2013 at 9:49 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>> there's a fairly generous but fixed-at-startup-time limit on how many
>> segments you can have. In practice I don't think this matters much,
>> but it was a sobering reminder that the main shared memory segment,
>> with all of its inflexibility, has important reliability properties
>> that are hard to replicate in more dynamic scenarios.
>
> Why wouldn't it be possible to have the same arrangment for
> shared_buffers, where you have more entires than you 'need' at startup
> but which then allows you to add more shared segments later? I do see
> that we would need an additional bit of indirection to handle that,
> which might be too much overhead, but the concept seems possible. Isn't
> that more-or-less how the kernel handles dynamic memory..?

Yeah, I think that something like would be possible, but the
synchronization would be tricky, and it wouldn't work at all with
System V shared memory or Windows shared memory, which apparently
can't be resized after creation. Also, it would rely on the system
administrator having a sensible setting for
max_on_the_fly_shared_buffers and only having the wrong setting for
initial_shared_buffers_until_i_change_it_later, which might not cover
a very high percentage of the cases that arise in practice.

>> Under the currently-proposed design, it can't be used to do any such
>> thing. It can only be used to modify some auto.conf file which lives
>> in $PGDATA. It's therefore no different from the ops perspective than
>> ALTER DATABASE or ALTER USER - except that it allows all settings to
>> be changed rather than only a subset.
>
> Claiming that modifying a file *included from a file in /etc* doesn't
> modify things under /etc is disingenuous, imv.

I think you're getting way too hung up on the fact that the proposed
auto.conf will be stored as a flat file. From your comments upthread,
I gather that you'd be rejoicing if it were a table. The only reason
we're not doing that is because of the possibility that something in
auto.conf might keep the server from starting. I don't think that's
gonna be very common now that we're mostly out from under the System V
shared memory limits, but we'll see. As you point out, it might also
be worth fixing: maybe we can find some way to arrange things so that
the server will always be able to start up regardless of how badly
messed-up auto.conf is... but that's a different patch.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company