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

Based on feedback in this mail chain, please find the modified method to have this feature:

Syntax for Command:

1. Have SQL command to change the configuration parameter values:
ALTER SYSTEM SET configuration_parameter {TO | =} {value, | 'value'} COMMENT 'value';
2. Have built-in to change the configuration parameter values:
pg_set_config(config_param,value)/pg_change_config(config_param,value)

If there is no objection, I would like to go-ahead with the 2nd approach (built-in) as per suggestion by Magnus.

Implementation approach

1. Add a configuration subdirectory (config.d) to the default installation. This will contain default .auto file.
Default .auto file will be empty. However we can modify it to contain all uncommented parameters of postgresql.conf if required.

2. Have the standard postgresql.conf end by including that directory.
Here user can have at end, begin or in-between, I think it will get handled.
By default we can keep at end which means the parameters in .auto file will be given more priority.

3. SQL parameter changes collect up all other active parameter changes, rewrite that file, and signal the server.
If any change requested requires a full server restart. warn the user of that fact.

I am not able to fully understand the writing of .auto file as suggested in this mail chain. What I understood from the above is that,
when user executes this function, it should collect all changed parameters and rewrite the .auto file. But according to
my understanding it can write incorrect values in .auto file as backend from which this command is getting executed
might have some old values.
The key point is how backend can get the latest config values without reading .auto file or by communicating with other backends?

4. Warning on sighup, to indicate that either the file isn't included, or something else "later in the chain" overwrote it.

5. Unite recovery.conf with postgresql.conf
I think if we use include dir concept for current feature implementation, it can address the basic design level concern for both the features.

Suggestions/Comments?

With Regards,
Amit Kapila.

On Saturday, November 03, 2012 7:09 AM Josh Berkus wrote:
>
> > -Add a configuration subdirectory to the default installation. Needs
> to
> > follow the config file location, so things like the Debian relocation
> of
> > postgresql.conf still work. Maybe it has zero files; maybe it has one
> > that's named for this purpose, which defaults to the usual:
> >
> > # Don't edit this file by hand! It's overwritten by...
> >
> > -Have the standard postgresql.conf end by including that directory
> > -SQL parameter changes collect up all other active parameter changes,
> > rewrite that file, and signal the server. If any change requested
> > requires a full server restart. warn the user of that fact.
>
> +1
>
> Simple, easy to understand, easy to customize.
>
> > The only obvious bad case I can think of here is if someone has left
> the
> > directory there, but deleted the include_dir statement; then the file
> > would be written successfully but never included. Seems like in the
> > worst case the postgresql.conf parser would just need to flag whether
> it
> > found the default directory included or not, to try and flag avoid
> > obvious foot shooting.
>
> Yes, and we can have the comment:
>
> # this includes the default directory for extra configuration files
> # do not delete or comment this out; remove any extra configuration
> # files you don't want instead
>
> ... or similar to warn users. Frankly, if someone removes the
> "includedir config/" line, we can presume they know what they are doing.
>
> For that matter, some users might want to move the line to the beginning
> of the file, instead of the end.
>
> > Some of the better received ideas I floated for merging the
> > recovery.conf file seemed headed this way too. That also all blocked
> > behind the include directory bit being surprisingly tricky to get
> > committed. So that's possible to revive usefully now. And as much as
> I
> > hate to expand scope by saying it, both changes should probably be
> > tackled at once. It's important to make sure they're both solved well
> > by whatever is adopted, they are going to co-exist as committed one
> day.
>
> Yes.
>
> I'll also point out that includedir would help solve the issue of
> "postgresql.conf is under Puppet, but I want to change the logging
> options ..." more handily than current solutions.
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>