Proposal for Allow postgresql.conf values to be changed via SQL - Mailing list pgsql-hackers

<div class="WordSection1"><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">SYNTAX:</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">ALTERSYSTEM SET configuration_parameter = value COMMENT
'value';</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><p
class="MsoNormal"><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif"">DESIGN IDEA: </span><p
class="MsoNormal"><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif"">(a) have a postgresql.conf.auto
</span><pclass="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">(b) add a default include
forpostgresql.conf.auto at the beginning of PostgreSQL.conf </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">(c)SQL updates go to postgresql.conf.auto, which consists
onlyof"setting = value #comments" . </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">(d)We document that settings which are changed manually in
postgresql.confwill override postgresql.conf.auto. </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">IMPLEMENTATIONIDEA: </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Themain Idea is we create a lock file, it acts as lock to
avoidconcurrent edit into .conf auto file </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">andalso as an intermediate file where we keep all the new
changesuntil we commit the alter system command.         </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">CCREATIONOF  AUTO FILE </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">1.during initdb we create the .auto file and it will be
empty.</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">2. .conf file will
haveits first entry as follows </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">#------------------------------------------------------------------------------
</span><pclass="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""># Postgresql.conf.auto
inclusion</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">#------------------------------------------------------------------------------
</span><pclass="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""># Do not edit
postgresql.conf.autofile or remove the include. </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">#You can Edit the settings below in this file which will
overrideauto-generated file.</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">include= 'postgresql.conf.auto' </span><p
class="MsoNormal"><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">   </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">      </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">ALGORITHMfor ALTER SYSTEM:         </span><p
class="MsoNormal"><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif"">        1. check whether the given
key: value is valid. </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">                --This is done so that next read from .auto
fileshould not throw error. </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">        2.get postgresql.conf.auto path. (always the data
directory)</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">                --Since the .auto file in data directory
pg_basebackupwill pick it up. </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">        3.Create the postgresql.conf.auto.lock file( with
O_EXCLflag). </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">                --This act as a protection from other
backendswho are trying to edit this file. </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">                --If already exist we wait for some time by
retrying.</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">         4. Open
thepostgresql.conf.auto file in read mode.      </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">         5.Write the new (key, value, comment) in to the
postgresql.conf.auto.lockfile by using below steps: </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">           a.read the contents of postgresql.conf.auto in
tomemory buffer line by line.         </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">           b.Scan for key in postgresql.conf.auto file.
</span><pclass="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">                  if found
getthe line number in file such that where we have to insert the new (key,value). </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">                  elsewe should write the new (key, value)
pairto last line.           </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">           c.add the new (key, value, comment) to memory
bufferto the line as found in step b. </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">           d.Write the memory buffer into
postgresql.conf.auto.lockfile.   </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">                --here memory buffer  represent the
modifiedstate of the postgresql.conf.auto file. </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">           e.Commit the .lock file. </span><p
class="MsoNormal"><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif"">                 -- Here rename the
lockfile to auto file. </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">                 --If auto file is opened by other process
(SIGHUPprocessing) then we retry rename for some time </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">                   otherwise alter system command
fails.                                </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">           f.If any error in between rollback lock file
</span><pclass="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">                -- here
deletethe lock file. </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">                </span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">CLARIFICATION</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">1.Tom, the below is mentioned by you in one of the
discussionsfor this topic. I need small clarification: </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">   "Aboutthe only change I want to make immediately is that
initdbought to shove its settings into postgresql.auto instead of mucking with </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">postgresql.conf."</span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">   So do you mean to say the settings done by initdb (like
max_connections,etc.) need to be in .auto file instead of .conf and let these </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">    parametersbe commented in .conf? </span><p
class="MsoNormal"><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif"">2. Do .auto file needs to be
includedby default?</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">3. Can
thepath of .auto be fixed as data directory path? </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Note:</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">1.Only One backend can edit conf file at a time others
wait.</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">2. Suppose .auto
haveinvalid entry eg: listening port number mentioned is taken up by other application </span><p
class="MsoNormal"><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif"">   then if we try to restart the
postgresit fails. This need manual intervention. </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">3.This command cannot be executed inside the transaction
block.Not sure what to do for this part, whether it needs to be supported </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">   ina block?</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">4.currently command for reset or invalidation of (key,
value)is not implemented.   </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Comments/Suggestionsabout the value of this feature and
ImplementationIdea?</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""> </span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">WithRegards,</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif"">AmitKapila.</span></div>