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

> From: Cédric Villemain [mailto:cedric(at)2ndquadrant(dot)com]
> Sent: Friday, November 16, 2012 1:55 PM
> To: pgsql-hackers(at)postgresql(dot)org
> Cc: Amit Kapila; 'Robert Haas'; 'Greg Smith'; 'Josh Berkus'; 'Tom Lane';
> 'Magnus Hagander'; 'Christopher Browne'
> Subject: Re: [HACKERS] Proposal for Allow postgresql.conf values to be
> changed via SQL
On Friday, November 16, 2012 1:55 PM Cédric Villemain wrote:
> Le vendredi 16 novembre 2012 07:16:09, Amit Kapila a écrit :
> > 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.
>
> It looks like a bit similar to what proposed Peter in another thread.
> If you use a table to store the values, the action of writing the file
> is just flush a table to disk, it might be a deferred trigger for
> example.
> This table can be inserted/updated/deleted in a « BEGIN TRANSACTION
> ISOLATION SERIALIZABLE » transaction so there is no issue on who touch
> what and when.
> Either it is commited without serialization error or it is not.
> (and we can elaborate with the table content being deleted at commit
> time, or not, etc.).
>
> I suppose it can be an extension also.
>
> >
> > > * 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?
>
> not yet.
> ...thinking some more...
> Maybe add a column to define where to write the updated GUC (before
> everything else, after everything else, instead of the current content).
> The trigger responsible to write that will do.

Currently, it will write all the configuration parameters to be changed by
SQL commands in separate PostgreSQL.auto.conf file and that will get
included at end of postgresql.conf. So I think wherever we write it in .auto
file as it is included at end, always parameters of .auto file will be
loaded later.

With Regards,
Amit Kapila.