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

On Friday, November 23, 2012 10:10 PM Fujii Masao wrote:
On Fri, Nov 23, 2012 at 6:56 PM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
>> When I remove postgresql.auto.conf, SET PERSISTENT failed.
>>
>>> =# SET PERSISTENT synchronous_commit = 'local';
>>> ERROR: failed to open "postgresql.auto.conf" file
>
>> There can be 2 ways to handle this, either we recreate the
>> "postgresql.auto.conf" file or give error.
>> I am not sure if user tries to delete internal files, what should be exact
>> behavior?
>> Any suggestion?

> I prefer to recreate it.

>$PGDATA/config_dir is specified in include_dir by default. Users might
>create and remove the configuration files in that directory many times.
>So I'm not surprised even if a user accidentally removes
>postgresql.auto.conf in that directory. Also users might purposely remove
>that file to reset all the settings by SET PERSISTENT.

One of the suggestion in this mail chain was if above happens then at restart, it should display/log message.
However I think if such a situation happens then we should provide some mechanism to users so that the settings through
command should work.

> So I think that SET
>PERSISTENT should handle the case where postgresql.auto.conf doesn't
>exist.

If we directly create a file user might not be aware that his settings done in previous commands will not work.
So how about if we display NOTICE also when internally for SET PERSISTENT new file needs to be created.
Notice should suggest that the settings done by previous commands are lost due to manual deletion of internal file.

>We might be able to expect that postgresql.auto.conf is not deleted by
>a user if it's in $PGDATA/global or base directory.

So do you mean to say that if we don't find file in config_dir, then we should search in $PGDATA/global or base directory?
Can't we mandate to user that even if it is deleted, the file has to be only expected in config_dir.

>>> We should implement "RESET PERSISTENT"? Otherwise, there is no way
>>> to get rid of the parameter setting from postgresql.auto.conf, via SQL.
>>> Also
>>> We should implement "SET PERSISTENT name TO DEFAULT"?
>
>> Till now, I have not implemented this in patch, thinking that it can be done
>> as a 2nd part if basic stuff is ready.
>> However I think you are right without one of "RESET PERSISTENT" or "SET
>> PERSISTENT name TO DEFAULT", it is difficult for user
>> to get rid of parameter.
>> Will "SET PERSISTENT name TO DEFAULT" be sufficient or do you think both are
>> necessary, because RESET PERSISTENT also internally might need
>> to behave similarly.
>
>> For implementation of "SET PERSISTENT name TO DEFAULT", there can be 2 ways
>> 1) Delete the entry from postgresql.auto.conf
>> 2) Update the entry value in postgresql.auto.conf to default value

>Both seems to be useful. I think that "SET ... TO DEFAULT" is suitable for
>2), and "RESET PERSISTENT ..." is suitable for 1).

For other commands behavior for "SET ... TO DEFAULT" and "RESET ..." is same.
In the docs it is mentioned "RESET "is an alternative name for "SET ... TO DEFAULT"

However still the way you are telling can be done.
Others, any objection to it, else I will go ahead with it?

> Another comment is:

> What happens if the server crashes while SET PERSISTENT is writing the
> setting to the file? A partial write occurs and restart of the server would fail
> because of corrupted postgresql.auto.conf?

This situation will not happen as SET PERSISTENT command will first write to ".lock" file and then at commit time,
rename it to ".auto.conf".

With Regards,
Amit Kapila.