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

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

From: Fujii Masao <masao(dot)fujii(at)gmail(dot)com>
To: Amit Kapila <amit(dot)kapila(at)huawei(dot)com>
Cc: 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, 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-23 16:40:54
Message-ID: (view raw or whole thread)
Lists: pgsql-hackers
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. So I think that SET
PERSISTENT should handle the case where doesn't

We might be able to expect that 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, 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).

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


Fujii Masao

In response to


pgsql-hackers by date

Next:From: Bruce MomjianDate: 2012-11-23 16:58:43
Subject: Re: Further pg_upgrade analysis for many tables
Previous:From: Tom LaneDate: 2012-11-23 16:24:48
Subject: Re: [Re] [Re] Re: PANIC: could not write to log file

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