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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
646 messages Options
1234 ... 33
Reply | Threaded
Open this post in threaded view
|

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

amitkapila

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.

Reply | Threaded
Open this post in threaded view
|

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

Merlin Moncure-2
On Mon, Oct 29, 2012 at 8:31 AM, Amit Kapila <[hidden email]> wrote:
> SYNTAX:
>
> ALTER SYSTEM SET configuration_parameter = value COMMENT 'value';
>

> Comments/Suggestions about the value of this feature and Implementation
> Idea?

How is this better than simply updating them through the pg_settings
view (in SQL)?

merlin


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

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

Chris Corbyn
In reply to this post by amitkapila
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.

Reply | Threaded
Open this post in threaded view
|

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

amitkapila
In reply to this post by Merlin Moncure-2
On Monday, October 29, 2012 7:06 PM Merlin Moncure wrote:

> On Mon, Oct 29, 2012 at 8:31 AM, Amit Kapila <[hidden email]>
> wrote:
> > SYNTAX:
> >
> > ALTER SYSTEM SET configuration_parameter = value COMMENT 'value';
> >
>
> > Comments/Suggestions about the value of this feature and
> Implementation
> > Idea?
>
> How is this better than simply updating them through the pg_settings
> view (in SQL)?

1. Comments cannot be given in pg_settings.
2. This is the syntax decided in the discussion last happened on this topic.
I am sorry, I should have mentioned the link of previous discussion in
proposal mail.
http://archives.postgresql.org/pgsql-hackers/2010-10/msg00764.php

With Regards,
Amit Kapila.



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

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

amitkapila
In reply to this post by Chris Corbyn

 

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

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 postgresql.conf.auto

 

 

 

>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:



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.

 

Reply | Threaded
Open this post in threaded view
|

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

Josh berkus
In reply to this post by Chris Corbyn
On 10/29/12 6:40 AM, Chris Corbyn wrote:
> 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).

The use is the ability to manage dozens, or hundreds, of PostgreSQL
servers via Port 5432.  It would also make writing an auto-configurator
easier.

I agree that there's not much benefit if you're only managing a single
PostgreSQL server.  There's a lot of benefit for those of us who have to
manage a lot of them though.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

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

Christopher Browne-3
On Tue, Oct 30, 2012 at 5:25 PM, Josh Berkus <[hidden email]> wrote:

> On 10/29/12 6:40 AM, Chris Corbyn wrote:
>> 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).
>
> The use is the ability to manage dozens, or hundreds, of PostgreSQL
> servers via Port 5432.  It would also make writing an auto-configurator
> easier.
>
> I agree that there's not much benefit if you're only managing a single
> PostgreSQL server.  There's a lot of benefit for those of us who have to
> manage a lot of them though.

