Re: Proposal for Allow postgresql.conf values to be changed via SQL

On Wed, Nov 7, 2012 at 5:19 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
> On Tuesday, November 06, 2012 11:30 PM Robert Haas wrote:
>> On Wed, Oct 31, 2012 at 8:17 AM, Magnus Hagander <magnus(at)hagander(dot)net>
>> wrote:
>> >> I'm not convinced we ever *had* a consensus on this. There were
>> >> proposals, but I'm not sure a majority ever bought into any one of
>> 'em.
>> >
>> > I thought there was a consensus. But given that the one I thought we
>> > had consensus on was different, I'm not sure we can correctly call it
>> > consensus.
>> >
>> > What we discussed at that time was to have a *function* that changes
>> > the permanent configuration, and not actually extend the syntax of the
>> > system. As a starting point.
>> >
>> > The idea at the time was to use the include *directory* functionality,
>> > for say a "config.d" directory in pgdata. The builtin one would then
>> > use a predictable filename in this directory, so that the DBA who
>> > prefers it can drop files both before and after that file into the
>> > directory.
>>
>> Reading over this thread, it seems that there are at least three
>> different proposals for how this should work in detail:
>>
>> 1. Have a configuration file that can be rewritten using SQL, and have
>> postgresql.conf include it by default.
>> 2. Have a configuration directory that gets included in
>> postgresql.conf by default, and one file in that directory will
>> contain all the parameters set via SQL.
>> 3. Have a configuration directory that gets included in
>> postgresql.conf by default, with one file per parameter, and rewrite
>> just that file when the corresponding parameter is set via SQL.
>>
>> Also, there are at least three different proposals for what the syntax
>> should look like:
>>
>> 1. ALTER SYSTEM
>> 2. SET PERSISENT
>> 3. pg_frob_my_configuration()
>
> This is very good summarization of all discussion in this mail chain.
> However there is one more point which I am not able to clearly make out is
> how to write into file that contains
> all configuration parameters changed by SQL.
> What I could understand from Greg and Josh's mail is that they are
> suggesting to write a file by collecting active changed parameters from
> memory or use pg_settings.
> But as mentioned in other mail as per my understanding that this can lead to
> have incorrect values in .auto file.
> I think I am missing or not able to understand how can it be done without
> reading .auto file or by communicating with other backends?

Perhaps you can look at pg_settings, to see if the current setting is
from the .auto file. If it is, then that's where it came from and it
should be written back there. If it's something else, that's not where
it came from.

That will remove it from the .auto file if someone manually adds an
override later, but I'm not sure we need to support people who do the
same config in two different ways - as long as we document how this
happens.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/