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

On Wednesday, November 14, 2012 12:25 AM Robert Haas wrote:
> On Mon, Nov 12, 2012 at 10:59 PM, Amit kapila <amit(dot)kapila(at)huawei(dot)com>
> wrote:
> > Is the above opinion about only locking or even on a way to write the
> changed things in a file as mentioned in point-1 in mail chain upthread.
> > (Point-1: > 1. While writing .auto file, it will always assume that
> .auto file contain
> >> all config parameters.
> >> Now as this .auto file is of fixed format and fixed record size, it
> can
> >> directly write a given record to its particular position.)
> > What my thinking was that if we can decide that the format and size of
> each configuration is fixed, it can be directly written without doing
> anything for it in memory.
>
> Uh, no, I don't think that's a good idea. IMHO, what we should do is:
>
> 1. Read postgresql.conf.auto and remember all the settings we saw. If
> we see something funky like an include directive, barf.
> 2. Forget the value we remembered for the particular setting being
> changed. Instead, remember the user-supplied new value for that
> parameter.
> 3. Write a new postgresql.conf.auto based on the information
> remembered in steps 1 and 2.

I am okay with implementing the above way because as per my understanding
this is almost very similar to what I have mentioned in my initial proposal
(Point-5 in Algorithm of Alter System Set ...).
http://archives.postgresql.org/pgsql-hackers/2012-10/msg01509.php

However as now Greg suggested to explore GUC concept as well, so I would
like to check and see the feasibility by that method.

The only reason I have mentioned about fixed format and fixed record size
concept is that during previous discussions for writing the file with GUC,
it came up that is it possible to write file without reading it in current
session.
(-- It seems to me that we ought to be able to rewrite a machine-generated
configuration file without loading those values into the current session.)
Now on second thought it seems to me may be you want to say by above comment
was without loading into session specific GUC.

> Of course, if we go with one-file-per-setting, then this becomes even
> simpler: just clobber the file for the single setting being updated -
> creating it if it exists - and ignore all the rest. I don't
> personally favor that approach because I think I think it's clunky to
> manage, but YMMV.

> With either approach, it's worth noting that a RESET variant of this
> could be useful - which would either remove the chosen setting from
> postgresql.conf.auto, or remove the file containing the
> automatically-set value for that setting. I think my personal
> favorite syntax is:
>
> ALTER SYSTEM .. SET wunk = 'thunk';
> ALTER SYSTEM .. RESET wunk;
>
> But I'm OK with something else if there's consensus. I don't
> particularly like SET PERSISTENT because I think this is more like
> ALTER DATABASE .. SET than it is like SET LOCAL, but IJWH.

I think for this there are multiple ways, one is Alter System .., other is
provide this through built-in function.
For first version may be I will go with built-in function Approach, then if
there is consensus to give it through
Alter System, we can change it.
One advantage, I am seeing in your above suggestion is that a method to
provide RESET will be better with ALTER SYSTEM rather than built-in
function. For the same to achieve through built-in, I think one way to
provide is to give a separate function.

With Regards,
Amit Kapila.