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

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).

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.

Just my $0.02. Of course, I might be missing the point.

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

> SYNTAX:
> ALTER SYSTEM SET configuration_parameter = value COMMENT 'value';
>
> DESIGN IDEA:
> (a) have a postgresql.conf.auto
> (b) add a default include for postgresql.conf.auto at the beginning of PostgreSQL.conf
> (c) SQL updates go to postgresql.conf.auto, which consists only of"setting = value #comments" .
> (d) We document that settings which are changed manually in postgresql.conf will override postgresql.conf.auto.
>
> IMPLEMENTATION IDEA:
>
> 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.
>
> CCREATION OF AUTO FILE
> 1. during initdb we create the .auto file and it will be empty.
> 2. .conf file will have its first entry as follows
>
>
> #------------------------------------------------------------------------------
> # Postgresql.conf.auto inclusion
> #------------------------------------------------------------------------------
> # Do not edit postgresql.conf.auto file or remove the include.
> # You can Edit the settings below in this file which will override auto-generated file.
>
> include = 'postgresql.conf.auto'
>
>
>
>
> ALGORITHM for ALTER SYSTEM:
> 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 postgresql.conf.auto path. (always the data directory)
> -- Since the .auto file in data directory pg_basebackup will pick it up.
> 3. Create the postgresql.conf.auto.lock 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 postgresql.conf.auto file in read mode.
> 5. Write the new (key, value, comment) in to the postgresql.conf.auto.lock file by using below steps:
> a. read the contents of postgresql.conf.auto in to memory buffer line by line.
> b. Scan for key in postgresql.conf.auto 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 line.
> c. add the new (key, value, comment) to memory buffer to the line as found in step b.
> d. Write the memory buffer into postgresql.conf.auto.lock file.
> -- here memory buffer represent the modified state of the postgresql.conf.auto 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.
>
>
> CLARIFICATION
> 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 postgresql.auto instead of mucking with
> postgresql.conf."
> 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?
>
> Note:
> 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 intervention.
> 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 implemented.
>
>
> Comments/Suggestions about the value of this feature and Implementation Idea?
>
> With Regards,
> Amit Kapila.