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

On Thursday, April 04, 2013 2:52 AM Robert Haas wrote:
> On Wed, Apr 3, 2013 at 2:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >> On Tue, Apr 2, 2013 at 12:19 PM, Peter Eisentraut <peter_e(at)gmx(dot)net>
> wrote:
> >>> It's weird that SET LOCAL and SET SESSION actually *set* the value,
> and
> >>> the second key word determines how long the setting will last. SET
> >>> PERSISTENT doesn't actually set the value. I predict that this
> will be
> >>> a new favorite help-it-doesn't-work FAQ.
> >
> >> I think this is another argument against this particular syntax. I
> >> have always thought that something along the lines of ALTER SYSTEM
> >> would be more appropriate. ALTER DATABASE .. SET and ALTER ROLE ..
> >> SET don't change the value immediately either, and nobody gets
> >> confused about that to my knowledge. But I can see where SET
> >> PERSISTENT could cause that sort of confusion.
> >
> > Yeah, I think I argued for using the SET syntax to start with, but
> > I'm coming around to the position that SET PERSISTENT is too much
> > unlike the behavior of other varieties of SET. ALTER is sounding
> > more attractive to me now. Not sure about "ALTER SYSTEM" in
> particular
> > though --- it's not clear that that has any real merit other than
> > already existing as a keyword. (Not that that's negligible.)
> > ALTER CONFIGURATION is another alternative using an existing keyword
> > that might be worth considering.
>
> Yeah, I thought about something like that. Aside from saving on
> keywords, the reason I like ALTER SYSTEM or similar is that I suspect
> there will be other system-wide things that we may want to let people
> ALTER in the future, so I think that route might avoid an unnecessary
> proliferation of top-level commands. I am not, however, deadly
> attached to the idea, if someone's got a good reason for preferring
> something else.

I think second parameter in SET command telling the scope should be fine. As
I could see Oracle
also has similar syntax for it's ALTER SYSTEM Command (Alter System Scope
[Memory|Spfile|Both]).
Description in short:
SPFILE indicates that the change is made in the server parameter file. The
new
setting takes effect when the database is next shut down and started up
again.
MEMORY indicates that the change is made in memory, takes effect
immediately,
and persists until the database is shut down.

The only reason to show above example is that second parameter telling Scope
exists in other databases as well.

However if you are not convinced with above reasoning, then the Alter syntax
can be as follows:
ALTER SYSTEM SET configuration_parameter {TO | =} {value, | 'value'};

With Regards,
Amit Kapila.