I rather think that the fact that postgresql.conf has supported an
"include directive" since at least as far back as 8.2 (likely further;
I'll not bother spelunking further into the docs) makes this extremely
troublesome.

We have long supported the notion that this configuration does not
have a Unique Place to be (e.g. - if you use INCLUDE, then there are
at least two possible places).

I should think that doing this requires heading back towards there
being a single unique configuration stream, and over the course of
several versions, deprecating the INCLUDE directive.

I imagine it means we'd want to come up with a representation that has
suitable semantics for being written to, make sure it is reasonably
expressive *without* INCLUDE, and establish a migration path between
the old and new forms.  At some point, the old form can be treated as
vestigal, and be dropped.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

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

Josh berkus

> I should think that doing this requires heading back towards there
> being a single unique configuration stream, and over the course of
> several versions, deprecating the INCLUDE directive.

Oh, maybe I should take a closer look at Amit's proposal then.  I
thought we planned to make use of the INCLUDE facility for SET
PERSISTENT, including supporting include-if-exists.  Possibly what he's
proposing and what I thought our last consensus were are highly divergent.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

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

Hannu Krosing-5
In reply to this post by amitkapila
On 10/29/2012 03:14 PM, Amit Kapila wrote:

 

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

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.

If interested I have somewhere pl/pythhonu functions for both looking at and
changing parameters in postgresql.conf file,

It even keeps the old value and adds comments both to old and to the new one abot who an when changed it.
Could also be extended to fpr example rotate last 10 postgreSQL conf files and/or skip rewriting the file in case the effective value of GUC did not change.

Cheers,
Hannu

Reply | Threaded
Open this post in threaded view
|

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

Alvaro Herrera-9
In reply to this post by Christopher Browne-3
Christopher Browne escribió:

> On Tue, Oct 30, 2012 at 5:25 PM, Josh Berkus <[hidden email]> wrote:
> > On 10/29/12 6:40 AM, Chris Corbyn wrote:
> >> 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).
> >
> > The use is the ability to manage dozens, or hundreds, of PostgreSQL
> > servers via Port 5432.  It would also make writing an auto-configurator
> > easier.
> >
> > I agree that there's not much benefit if you're only managing a single
> > PostgreSQL server.  There's a lot of benefit for those of us who have to
> > manage a lot of them though.
>
> I rather think that the fact that postgresql.conf has supported an
> "include directive" since at least as far back as 8.2 (likely further;
> I'll not bother spelunking further into the docs) makes this extremely
> troublesome.

This is precisely the reason why "source file" and "source line" are now
tracked for configuration parameters.  If a setting is in the auto file
(or any other file), it would be very simple to find.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by Josh berkus
Josh Berkus <[hidden email]> writes:
>> I should think that doing this requires heading back towards there
>> being a single unique configuration stream, and over the course of
>> several versions, deprecating the INCLUDE directive.

> Oh, maybe I should take a closer look at Amit's proposal then.  I
> thought we planned to make use of the INCLUDE facility for SET
> PERSISTENT, including supporting include-if-exists.  Possibly what he's
> proposing and what I thought our last consensus were are highly divergent.

I'm not convinced we ever *had* a consensus on this.  There were
proposals, but I'm not sure a majority ever bought into any one of 'em.
The whole problem of intermixing manual editing and programmatic editing
is just a big can of worms, and not everybody is prepared to give up the
former to have the latter.

You can, if you are so inclined, implement something functionally
equivalent to Amit's proposal today using contrib/adminpack's
pg_file_write --- okay, it's much less convenient than a built-in
implementation would be, but you can drop some variable assignments into
a file and then put a suitable INCLUDE into the otherwise-static main
config file.  The fact that this isn't being done by a large number of
people (is anybody at all actually doing it?) suggests to me that maybe
the demand isn't all that great.

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

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

Andres Freund-3
On Tuesday, October 30, 2012 11:24:20 PM Tom Lane wrote:
> The fact that this isn't being done by a large number of
> people (is anybody at all actually doing it?) suggests to me that maybe
> the demand isn't all that great.

It might also be that the idea of implementing that yourself is quite scary.

Also you would need to parse the file to reset values which isn't exactly
easy... I think it only really becomes viable with the introduction of
directory includes where you can use one file per value.

Andres
--
 Andres Freund                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

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

Josh berkus
In reply to this post by Tom Lane-2
Tom,

> I'm not convinced we ever *had* a consensus on this.  There were
> proposals, but I'm not sure a majority ever bought into any one of 'em.
> The whole problem of intermixing manual editing and programmatic editing
> is just a big can of worms, and not everybody is prepared to give up the
> former to have the latter.

Well, I think we have consensus that intermixing is impractical, which
is why every further proposal is around having a separate file for the
SQL-modified values.  And yes, we have a certain amount of "You'll get
my carefully edited postgresql.conf when you pry it out of my cold, dead
hands" going on.

The real consensus problem, AFAICT, is that while we have consensus that
we would like something like SET PERSISTENT as an *option*, there's a
Hurricane Sandy-sized Bikeshedding Windstorm about how, exactly, people
would like it to work.  Personally, I would prefer the implementation
which actually gets committed. ;-)

> You can, if you are so inclined, implement something functionally
> equivalent to Amit's proposal today using contrib/adminpack's
> pg_file_write --- okay, it's much less convenient than a built-in
> implementation would be, but you can drop some variable assignments into
> a file and then put a suitable INCLUDE into the otherwise-static main
> config file.  The fact that this isn't being done by a large number of
> people (is anybody at all actually doing it?) suggests to me that maybe
> the demand isn't all that great.

