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

On Tuesday, November 13, 2012 9:29 AM Amit kapila wrote:
On Monday, November 12, 2012 12:07 PM Greg Smith wrote:
On 11/9/12 11:59 PM, Amit kapila wrote:

>> 1) Change to add scanning a .conf directory in the default configuration
>> using include-dir. This is a quick fix. I predict most of the
>> headaches around it will end up being for packagers rather than the core
>> code to deal with.

>> You could submit this as a small thing to be evaluated on its own. How
>> it's done is going to be controversial. Might as well get that fighting
>> focused against a sample implementation as soon as possible.

> As per my understanding,
> a. during initdb, new conf directory can be created and also create .auto file in it.
> b. use include_dir at end of postgresql.conf to include directory created in above step.
> c. server start/sighup will take care of above include_dir

WIP patch to address above point is attached.
It needs cleanup w.r.t moving function for absolute path to common place where initdb as well as server code can use it.

>> 2) Get familiar with navigating the GUC data and figuring out what,
>> exactly, needs to be written out. This should include something that
>> navigates like things appear after a RESET, ignoring per-user or
>> per-session changes when figuring out what goes there. It seems
>> inevitable that some amount of validating against the source
>> information--what pg_settings labels source, sourcefile, and sourceline
>> will be needed. An example is the suggestion Magnus made for confirming
>> that the include-dir is still active before writing something there.

> Okay, what I will do for this is that I shall explain in next mail the way to do by navigating GUC's.

One basic idea to do execution of SQL Command with GUC's is described as below:

1. Take Global level lock so that no other session can run the ALTER SYSTEM/built-in Command to change config parameter
2. Navigate through GUC variable's and remember all GUC's (of .auto file ) reset_val.
3. Compare the config parameter to be changed with the parameters stored in step-2 and if it exists, replace its value else add new variable-value to it.
4. Flush the file with all parameters computed in Step-3.
5. Signal all other backends to update this value in their GUC's reset_val, so that all backends always have recent copy.
6. When all backends have updated, change corresponding reset_val in current session as well.
7. Release the Global level lock.

Some problems with the above approach:
a. When the signal is sent in step-5, if other backend is also waiting on global lock, it can cause signal handling little tricky,
may be special handling needs to be done to handle this situation
b. If after step-5 or 6, rollback happened it might be difficult to rollback. In general if this command executes in transaction-block, the same issue can arise.
c. Updation of reset_val for parameters which cannot be reset without restart is wrong. For such kind of parameters, I think we can give warning to users.

I think this is the initial idea to check if I am thinking on lines you have in mind.

Comments/Suggestions?

With Regards,
Amit Kapila.