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: "'Chris Corbyn'" <chris(at)w3style(dot)co(dot)uk>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Proposal for Allow postgresql.conf values to be changed via SQL
Date: 2012-10-29 14:14:27
Message-ID: 008901cdb5df$b56238a0$2026a9e0$ (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers

On Monday, October 29, 2012 7:11 PM Chris Corbyn

> What's the use case of this? It sounds like it will just create a
maintenance nightmare where some stuff you expect to lookup in in
postgresql.conf is actually hiding in the .auto file. Assuming only super
users/sysadmins would have the ability to change things in the config file,
wouldn't they be more likely to just do it on the server and edit the .conf
(which among other things, keeps it tidy and orderly).


Basically after this user will have 2 options to change the postgresql.conf

One is by directly editing the postgresql.conf file and

Other is by using SQL commands.

There will be nothing hidden in .auto file, it's just that it will create
separate file for parameters set by SQL command to avoid the hassles of
parsing the postgresql.conf during the processing of SQL command. Anything
changed by user in postgresql.conf will override the values in




>Also, how would you propose to handle settings that require the server to
be restarted, such as checkpoint_segments? It seems like by allowing these
to be set via a command (which isn't really SQL) you're creating the
impression that they will take immediate effect, which isn't the case.


The values will take effect after server restart or by SIGHUP.




Il giorno 30/ott/2012, alle ore 00:31, Amit Kapila ha scritto:


ALTER SYSTEM SET configuration_parameter = value COMMENT 'value';



(a) have a

(b) add a default include for at the beginning of

(c) SQL updates go to, which consists only of"setting =
value #comments" .

(d) We document that settings which are changed manually in postgresql.conf
will override




The main Idea is we create a lock file, it acts as lock to avoid concurrent
edit into .conf auto file

and also as an intermediate file where we keep all the new changes until we
commit the alter system command.        



1. during initdb we create the .auto file and it will be empty.

2. .conf file will have its first entry as follows




# inclusion


# Do not edit file or remove the include.

# You can Edit the settings below in this file which will override
auto-generated file.


include = ''






        1. check whether the given key : value is valid.

                -- This is done so that next read from .auto file should not
throw error.

        2. get path. (always the data directory)

                -- Since the .auto file in data directory pg_basebackup will
pick it up.

        3. Create the file( with O_EXCL flag).

                -- This act as a protection from other backends who are
trying to edit this file.

                -- If already exist we wait for some time by retrying.

         4. Open the file in read mode.      

         5. Write the new (key, value, comment) in to the file by using below steps:

           a. read the contents of in to memory buffer
line by line.        

           b. Scan for key in file.

                  if found get the line number in file such that where we
have to insert the new (key,value).

                  else we should write the new (key, value) pair to last

           c. add the new (key, value, comment) to memory buffer to the line
as found in step b.

           d. Write the memory buffer into file.  

                -- here memory buffer  represent the modified state of the file.

           e. Commit the .lock file.

                 -- Here rename the lock file to auto file.

                 -- If auto file is opened by other process (SIGHUP
processing) then we retry rename for some time

                   other wise alter system command fails.

           f. If any error in between rollback lock file

                -- here delete the lock file.




1. Tom, the below is mentioned by you in one of the discussions for this
topic. I need small clarification:

   "About the only change I want to make immediately is that initdb ought to
shove its settings into instead of mucking with


    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

    parameters be commented in .conf?

2. Do .auto file needs to be included by default?

3. Can the path of .auto be fixed as data directory path?



1. Only One backend can edit conf file at a time others wait.

2. Suppose .auto have invalid entry eg: listening port number mentioned is
taken up by other application

   then if we try to restart the postgres it fails. This need manual

3. This command cannot be executed inside the transaction block. Not sure
what to do for this part, whether it needs to be supported

   in a block?

4. currently command for reset or invalidation of (key, value) is not



Comments/Suggestions about the value of this feature and Implementation


With Regards,

Amit Kapila.


In response to


pgsql-hackers by date

Next:From: Amit KapilaDate: 2012-10-29 14:32:11
Subject: Re: Performance Improvement by reducing WAL for Update Operation
Previous:From: Amit KapilaDate: 2012-10-29 14:06:08
Subject: Re: Proposal for Allow postgresql.conf values to be changed via SQL

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