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

On Wednesday, October 31, 2012 5:47 PM Magnus Hagander wrote:
On Tue, Oct 30, 2012 at 11:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>>>> I should think that doing this requires heading back towards there
>>>> being a single unique configuration stream, and over the course of
>>>> several versions, deprecating the INCLUDE directive.
>
>>> Oh, maybe I should take a closer look at Amit's proposal then. I
>>> thought we planned to make use of the INCLUDE facility for SET
>>> PERSISTENT, including supporting include-if-exists. Possibly what he's
>>> proposing and what I thought our last consensus were are highly divergent.
>
> >I'm not convinced we ever *had* a consensus on this. There were
> >proposals, but I'm not sure a majority ever bought into any one of 'em.

> I thought there was a consensus. But given that the one I thought we
> had consensus on was different, I'm not sure we can correctly call it
> consensus.

> What we discussed at that time was to have a *function* that changes
> the permanent configuration, and not actually extend the syntax of the
> system. As a starting point.

Do you mean a function like pg_set_config(config_param,value)/pg_change_config(config_param,value)/pg_configure(config_param,value) to change the configuration values in file?

So till now below options are discussed which can be used to provide this functionality:

1. Set PERSISTENT --This has advantage that user can have one syntax (SET) to change values at different levels. But not sure if it is good incase COMMENTS also needs to be included.
2. ALTER SYSTEM SET configuration_parameter {TO | =} {value, | 'value'} COMMENT 'value';
This syntax is very much similar to what Oracle provides.
3. pg_set_config(config_param,value)/pg_change_config(config_param,value)
This is somewhat similar to SQL Server. Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE.
To change settings that affect only the current user session, use the SET statement.
http://msdn.microsoft.com/en-us/library/ms188787(v=sql.90).aspx
4. Any other better ideas for Syntax?

Please provide your suggestions which one is better?

> The idea at the time was to use the include *directory* functionality,
> for say a "config.d" directory in pgdata. The builtin one would then
> use a predictable filename in this directory, so that the DBA who
> prefers it can drop files both before and after that file into the
> directory.

Can you please explain in more detail how using this idea the whole implementation can be realized.
Do you see problems or improvements required in the design/implementation described in proposal mail?

>> The whole problem of intermixing manual editing and programmatic editing
>> is just a big can of worms, and not everybody is prepared to give up the
> former to have the latter.
>
>> You can, if you are so inclined, implement something functionally
>> equivalent to Amit's proposal today using contrib/adminpack's
>> pg_file_write --- okay, it's much less convenient than a built-in
>> implementation would be, but you can drop some variable assignments into
>> a file and then put a suitable INCLUDE into the otherwise-static main
>> config file. The fact that this isn't being done by a large number of
>> people (is anybody at all actually doing it?) suggests to me that maybe
>> the demand isn't all that great.

> The demand for running something like thta manually isn't all that
> great, I believe. This is why I think using a function for it is
> perfectly OK, and we don't necessarily need ALTER SYSTEM or something
> like that. (In fact, a function might be preferred in many cases since
> you can feed it the result of a query, unlike an ALTER statement). But
> a standardized way for how it's dealt with so that multiple tools
> don't step on each other is a very good idea - and probably one reason
> people don't build this stuff themselves.

> Being able to automate it across many machines is bigger, but most
> people solve that today with things like puppet and chef.

> Being able to build a nice configuration interface into something like
> pgadmin is something that a lot of people ask for - but that's at best
> a secondary effect from having a change like this, which is why we're
> not seeing direct demand for it.

I agree that may be demand is not high, but it is a useful feature considering that commercial databases (Oracle,SQL Server, etc.) and git provides this feature.

With Regards,
Amit Kapila.
As this feature