It suggest nothing of the sort:

1. a tiny minority of our users even know about adminpack

2. implementing it the way you suggest would require a hacker's
understanding of Postgres, which is an even smaller minority.

On the other hand, the success of tools like Puppet have made having SET
PERSISTENT a lot less urgent for many large-scale installation managers.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

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

amitkapila
On Wednesday, October 31, 2012 4:14 AM Josh Berkus wrote:

> Tom,
>
> > I'm not convinced we ever *had* a consensus on this.  There were
> > proposals, but I'm not sure a majority ever bought into any one of
> 'em.
> > The whole problem of intermixing manual editing and programmatic
> editing
> > is just a big can of worms, and not everybody is prepared to give up
> the
> > former to have the latter.
>
> Well, I think we have consensus that intermixing is impractical, which
> is why every further proposal is around having a separate file for the
> SQL-modified values.  And yes, we have a certain amount of "You'll get
> my carefully edited postgresql.conf when you pry it out of my cold, dead
> hands" going on.

  I think for that part it was discussed that always postgresql.conf values will override the values of .auto.
 
 
> The real consensus problem, AFAICT, is that while we have consensus that
> we would like something like SET PERSISTENT as an *option*, there's a
> Hurricane Sandy-sized Bikeshedding Windstorm about how, exactly, people
> would like it to work.  Personally, I would prefer the implementation
> which actually gets committed. ;-)

I think the original syntax is proposed by Robert Hass by reffering Oracle's syntax in below mail:
http://archives.postgresql.org/pgsql-hackers/2010-10/msg00953.php

and then finally the Syntax which I have used in my proposal was suggested by Tom in below mail:
http://archives.postgresql.org/pgsql-hackers/2010-10/msg00977.php


Do you see any discrepancy in the proposal I have sent and what have been concluded in previous discussions?

With Regards,
Amit Kapila.



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

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

amitkapila
In reply to this post by Andres Freund-3
On Wednesday, October 31, 2012 3:58 AM Andres Freund wrote:
> On Tuesday, October 30, 2012 11:24:20 PM Tom Lane wrote:
> > The fact that this isn't being done by a large number of
> > people (is anybody at all actually doing it?) suggests to me that
> maybe
> > the demand isn't all that great.
>
> It might also be that the idea of implementing that yourself is quite
> scary.

 
> Also you would need to parse the file to reset values which isn't
> exactly
> easy...

As this new file (postgresql.conf.auto) will not be edited by users, so it
might not be
difficult to handle it, as the code will now the exact format of file.
The problem can be there if we need to parse postgresql.conf to set/reset
values, as for that
the format is not fixed. However that is taken care by having 2 files.
Please point me, if I misunderstood the difficulty raised by you.


With Regards,
Amit Kapila.




--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

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

amitkapila
In reply to this post by Hannu Krosing-5

On Wednesday, October 31, 2012 3:32 AM Hannu Krosing wrote:
On 10/29/2012 03:14 PM, Amit Kapila wrote:

 

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

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.

 

 

>If interested I have somewhere pl/pythhonu functions for both looking at and
changing parameters in postgresql.conf file,

 

In the previous discussion about this feature, it was mentioned by many people as postgresql.conf can be editied by users in many ways, it will be difficult to come up with a reliable function which can handle all possible cases. That is why I have taken the approach of having 2 separate files, one user editable and other can be only edited by SQL Commands.

In anycase if you have those functions readily available then please send them, it can be useful for me.

 

With Regards,

Amit Kapila.

Reply | Threaded
Open this post in threaded view
|

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

amitkapila
In reply to this post by Josh berkus
On Wednesday, October 31, 2012 3:25 AM Josh Berkus
> > I should think that doing this requires heading back towards there
> > being a single unique configuration stream, and over the course of
> > several versions, deprecating the INCLUDE directive.
>
> Oh, maybe I should take a closer look at Amit's proposal then.  I
> thought we planned to make use of the INCLUDE facility for SET
> PERSISTENT, including supporting include-if-exists.  Possibly what he's
> proposing and what I thought our last consensus were are highly
> divergent.

