Thread: another idea for changing global configuration settings from SQL
Independent of the discussion of how to edit configuration files from SQL, I had another idea how many of the use cases for this could be handled. We already have the ability to store in pg_db_role_setting configuration settings for specific user, specific database specific user, any database any user, specific database The existing infrastructure would also support any user, any database (= all the time) All you'd need is to add ApplySetting(InvalidOid, InvalidOid, relsetting, PGC_S_$SOMETHING); in postinit.c, and have some SQL command to modify this setting. The only thing you couldn't handle that way are SIGHUP settings, but the often-cited use cases work_mem, logging, etc. would work. There would also be the advantage that pg_dumpall would save these settings. Thoughts?
On Thu, Nov 15, 2012 at 12:53 PM, Peter Eisentraut <peter_e@gmx.net> wrote: > Independent of the discussion of how to edit configuration files from > SQL, I had another idea how many of the use cases for this could be handled. > > We already have the ability to store in pg_db_role_setting configuration > settings for > > specific user, specific database > specific user, any database > any user, specific database > > The existing infrastructure would also support > > any user, any database (= all the time) > > All you'd need is to add > > ApplySetting(InvalidOid, InvalidOid, relsetting, PGC_S_$SOMETHING); > > in postinit.c, and have some SQL command to modify this setting. > > The only thing you couldn't handle that way are SIGHUP settings, but the > often-cited use cases work_mem, logging, etc. would work. > > There would also be the advantage that pg_dumpall would save these settings. > > Thoughts? Personally, I think that would be wonderful. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Le jeudi 15 novembre 2012 18:53:15, Peter Eisentraut a écrit : > Independent of the discussion of how to edit configuration files from > SQL, I had another idea how many of the use cases for this could be > handled. > > We already have the ability to store in pg_db_role_setting configuration > settings for > > specific user, specific database > specific user, any database > any user, specific database > > The existing infrastructure would also support > > any user, any database (= all the time) > > All you'd need is to add > > ApplySetting(InvalidOid, InvalidOid, relsetting, PGC_S_$SOMETHING); > > in postinit.c, and have some SQL command to modify this setting. > > The only thing you couldn't handle that way are SIGHUP settings, but the > often-cited use cases work_mem, logging, etc. would work. > > There would also be the advantage that pg_dumpall would save these > settings. > > Thoughts? I like the idea. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
> ApplySetting(InvalidOid, InvalidOid, relsetting, PGC_S_$SOMETHING); > > in postinit.c, and have some SQL command to modify this setting. > > The only thing you couldn't handle that way are SIGHUP settings, but the > often-cited use cases work_mem, logging, etc. would work. > > There would also be the advantage that pg_dumpall would save these settings. I think this is a great idea. One caveat: we really, really, really need a system view which allows DBAs to easily review settings defined for specific users and databases.Right now, it requires significant pg_catalog hackingexpertise to pull out user-specific settings. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Peter Eisentraut <peter_e@gmx.net> writes: > The existing infrastructure would also support > any user, any database (= all the time) > > There would also be the advantage that pg_dumpall would save these settings. > > Thoughts? That's brilliant. +1. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Thu, Nov 15, 2012 at 6:53 PM, Peter Eisentraut <peter_e@gmx.net> wrote: > Independent of the discussion of how to edit configuration files from > SQL, I had another idea how many of the use cases for this could be handled. > > We already have the ability to store in pg_db_role_setting configuration > settings for > > specific user, specific database > specific user, any database > any user, specific database > > The existing infrastructure would also support > > any user, any database (= all the time) > > All you'd need is to add > > ApplySetting(InvalidOid, InvalidOid, relsetting, PGC_S_$SOMETHING); > > in postinit.c, and have some SQL command to modify this setting. > The only thing you couldn't handle that way are SIGHUP settings, but the > often-cited use cases work_mem, logging, etc. would work. How hard would it be to make it work for SIGHUP? I can see how it would be impossible to handle things like POSTMASTER, but SIGHUP seems like it should be doable somehow? > There would also be the advantage that pg_dumpall would save these settings. > > Thoughts? I like it. Not as a replacement for the other facility, but as another way of doing it. And I'd expect it could be the "main way" for manual changes, but tools would still need access to the other way of course. We probably need to enhance pg_settings to tell the user *where* the setting came from whe nit's set this way. In fact, we need this already, since it can be hard to track down... --Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes:
> On Thu, Nov 15, 2012 at 6:53 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
>> The only thing you couldn't handle that way are SIGHUP settings, but the
>> often-cited use cases work_mem, logging, etc. would work.
> How hard would it be to make it work for SIGHUP?
One issue is that pg_db_role_setting is currently considered only at
session start, and unless you want to hack that somehow, these new
settings would only be absorbed by freshly-started sessions.
Now, there's already a good deal of asynchrony in when individual
processes notice postgresql.conf updates, whether they're for SIGHUP
or lesser settings. So maybe that's all right. If you weren't happy
about it, one of several things you'd have to work out is how to send a
SIGHUP only after you've committed the changes.
Another and probably bigger thing is that SIGHUP is used for settings
that do something useful only in background processes (eg checkpointer).
Some of those processes are not capable of reading system catalogs at
all. This is particularly a showstopper for settings affecting the
postmaster itself, which is most certainly *not* going to grow the
ability to read catalogs.
On the whole I suspect the existing push towards rewritable config file
entries is going to go further in less time for anything whose effects
aren't limited to regular backend sessions. I don't object to Peter's
idea as such, but it's not going to help us for SIGHUP settings.
regards, tom lane
On 11/16/2012 02:38 AM, Josh Berkus wrote:
>> ApplySetting(InvalidOid, InvalidOid, relsetting, PGC_S_$SOMETHING);
>>
>> in postinit.c, and have some SQL command to modify this setting.
>>
>> The only thing you couldn't handle that way are SIGHUP settings, but the
>> often-cited use cases work_mem, logging, etc. would work.
>>
>> There would also be the advantage that pg_dumpall would save these settings.
> I think this is a great idea.
>
> One caveat: we really, really, really need a system view which allows
> DBAs to easily review settings defined for specific users and databases.
> Right now, it requires significant pg_catalog hacking expertise to pull
> out user-specific settings.
A system information function like settings_for_user('username') would
certainly be welcome, showing:
setting_name setting_value setting_origin
-- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 11/15/2012 11:38 PM, Tom Lane wrote: > Magnus Hagander <magnus@hagander.net> writes: >> On Thu, Nov 15, 2012 at 6:53 PM, Peter Eisentraut <peter_e@gmx.net> wrote: >>> The only thing you couldn't handle that way are SIGHUP settings, but the >>> often-cited use cases work_mem, logging, etc. would work. >> How hard would it be to make it work for SIGHUP? > One issue is that pg_db_role_setting is currently considered only at > session start, and unless you want to hack that somehow, these new > settings would only be absorbed by freshly-started sessions. > > Now, there's already a good deal of asynchrony in when individual > processes notice postgresql.conf updates, whether they're for SIGHUP > or lesser settings. So maybe that's all right. If you weren't happy > about it, one of several things you'd have to work out is how to send a > SIGHUP only after you've committed the changes. > > Another and probably bigger thing is that SIGHUP is used for settings > that do something useful only in background processes (eg checkpointer). > Some of those processes are not capable of reading system catalogs at > all. This is particularly a showstopper for settings affecting the > postmaster itself, which is most certainly *not* going to grow the > ability to read catalogs. > > On the whole I suspect the existing push towards rewritable config file > entries is going to go further in less time for anything whose effects > aren't limited to regular backend sessions. I don't object to Peter's > idea as such, but it's not going to help us for SIGHUP settings. > > regards, tom lane > Why not just make the sending SIGHUP a separate command as it is now ? SELECT pg_reload_config(); Hannu
On 16-11-2012 12:27, Hannu Krosing wrote: > Why not just make the sending SIGHUP a separate command as it is now ? > > SELECT pg_reload_config(); > ... or even a RELOAD command. I've already coded a WIP patch for such command. -- Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Attachment
On 11/15/12 12:53 PM, Peter Eisentraut wrote: > All you'd need is to add > > ApplySetting(InvalidOid, InvalidOid, relsetting, PGC_S_$SOMETHING); > > in postinit.c, and have some SQL command to modify this setting. Alright, any suggestions for the syntax? We currently have ALTER DATABASE ... SET ... ALTER ROLE ... SET ... ALTER ROLE ... IN DATABASE ... SET I was thinking something like ALTER ROLE ANY SET ... in order to avoid creating a new top-level command, but it's not pretty. Another way might be something like SET GLOBAL name = value but that would make the command very dissimilar from the other ones, even though their effects are closely related.
On 16-11-2012 12:59, Peter Eisentraut wrote: > Another way might be something like > > SET GLOBAL name = value > That's the exact syntax I'm about to propose for this feature (changing settings using SQL). Are you thinking about allowing changing all configuration settings or just a subset of it? As said by others, using pg_db_role_setting only works for sighup, superuser, and user context. How would you solve the backend and postmaster context? -- Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte24x7 e Treinamento
Peter Eisentraut <peter_e@gmx.net> writes:
> Another way might be something like
> SET GLOBAL name = value
> but that would make the command very dissimilar from the other ones,
> even though their effects are closely related.
Yeah. I think it would also give people a wrong impression about when
the setting would take effect, because existing variants of SET are
immediate (for some value of immediate). And it would invite confusion
with the write-the-config-file patch, which is going to end up using
some syntax much like this one. I think we really want to use ALTER,
though I agree none of the alternatives are great.
Have you considered ALTER SYSTEM SET ... ? We'd talked about that in
the context of the other patch, but it seems to fit much more naturally
with this one. Or maybe ALTER GLOBAL SET or ALTER ALL SET.
regards, tom lane
On Thu, Nov 15, 2012 at 5:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Another and probably bigger thing is that SIGHUP is used for settings > that do something useful only in background processes (eg checkpointer). > Some of those processes are not capable of reading system catalogs at > all. This is particularly a showstopper for settings affecting the > postmaster itself, which is most certainly *not* going to grow the > ability to read catalogs. This seems like a pretty large strike against this whole idea. In fact, I think we might want to abandon this whole approach on this basis. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 11/16/2012 06:05 PM, Robert Haas wrote: > On Thu, Nov 15, 2012 at 5:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Another and probably bigger thing is that SIGHUP is used for settings >> that do something useful only in background processes (eg checkpointer). >> Some of those processes are not capable of reading system catalogs at >> all. This is particularly a showstopper for settings affecting the >> postmaster itself, which is most certainly *not* going to grow the >> ability to read catalogs. > This seems like a pretty large strike against this whole idea. In > fact, I think we might want to abandon this whole approach on this > basis. Can't we keep a separate text .conf file specifically for the background processes which can't read system catalogs. It could contain only the GUCs these processes are interested in. This file can be written out via a OnCommit hook which unhooks itself when the work is done. This approach should guarantee that the latest committed state is always in the text file. Hannu
Hannu Krosing <hannu@krosing.net> writes:
> Can't we keep a separate text .conf file specifically for the background
> processes which can't read system catalogs. It could contain only the
> GUCs these processes are interested in.
What's the value of that, compared to the existing proposal for
write-a-text-file-directly? It seems like useless complication.
If we could move *all* the GUCs into system catalogs, maybe it'd be
worth the trouble, but I think that's a fundamentally bad idea.
It will make it impossible to change settings when the system is down,
and thus for example impossible to fix configuration errors that
prevent the postmaster from starting. I think we should stick with
the principle that the text file is the primary authority, and that
means we don't need a system catalog entry for global settings.
A possibly instructive precedent is that we got rid of
pg_tablespace.spclocation after deciding it was counterproductive
to have a catalog entry that wasn't the authoritative state.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Have you considered ALTER SYSTEM SET ... ? We'd talked about that in > the context of the other patch, but it seems to fit much more naturally > with this one. Or maybe ALTER GLOBAL SET or ALTER ALL SET. I would paint that one ALTER SYSTEM SET and the file based one ALTER CONFIGURATION SET. No new keyword were armed in that proposal. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Saturday, November 17, 2012 3:35 AM Dimitri Fontaine wrote: > Tom Lane <tgl@sss.pgh.pa.us> writes: > > Have you considered ALTER SYSTEM SET ... ? We'd talked about that in > > the context of the other patch, but it seems to fit much more > naturally > > with this one. Or maybe ALTER GLOBAL SET or ALTER ALL SET. > > I would paint that one ALTER SYSTEM SET and the file based one ALTER > CONFIGURATION SET. No new keyword were armed in that proposal. One more could be to have built-in function pg_change_config(level,key,value) level - PG_NEW_CONNECTION - PG_SYTEM_LEVEL Level will distinguish how and when the value will be used. With Regards, Amit Kapila.
On Fri, Nov 16, 2012 at 2:53 AM, Peter Eisentraut <peter_e@gmx.net> wrote: > Independent of the discussion of how to edit configuration files from > SQL, I had another idea how many of the use cases for this could be handled. > > We already have the ability to store in pg_db_role_setting configuration > settings for > > specific user, specific database > specific user, any database > any user, specific database > > The existing infrastructure would also support > > any user, any database (= all the time) > > All you'd need is to add > > ApplySetting(InvalidOid, InvalidOid, relsetting, PGC_S_$SOMETHING); > > in postinit.c, and have some SQL command to modify this setting. > > The only thing you couldn't handle that way are SIGHUP settings, but the > often-cited use cases work_mem, logging, etc. would work. > > There would also be the advantage that pg_dumpall would save these settings. > > Thoughts? In this approach, we cannot change the settings in the standby? If yes, I don't like this approach. Regards, -- Fujii Masao
On 11/15/12 12:53 PM, Peter Eisentraut wrote: > We already have the ability to store in pg_db_role_setting configuration > settings for > > specific user, specific database > specific user, any database > any user, specific database > > The existing infrastructure would also support > > any user, any database (= all the time) > > All you'd need is to add > > ApplySetting(InvalidOid, InvalidOid, relsetting, PGC_S_$SOMETHING); > > in postinit.c, and have some SQL command to modify this setting. Here is a patch for that. The internals are straightforward. Actually, we might want to refactor this a bit later, unifying the AlterRoleSet and AlterDatabaseSet parse nodes and the functions that do the work, because it's really all the same. The SQL level interface is a bit odd. The existing facilities are ALTER ROLE / SET ALTER DATABASE / SET ALTER ROLE / IN DATABASE / SET Since the original design somehow considered roles to be superior to databases in this regard, I added the global setting as ALTER ROLE ALL SET ..., but that's obviously arbitrary. Most other variants would probably be much more invasive, though.
Attachment
Hello Peter
I am looking on your patch.
I found only one issue
in documentation is role name or keyword ALL marked as optional, but
it is mandatory
+ALTER ROLE [ <replaceable class="PARAMETER">name</replaceable> | ALL
] [ IN DATABASE <replaceable
class="PARAMETER">database_name</replaceable> ] SET
<replaceable>configuration_parameter</replaceable> FROM CURRENT
+ALTER ROLE [ <replaceable class="PARAMETER">name</replaceable> | ALL
] [ IN DATABASE <replaceable
class="PARAMETER">database_name</replaceable> ] RESET
<replaceable>configuration_parameter</replaceable>
+ALTER ROLE [ <replaceable class="PARAMETER">name</replaceable> | ALL
] [ IN DATABASE <replaceable
class="PARAMETER">database_name</replaceable> ] RESET ALL
should be
+ALTER ROLE { <replaceable class="PARAMETER">name</replaceable> | ALL
} [ IN DATABASE <replaceable
class="PARAMETER">database_name</replaceable> ] SET
<replaceable>configuration_parameter</replaceable> FROM CURRENT
+ALTER ROLE { <replaceable class="PARAMETER">name</replaceable> | ALL
} [ IN DATABASE <replaceable
class="PARAMETER">database_name</replaceable> ] RESET
<replaceable>configuration_parameter</replaceable>
+ALTER ROLE { <replaceable class="PARAMETER">name</replaceable> | ALL
} [ IN DATABASE <replaceable
class="PARAMETER">database_name</replaceable> ] RESET ALL
???
Regards
Pavel Stehule
2013/1/15 Peter Eisentraut <peter_e@gmx.net>:
> On 11/15/12 12:53 PM, Peter Eisentraut wrote:
>> We already have the ability to store in pg_db_role_setting configuration
>> settings for
>>
>> specific user, specific database
>> specific user, any database
>> any user, specific database
>>
>> The existing infrastructure would also support
>>
>> any user, any database (= all the time)
>>
>> All you'd need is to add
>>
>> ApplySetting(InvalidOid, InvalidOid, relsetting, PGC_S_$SOMETHING);
>>
>> in postinit.c, and have some SQL command to modify this setting.
>
> Here is a patch for that.
>
> The internals are straightforward. Actually, we might want to refactor
> this a bit later, unifying the AlterRoleSet and AlterDatabaseSet parse
> nodes and the functions that do the work, because it's really all the same.
>
> The SQL level interface is a bit odd. The existing facilities are
>
> ALTER ROLE / SET
> ALTER DATABASE / SET
> ALTER ROLE / IN DATABASE / SET
>
> Since the original design somehow considered roles to be superior to
> databases in this regard, I added the global setting as ALTER ROLE ALL
> SET ..., but that's obviously arbitrary. Most other variants would
> probably be much more invasive, though.
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>