Skip site navigation (1) Skip section navigation (2)

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

From: Amit kapila <amit(dot)kapila(at)huawei(dot)com>
To: Fujii Masao <masao(dot)fujii(at)gmail(dot)com>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "cedric(at)2ndquadrant(dot)com" <cedric(at)2ndquadrant(dot)com>, Robert Haas<robertmhaas(at)gmail(dot)com>, Greg Smith <greg(at)2ndquadrant(dot)com>, Josh Berkus<josh(at)agliodbs(dot)com>, Magnus Hagander <magnus(at)hagander(dot)net>, Christopher Browne <cbbrowne(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Subject: Re: Proposal for Allow postgresql.conf values to be changed via SQL
Date: 2012-11-24 05:10:45
Message-ID: 6C0B27F7206C9E4CA54AE035729E9C383BE8A349@szxeml509-mbs (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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, SET PERSISTENT failed.
>>> =# SET PERSISTENT synchronous_commit = 'local';
>>> ERROR:  failed to open "" file
>> There can be 2 ways to handle this, either we recreate the
>> "" 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
> 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 doesn't

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 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, 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
>> 2) Update the entry value in 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

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.

In response to


pgsql-hackers by date

Next:From: Andres FreundDate: 2012-11-24 15:50:05
Subject: Re: Problem Observed in behavior of Create Index Concurrently and Hot Update
Previous:From: Bruce MomjianDate: 2012-11-24 03:31:13
Subject: Re: In pg_upgrade, copy fsm, vm, and extent files by checking for fi

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group