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

On Wed, Oct 31, 2012 at 8:17 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> 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.

Reading over this thread, it seems that there are at least three
different proposals for how this should work in detail:

1. Have a configuration file that can be rewritten using SQL, and have
postgresql.conf include it by default.
2. Have a configuration directory that gets included in
postgresql.conf by default, and one file in that directory will
contain all the parameters set via SQL.
3. Have a configuration directory that gets included in
postgresql.conf by default, with one file per parameter, and rewrite
just that file when the corresponding parameter is set via SQL.

Also, there are at least three different proposals for what the syntax
should look like:

1. ALTER SYSTEM
2. SET PERSISENT
3. pg_frob_my_configuration()

For all of that, I think there is broad agreement that being able to
set configuration parameters on a server-wide basis via SQL commands
is a useful thing to do. I certainly think it is.

It seems to me that the only reason why we have any of this
information in a text file at all is because there are some parameters
that the server has to know before it can start. After all, ALTER
USER and ALTER DATABASE and ALTER FUNCTION store their values in the
database itself, and no one has said, oh, those values should be
stored in a file so people can edit them with a text editor. Why not?
Surely that's just as defensible as wanting to edit the server-wide
parameters, but you can't.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company