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

On Sunday, November 18, 2012 3:28 AM Tom Lane wrote:
> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> writes:
> > Do we really need to store the settings in a system table?
> > Since WAL would be generated when storing the settings
> > in a system table, this approach seems to prevent us from
> > changing the settings in the standby.
>
> That's a really good point: if we try to move all GUCs into a system
> table, there's no way for a standby to have different values; and for
> some of them different values are *necessary*.
>
> I think that shoots down this line of thought entirely. Can we go
> back to the plain "write a file" approach now?

Sure.

> I think a "SET
> PERSISTENT" command that's disallowed in transaction blocks and just
> writes the file immediately is perfectly sensible.

I got the point that we can disallow inside transaction blocks.
Just to clarify, that by above do you mean to say that file write (rename
from .conf.lock to .conf) should be done as soon as
command execution ends rather than the transaction end or it should be done
at transaction end?

Still I think we are not able to completely rule out one or other from
syntax perspective.

We have discussion about below 3 different syntaxes for this command

1. ALTER SYSTEM
2. SET PERSISENT
3. pg_change_conf()

I think to conclude, we need to evaluate which syntax has more advantages.
Comparison for above syntax

1. If we want to allow multiple configuration parameters now or in future to
be updated in single command. Example
a. Alter System Set port = 5435, max_connections = 100;
b. Select pg_change_conf('port', 5435),
pg_change_conf('max_connections',100);

I think it might be convenient for user to use Command syntax.

2. If we provide built-in, user can try to use in some complicated syntax
Select 1/0 from tbl where a= pg_change_conf('max_connections',100);
The design and test needs to take care of such usage, so that it doesn't
create any problem.

3. Using with the SET command syntax can create some confusion for user, as
SET SESSION | LOCAL option can work
in transaction blocks, but this feature may not be required to work in
transaction blocks as it will change in
config file which can take effect only on re-start or sighup.

I believe some more thoughts and suggestions are required to conclude.

Thoughts/Suggestions/Comments?

With Regards,
Amit Kapila.