Currently INCLUDE is used for including postgresql.conf.auto in postgresql.conf by default.
Can you please let me know what is the expectation?

Instead of INCLUDE,
1. include-if-exists can be used.
2. In code first read .auto file then .conf and override the values read from .auto by values from .conf.


With Regards,
Amit Kapila.



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

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

Magnus Hagander-2
In reply to this post by Tom Lane-2
On Tue, Oct 30, 2012 at 11:24 PM, Tom Lane <[hidden email]> wrote:

> Josh Berkus <[hidden email]> writes:
>>> I should think that doing this requires heading back towards there
>>> being a single unique configuration stream, and over the course of
>>> several versions, deprecating the INCLUDE directive.
>
>> Oh, maybe I should take a closer look at Amit's proposal then.  I
>> thought we planned to make use of the INCLUDE facility for SET
>> PERSISTENT, including supporting include-if-exists.  Possibly what he's
>> proposing and what I thought our last consensus were are highly divergent.
>
> I'm not convinced we ever *had* a consensus on this.  There were
> proposals, but I'm not sure a majority ever bought into any one of 'em.

I thought there was a consensus. But given that the one I thought we
had consensus on was different, I'm not sure we can correctly call it
consensus.

What we discussed at that time was to have a *function* that changes
the permanent configuration, and not actually extend the syntax of the
system. As a starting point.

The idea at the time was to use the include *directory* functionality,
for say a "config.d" directory in pgdata. The builtin one would then
use a predictable filename in this directory, so that the DBA who
prefers it can drop files both before and after that file into the
directory.

> The whole problem of intermixing manual editing and programmatic editing
> is just a big can of worms, and not everybody is prepared to give up the
> former to have the latter.
>
> You can, if you are so inclined, implement something functionally
> equivalent to Amit's proposal today using contrib/adminpack's
> pg_file_write --- okay, it's much less convenient than a built-in
> implementation would be, but you can drop some variable assignments into
> a file and then put a suitable INCLUDE into the otherwise-static main
> config file.  The fact that this isn't being done by a large number of
> people (is anybody at all actually doing it?) suggests to me that maybe
> the demand isn't all that great.

The demand for running something like thta manually isn't all that
great, I believe. This is why I think using a function for it is
perfectly OK, and we don't necessarily need ALTER SYSTEM or something
like that. (In fact, a function might be preferred in many cases since
you can feed it the result of a query, unlike an ALTER statement). But
a standardized way for how it's dealt with so that multiple tools
don't step on each other is a very good idea - and probably one reason
people don't build this stuff themselves.


Being able to automate it across many machines is bigger, but most
people solve that today with things like puppet and chef.

Being able to build a nice configuration interface into something like
pgadmin is something that a lot of people ask for - but that's at best
a secondary effect from having a change like this, which is why we're
not seeing direct demand for it.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

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

Josh berkus
In reply to this post by amitkapila
Amit,

I think you can simplify this task by forgetting about parsing the .auto
file entirely when writing it.  That is, the .auto file should be
regenerated, and should write out whatever has been set in pg_settings,
regardless of what was in the file beforehand.  I don't see the value in
parsing the file before writing it out.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

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

amitkapila
On Wednesday, October 31, 2012 10:21 PM Josh Berkus wrote:
Amit,

> I think you can simplify this task by forgetting about parsing the .auto
> file entirely when writing it.  That is, the .auto file should be
> regenerated, and should write out whatever has been set in pg_settings,
> regardless of what was in the file beforehand.  I don't see the value in
> parsing the file before writing it out.

   In that case how the new value of config parameter as set by user, will go in .auto file.
   Shall we change in guc, from where pg_settings take the values?

   Another point is curretly pg_settings doesn't have comments, so user will not be allowed to give comments with new value of config parameter.
   Is that okay?

With Regards,
Amit Kapila.

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
1234 ... 33