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: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "'Fujii Masao'" <masao(dot)fujii(at)gmail(dot)com>
Cc: <cedric(at)2ndquadrant(dot)com>, <pgsql-hackers(at)postgresql(dot)org>, "'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>
Subject: Re: Proposal for Allow postgresql.conf values to be changed via SQL
Date: 2012-11-19 07:01:00
Message-ID: 007101cdc623$a2e1e280$e8a5a780$@kapila@huawei.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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.





In response to

Responses

pgsql-hackers by date

Next:From: Amit KapilaDate: 2012-11-19 07:04:27
Subject: Re: Proposal for Allow postgresql.conf values to be changed via SQL
Previous:From: Michael PaquierDate: 2012-11-19 06:46:31
Subject: Re: [PATCH 13/14] Introduce pg_receivellog, the pg_receivexlog equivalent for logical changes

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