Permanent settings
Currently, pgAdmin supports editing postgresql.conf remotely using the
adminpack to open the file, change it locally in memory, and using the
adminpack again to write it back. This means that in theory pgAdmin needs a
full postgresql.conf parser. Right now it doesn't have this - it just
exposes the config file itself. Which sucks for usability, and it's
something I've heard a lot of people complain about. Other databases (in my
personal experience MSSQL, but IIRC I've had people say the same about
other ones as well) support configuring the database remotely (and using a
GUI for the most common options), and this is a feature that a lot of users
are lacking in PostgreSQL. I'd like to do something about that.
What I'd really like to see is something like a new keyword on the SET
command, so you could to SET PERMANENT foo=bar, which would write the
configuration back into postgresql.conf.
I don't have a complete solution for how to actually implement it, so I'm
just throwing out some ideas for comment.
I don't think we need to be able to parse and deal with "very complex
configuration files", as long as we're not likely to corrupt them badly.
The task got a bit harder with the support of include files, but I'm sure
it's doable.
One way might be to simply have the config file reader store the location
for each setting where it was found, and when you do a SET PERMANENT (if
that's what we'd call it) it'll go back to that place and make the
modification there. If a setting hasn't previously been set, we could just
append it to the end of the main configuration file.
One thing that can be hard to deal with is comments. It would be good if
there was some way to support reading/writing simple comments (say a # at
the end of the line) through this API, but I think it's OK not to deal with
complex multi-line comments. I think it's fairly safe to say that the vast
majority of users will *either* change their configuration through the
config file *or* through the API. Or those that use both aren't likely to
use really complex combinations of config files and comments and such.
(before someone complains about the "argh, editing config files remote is
insecure" - we can always have a config option to turn it off. And it can
still be protected by not giving the server write permissions on the file,
or selinux, or whatever)
Thoughts? More ranting?
//Magnus
* Magnus Hagander <magnus@hagander.net> [080219 09:37]:
One way might be to simply have the config file reader store the location
for each setting where it was found, and when you do a SET PERMANENT (if
that's what we'd call it) it'll go back to that place and make the
modification there. If a setting hasn't previously been set, we could just
append it to the end of the main configuration file.One thing that can be hard to deal with is comments. It would be good if
there was some way to support reading/writing simple comments (say a # at
the end of the line) through this API, but I think it's OK not to deal with
complex multi-line comments. I think it's fairly safe to say that the vast
majority of users will *either* change their configuration through the
config file *or* through the API. Or those that use both aren't likely to
use really complex combinations of config files and comments and such.
Speaking as one who favours the unix admin style (i.e. editing the
config file), take the following with a grain of salt.
But if you *need* a way to "set permanent", couldn't you do with with
the following KISS idea?
Any "set permanent" settings should be *appended* to the main config
file, preferably with a comment line, like:
# Set by user <USER> from client <CLIENT> on <TIMESTAMP>
some_guc option = some_value
This does 2 things:
1) Eliminates a need for a fancy config parser/editor/rewriter
2) Makes it very easy to implement
3) Even allows adminpack to have a "set_permanent" function that could do it
all internally, and not actually need support in the backend core.
This relies on the fact that a "later" setting over-rides an earlier
one. This also means that your postgres user actually has write access
to the config files (is this something people normally allow?) I guess
this "write" problem could be overcome with yet-another-guc to specify
the "permanent write file" path..
a.
--
Aidan Van Dyk Create like a god,
aidan@highrise.ca command like a king,
http://www.highrise.ca/ work like a slave.
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
owner@postgresql.org] On Behalf Of Magnus Hagander
Sent: Tuesday, February 19, 2008 8:36 AM
To: pgsql-hackers
Subject: [HACKERS] Permanent settingsWhat I'd really like to see is something like a new keyword on the SET
command, so you could to SET PERMANENT foo=bar, which would write the
configuration back into postgresql.conf.
How about putting an indicator in the postgresql.conf file dynamic=1 and
then the db could manage the file else the dynamic change wouldn't stick
on a restart? You wouldn't need to add a new keyword this way and less
likely for a DBA to mess up the syntax.
Jon
On Tue, Feb 19, 2008 at 09:53:30AM -0500, Aidan Van Dyk wrote:
* Magnus Hagander <magnus@hagander.net> [080219 09:37]:
One way might be to simply have the config file reader store the location
for each setting where it was found, and when you do a SET PERMANENT (if
that's what we'd call it) it'll go back to that place and make the
modification there. If a setting hasn't previously been set, we could just
append it to the end of the main configuration file.One thing that can be hard to deal with is comments. It would be good if
there was some way to support reading/writing simple comments (say a # at
the end of the line) through this API, but I think it's OK not to deal with
complex multi-line comments. I think it's fairly safe to say that the vast
majority of users will *either* change their configuration through the
config file *or* through the API. Or those that use both aren't likely to
use really complex combinations of config files and comments and such.Speaking as one who favours the unix admin style (i.e. editing the
config file), take the following with a grain of salt.But if you *need* a way to "set permanent", couldn't you do with with
the following KISS idea?Any "set permanent" settings should be *appended* to the main config
file, preferably with a comment line, like:
# Set by user <USER> from client <CLIENT> on <TIMESTAMP>
some_guc option = some_value
Are you suggesting we keep appending? So if I set the same parameter 100
times, it would show up on 100 rows?
This does 2 things:
1) Eliminates a need for a fancy config parser/editor/rewriter
2) Makes it very easy to implement
3) Even allows adminpack to have a "set_permanent" function that could do it
all internally, and not actually need support in the backend core.
I should warn you that it's on my radar to try to find the compromises
necessary to merge the required functionality away from adminpack and into
core for 8.4. Not sure if I'll manage, but I'm certainly going to try.
Having to install contrib modules to be able to read your logfiles (just
one of several examples) is another thing that most users I've come in
contact with hate.
//Magnus
* Magnus Hagander <magnus@hagander.net> [080219 10:28]:
But if you *need* a way to "set permanent", couldn't you do with with
the following KISS idea?Any "set permanent" settings should be *appended* to the main config
file, preferably with a comment line, like:
# Set by user <USER> from client <CLIENT> on <TIMESTAMP>
some_guc option = some_valueAre you suggesting we keep appending? So if I set the same parameter 100
times, it would show up on 100 rows?
In my opinion, absolutely. It's easy, safe, and the "overhead"
associated with it is minimal, and not in any critical path "work" path.
Add to that the fact that the admin can easily clean up the file any
time he wants too.
This does 2 things:
1) Eliminates a need for a fancy config parser/editor/rewriter
2) Makes it very easy to implement
3) Even allows adminpack to have a "set_permanent" function that could do it
all internally, and not actually need support in the backend core.I should warn you that it's on my radar to try to find the compromises
necessary to merge the required functionality away from adminpack and into
core for 8.4. Not sure if I'll manage, but I'm certainly going to try.
Having to install contrib modules to be able to read your logfiles (just
one of several examples) is another thing that most users I've come in
contact with hate.
Sure, but the "append via an adminpack function" has the added benifit
that it can easily be "backported" as a contrib module to the previous
versions that pgadmin supports as well.
a.
--
Aidan Van Dyk Create like a god,
aidan@highrise.ca command like a king,
http://www.highrise.ca/ work like a slave.
Are you suggesting we keep appending? So if I set the same parameter 100
times, it would show up on 100 rows?
What about not touching the config file at all, but write to a separate
file which is completely under the control of postgres and include that
at the end of the config file ? You just said includes are a new feature
which could complicate things, so why not use it actually in your
advantage ;-)
That way disabling the overrides would be as simple as commenting out
the inclusion of the postgres controlled config file. And it would
separate the user writable and machine writable configuration...
Cheers,
Csaba.
On Tue, Feb 19, 2008 at 10:34:26AM -0500, Aidan Van Dyk wrote:
* Magnus Hagander <magnus@hagander.net> [080219 10:28]:
But if you *need* a way to "set permanent", couldn't you do with with
the following KISS idea?Any "set permanent" settings should be *appended* to the main config
file, preferably with a comment line, like:
# Set by user <USER> from client <CLIENT> on <TIMESTAMP>
some_guc option = some_valueAre you suggesting we keep appending? So if I set the same parameter 100
times, it would show up on 100 rows?In my opinion, absolutely. It's easy, safe, and the "overhead"
associated with it is minimal, and not in any critical path "work" path.
Add to that the fact that the admin can easily clean up the file any
time he wants too.
I think that's entirely unworkable. While I absolutelyi don't want to break
things for people who use the config file as the primary interface (heck,
*I* am one of those people), it has to be usable for the case it's trying
to fix. And this really wouldn't be.
This does 2 things:
1) Eliminates a need for a fancy config parser/editor/rewriter
2) Makes it very easy to implement
3) Even allows adminpack to have a "set_permanent" function that could do it
all internally, and not actually need support in the backend core.I should warn you that it's on my radar to try to find the compromises
necessary to merge the required functionality away from adminpack and into
core for 8.4. Not sure if I'll manage, but I'm certainly going to try.
Having to install contrib modules to be able to read your logfiles (just
one of several examples) is another thing that most users I've come in
contact with hate.Sure, but the "append via an adminpack function" has the added benifit
that it can easily be "backported" as a contrib module to the previous
versions that pgadmin supports as well.
I should clearify that I'm not at all against implementing it as a
function - that may well be a better way. And it would be backportable. I'm
only against the "stick it in adminpack because some people don't want it"
part. It should be made good enough that it's not disruptive for those that
don't want it, and good enough to be in core.
//Magnus
On Tue, Feb 19, 2008 at 04:38:16PM +0100, Csaba Nagy wrote:
Are you suggesting we keep appending? So if I set the same parameter 100
times, it would show up on 100 rows?What about not touching the config file at all, but write to a separate
file which is completely under the control of postgres and include that
at the end of the config file ? You just said includes are a new feature
which could complicate things, so why not use it actually in your
advantage ;-)That way disabling the overrides would be as simple as commenting out
the inclusion of the postgres controlled config file. And it would
separate the user writable and machine writable configuration...
Yeah, that may actually be a very good way to implement it. I don't like
the idea of continously appending to an existing file, but if we did have a
separate file with a tightly controlled format that would be doable.
The end result wouldn't be "as clean" as some would expect, but it would
certainly be easier code-wise. For example, I'm sure someone would get the
suggestion to go edit postgresql.conf to change a config value, and be
surprised when it didn't show up as a changed setting because it was
overridden from another file..
//Magnus
Magnus Hagander wrote:
What I'd really like to see is something like a new keyword on the SET
command, so you could to SET PERMANENT foo=bar, which would write the
configuration back into postgresql.conf.I don't have a complete solution for how to actually implement it, so I'm
just throwing out some ideas for comment.
Not sure if it's of interest, but you might want to look at the postfix
mailserver configuration setup and see if that translates to an API.
postconf
lists the configuration settings (in alphabetical order)
postconf -n
list non-default settings
postconf <setting>
display "setting = value"
postconf -e <setting> = <value>
edit the configuration file, changing that setting
The editing option replaces any existing version of that setting and
adds the new value at the end of the file.
Having all the values at the end of the file works well, because for a
simple setup you don't need to change many settings and they don't
depend on order.
--
Richard Huxton
Archonet Ltd
On Tue, 2008-02-19 at 16:41 +0100, Magnus Hagander wrote:
The end result wouldn't be "as clean" as some would expect, but it would
certainly be easier code-wise. For example, I'm sure someone would get the
suggestion to go edit postgresql.conf to change a config value, and be
surprised when it didn't show up as a changed setting because it was
overridden from another file..
Yes, but at least the override part would be nicely separated in a file,
and could suggestively be named as something like
postgresql.conf.override, and hopefully will stick out sufficiently for
those who edit the config file directly to wonder about it's purpose...
and of course always editable directly too, so you can easily manually
fix foot-shooting mistakes made from the admin interface. It would be
just simply rewritten each time you change something without regard to
the manual changes, and possibly ignored altogether if your manual
changes violate it's expected layout.
Cheers,
Csaba.
On Tue, Feb 19, 2008 at 03:53:11PM +0000, Richard Huxton wrote:
Magnus Hagander wrote:
What I'd really like to see is something like a new keyword on the SET
command, so you could to SET PERMANENT foo=bar, which would write the
configuration back into postgresql.conf.I don't have a complete solution for how to actually implement it, so I'm
just throwing out some ideas for comment.Not sure if it's of interest, but you might want to look at the postfix
mailserver configuration setup and see if that translates to an API.postconf
lists the configuration settings (in alphabetical order)
SELECT * FROM pg_settings
postconf -n
list non-default settings
SELECT * FROM pg_settings WHERE NOT source='default'
postconf <setting>
display "setting = value"
SHOW log_destination
postconf -e <setting> = <value>
edit the configuration file, changing that setting
That's the one remaining :-)
The editing option replaces any existing version of that setting and
adds the new value at the end of the file.
Eh, it cannot both replace it, and add it at the end of the file, can it?
Does it replace it in-line, or does it remove the in-line entry and put the
new one at the end? Or are you saying it edits in-line entries and appends
new ones at the end?
Having all the values at the end of the file works well, because for a
simple setup you don't need to change many settings and they don't
depend on order.
Right. I don't think we have any settings that depend on order, do we?
//Magnus
Magnus Hagander wrote:
On Tue, Feb 19, 2008 at 03:53:11PM +0000, Richard Huxton wrote:
Magnus Hagander wrote:
What I'd really like to see is something like a new keyword on the SET
command, so you could to SET PERMANENT foo=bar, which would write the
configuration back into postgresql.conf.I don't have a complete solution for how to actually implement it, so I'm
just throwing out some ideas for comment.Not sure if it's of interest, but you might want to look at the postfix
mailserver configuration setup and see if that translates to an API.postconf
lists the configuration settings (in alphabetical order)SELECT * FROM pg_settings
postconf -n
list non-default settingsSELECT * FROM pg_settings WHERE NOT source='default'
postconf <setting>
display "setting = value"SHOW log_destination
postconf -e <setting> = <value>
edit the configuration file, changing that settingThat's the one remaining :-)
The editing option replaces any existing version of that setting and
adds the new value at the end of the file.Eh, it cannot both replace it, and add it at the end of the file, can it?
Does it replace it in-line, or does it remove the in-line entry and put the
new one at the end? Or are you saying it edits in-line entries and appends
new ones at the end?
Sorry,
- Edits existing lines.
- Adds new ones to end of file.
- Leaves blank lines, comments etc. alone
Having all the values at the end of the file works well, because for a
simple setup you don't need to change many settings and they don't
depend on order.Right. I don't think we have any settings that depend on order, do we?
That's what I was trying to think of - nothing came to mind.
--
Richard Huxton
Archonet Ltd
* Magnus Hagander <magnus@hagander.net> [080219 10:39]:
On Tue, Feb 19, 2008 at 10:34:26AM -0500, Aidan Van Dyk wrote:
Are you suggesting we keep appending? So if I set the same parameter 100
times, it would show up on 100 rows?In my opinion, absolutely. It's easy, safe, and the "overhead"
associated with it is minimal, and not in any critical path "work" path.
Add to that the fact that the admin can easily clean up the file any
time he wants too.I think that's entirely unworkable. While I absolutelyi don't want to break
things for people who use the config file as the primary interface (heck,
*I* am one of those people), it has to be usable for the case it's trying
to fix. And this really wouldn't be.
Can you explain why this wouldn't be usable?
I see the following propeties:
*) KISS
*) Easily "function-able"
*) 0 cost on the server writing "new/changed" GUC settings (open/seek/write/close)
*) 0 cost on setting "permanent" settings via commands
*) 0 cost on PostgreSQL config code infrastructure
*) 0 cost on "running" database
*) minimal cost on "reading" config file (a few more lines)
This seems to be usable for everything the case it's trying to fix
wants:
*) simple, and guarenteed to work, not loosing any existing config file syntax
*) not hard to maintain/backport
*) not expensive to a running database cluster
*) "permanent" settings are saved/reloaded correctly
I don't see anything that would make this unusable for the purpose of
having the server be able to "permanently" save GUC settings.
A user using this interface isn't going to care if a file is 1 line,
or 100 lines, and whether the config file parsing (on startup or reload)
takes 13.34ms or 13.69ms.
a.
--
Aidan Van Dyk Create like a god,
aidan@highrise.ca command like a king,
http://www.highrise.ca/ work like a slave.
* Csaba Nagy <nagy@ecircle-ag.com> [080219 10:59]:
Are you suggesting we keep appending? So if I set the same parameter 100
times, it would show up on 100 rows?What about not touching the config file at all, but write to a separate
file which is completely under the control of postgres and include that
at the end of the config file ? You just said includes are a new feature
which could complicate things, so why not use it actually in your
advantage ;-)That way disabling the overrides would be as simple as commenting out
the inclusion of the postgres controlled config file. And it would
separate the user writable and machine writable configuration...
Yes, I think that would be necessary (like I said), because in most
installations, I don't even thing the postgres user even has write
access to the main config file.
a.
--
Aidan Van Dyk Create like a god,
aidan@highrise.ca command like a king,
http://www.highrise.ca/ work like a slave.
Aidan Van Dyk wrote:
Any "set permanent" settings should be *appended* to the main config
file, preferably with a comment line, like:
# Set by user <USER> from client <CLIENT> on <TIMESTAMP>
some_guc option = some_valueAre you suggesting we keep appending? So if I set the same parameter 100
times, it would show up on 100 rows?In my opinion, absolutely. It's easy, safe, and the "overhead"
associated with it is minimal, and not in any critical path "work" path.
Add to that the fact that the admin can easily clean up the file any
time he wants too.
I think this is quite unacceptable and ugly. Creating an ever-growing
file that the admin would have to clean up by hand is horrid.
ISTM that this whole area is likely to be difficult unless we move to a
more structured config file (JSON, anyone?)
cheers
andrew
Richard Huxton wrote:
Magnus Hagander wrote:
Right. I don't think we have any settings that depend on order, do we?
That's what I was trying to think of - nothing came to mind.
custom_variable_classes and dependents?
cheers
andrew
On Tue, Feb 19, 2008 at 11:11:05AM -0500, Aidan Van Dyk wrote:
* Csaba Nagy <nagy@ecircle-ag.com> [080219 10:59]:
Are you suggesting we keep appending? So if I set the same parameter 100
times, it would show up on 100 rows?What about not touching the config file at all, but write to a separate
file which is completely under the control of postgres and include that
at the end of the config file ? You just said includes are a new feature
which could complicate things, so why not use it actually in your
advantage ;-)That way disabling the overrides would be as simple as commenting out
the inclusion of the postgres controlled config file. And it would
separate the user writable and machine writable configuration...Yes, I think that would be necessary (like I said), because in most
installations, I don't even thing the postgres user even has write
access to the main config file.
The postgres user gets it by default whenever you run a standard initdb,
AFAIK.
//Magnus
On Tue, Feb 19, 2008 at 11:09:43AM -0500, Aidan Van Dyk wrote:
* Magnus Hagander <magnus@hagander.net> [080219 10:39]:
On Tue, Feb 19, 2008 at 10:34:26AM -0500, Aidan Van Dyk wrote:
Are you suggesting we keep appending? So if I set the same parameter 100
times, it would show up on 100 rows?In my opinion, absolutely. It's easy, safe, and the "overhead"
associated with it is minimal, and not in any critical path "work" path.
Add to that the fact that the admin can easily clean up the file any
time he wants too.I think that's entirely unworkable. While I absolutelyi don't want to break
things for people who use the config file as the primary interface (heck,
*I* am one of those people), it has to be usable for the case it's trying
to fix. And this really wouldn't be.Can you explain why this wouldn't be usable?
Because you will end up with an ever-growing file, that will be a PITA to
deal with. Consider it after 10k+ changes. (yes, I can see that happening.
You know how some people use GUIs) Or 100k. The problem does not happen at
100 lines...
I can see the solution with a single file with them all in, but it needs to
be able to overwrite them IMHO.
//Magnus
On Tue, Feb 19, 2008 at 11:14:59AM -0500, Andrew Dunstan wrote:
Richard Huxton wrote:
Magnus Hagander wrote:
Right. I don't think we have any settings that depend on order, do we?
That's what I was trying to think of - nothing came to mind.
custom_variable_classes and dependents?
I think we could easily get away with saying that you can't change
custom_variable_classes remotely through this interface. That's not
something the user generally changes, that's something that's set when you
install a new module.
//Magnus
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
What I'd really like to see is something like a new keyword on the SET
command, so you could to SET PERMANENT foo=bar, which would write the
configuration back into postgresql.conf.
FWIW, I made a Pl/PerlU function that did this at one point. At first, I
parsed the postgresql.conf file and rewrote it, but after a while
I switched to the include a writeable file idea. The downside to that was
trying to follow the path of files to figure out what a particular setting
was (e.g. "grep 'effective' postgresql.conf" no longer provided a canonical
answer), so at the end of the day I simply appended a big comment to the bottom
of the postgresql.conf file and added the settings there. Rather than
adding 100 lines for 100 changes to the same variable, the function checked
the postgresql.conf into version control[1]As long as your version control was cvs, subversion, git, or rcs. after every change. That might be
ambitious for the SET command to handle, but it would sure be a slick feature :)
If not that, it might be nice to provide a switch to allow 100 lines, with
timestamp, if desired. An optional comment from the command line would be
another nice touch:
SET PERMANENT effective_cache_size='4GB' COMMENT='Added more RAM to box'
[1]: As long as your version control was cvs, subversion, git, or rcs.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200802191128
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAke7BGAACgkQvJuQZxSWSsg4GQCg3nnXaRBvZqJRnFIkq+Y8sXRr
hZ4AoPVQnJEnk3lJFpNJmikuDwaqz88c
=5BwE
-----END PGP SIGNATURE-----
