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

Le vendredi 16 novembre 2012 15:08:30, Amit Kapila a écrit :
> On Thursday, November 15, 2012 8:18 PM Amit kapila wrote:
> > 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.
>
> About transaction capability, I think it will be difficult to implement it
> in transaction block,
> because during Rollback to savepoint it is difficult to rollback (delete
> the file), as there is no track of changes w.r.t
> Savepoint.

not a problem.
consider that pseudo code:

begin serializable;

update pg_settings; -- or whatever the name of the object (can include
creation of a table, etc...)

savepoint...

update pg_settings;

rollback to savepoint;

commit; <-- here the deferred trigger FOR STATEMENT on pg_settings is fired
and is responsible to write/mv the/a file.

Is there something obvious I'm not seeing ?

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation