Re: 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(at)gmx(dot)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 hacking expertise to pull
out user-specific settings.

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

Peter Eisentraut <peter_e(at)gmx(dot)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(at)gmx(dot)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 Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> On Thu, Nov 15, 2012 at 6:53 PM, Peter Eisentraut <peter_e(at)gmx(dot)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(at)hagander(dot)net> writes:
>> On Thu, Nov 15, 2012 at 6:53 PM, Peter Eisentraut <peter_e(at)gmx(dot)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

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, Suporte 24x7 e Treinamento

Peter Eisentraut <peter_e(at)gmx(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)krosing(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)gmx(dot)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.

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(at)gmx(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>