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: CAHGQGwHKn9sq1fO3=ezCCcYtMf3c=DnhPM18fDBOt71ohAEDGA@mail.gmail.com (view raw or flat)
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 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


In response to

Responses

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-2014 The PostgreSQL Global Development Group