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

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. So I think that SET
PERSISTENT should handle the case where postgresql.auto.conf doesn't
exist.

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.

>> 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).

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?

Regards,

--
Fujii Masao