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

On Thursday, November 15, 2012 11:28 PM Cédric Villemain wrote:
> Le jeudi 15 novembre 2012 15:48:14, Amit kapila a écrit :
> > On Wednesday, November 14, 2012 12:24 AM Robert Haas wrote:
> >
> > On Mon, Nov 12, 2012 at 10:59 PM, Amit kapila <amit(dot)kapila(at)huawei(dot)com>
> wrote:
> > > 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.
> >
> > Attached patch contains implementaion for above concept.
> > It can be changed to adapt the write file based on GUC variables as
> > described by me yesterday or in some other better way.
> >
> > Currenty to remember and forget, I have used below concept:
> > 1. Read postgresql.auto.conf in memory.
> > 2. parse the GUC_file for exact loction of changed variable 3. update
> > the changed variable in memory at location found in step-2 4. Write
> > the postgresql.auto.conf
> >
> > Overall changes:
> > 1. include dir in postgresql.conf at time of initdb 2. new built-in
> > function pg_change_conf to change configuration settings 3. write file
> > as per logic described above.
> >
> > Some more things left are:
> > 1. Transactional capability to command, so that rename of .lock file
> > to .auto.conf can be done at commit time.
> >
> > I am planing to upload the attached for this CF.
> >
> > Suggestions/Comments?
>
> Yes, sorry to jump here so late.
> * Why don't we use pg_settings ? (i.e. why not materialize the view and
> use it, it should be easier to have something transactional and also
> serializable with probably a DEFERRABLE select pg_reload_config() which
> mv the configuration file at commit time)

I think some consistency issues can come, because before editing and
flushing, each backend has to have latest copy
else it might override some parameters values.
Can you explain the whole idea in detail, may be it will be easier to verify
if it has any problem.

> * Can I define automatic parameters to be loaded before and/or after the
> non- automatic parameters in a convenient way (without editing files at
> all)?

In the current implementation, there is no way. Do you have any suggestion?

With Regards,
Amit Kapila.