Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: Proposal for Allow postgresql.conf values to be changed via SQL [review])

On 2013-08-30 09:19:42 -0400, Stephen Frost wrote:
> * Andres Freund (andres(at)2ndquadrant(dot)com) wrote:
> > On 2013-08-30 08:48:21 -0400, Stephen Frost wrote:
> > > I really just don't buy that- I've already put forward suggestions for
> > > how to deal with it, but no one here seems to understand the
> > > distinction. Modifying listen_addresses through ALTER SYSTEM is akin to
> > > ISC/bind allowing changes to its listen_addresses equivilant through
> > > dynamic DNS updates. Would it be possible to implement? Sure. Does it
> > > make any sense? Certainly not.
> >
> > I very much want to change stuff like wal_level, listen_addresses and
> > shared_buffers via ALTER SYSTEM. Configuration variables like that
> > (PGC_POSTMASTER stuff mostly) are the prime reason why you actually need
> > to change postgresql.conf instead of changing per user/database
> > settings.
>
> wal_level and shared_buffers I can buy, but listen_addresses? The most
> typical change there is going from localhost -> '*', but you've got to
> be on the box to do that. Anything else and you're going to need to be
> adding interfaces to the box anyway and hacking around in
> /etc/network/interfaces or what-have-you.

Even if it requires to be on the box locally, I only need libpq for
it. And it's not infrequent to allow additional, already configured
interfaces. And even if not, what's the point of prohibiting
listen_interfaces specifically? When the other very interesting
variables have the same dangers?
Doing this on a variable-by-variable basis will a) be a ridiculous
amount of effort, b) confuse users which may not share our judgement of
individual variables.

> > > Because we've got crap mixed into postgresql.conf which are bootstrap
> > > configs needed to get the system started. Those things, in my view
> > > anyway, fall much more into the category of "resources which should be
> > > managed outside the database" than pg_hba.conf.
> >
> > I think the problem with your position in this thread is that you want
> > to overhaul the way our configuration works in a pretty radical
> > way. Which is fair enough, there certainly are deficiencies. But it's
> > not the topic of this thread.
>
> You and Robert both seem to be of the opinion that this hack which
> brings postgresql.conf into the database via ALTER SYSTEM is a-ok
> because it's moving us "forward" in someone's mind, but it really is
> developing a system configuration management system which *looks* like a
> first-class citizen when it actually falls woefully short of that.

It's what plenty of people want and it doesn't hurt people who do not
want it. Yes. I think that's a step forward.

> There is absolutely no question in my mind that this will be a huge
> support pain, from the first "ALTER SYSTEM SET shared_buffers = blah;
> SHOW shared_buffers;" to the "why can't my database start?!? it's
> complaining it can't allocate memory but I keep changing postgresql.conf
> and nothing works!" I'm simply not convinced that this is moving us
> forward nor that we will end up with more benefit than pain from it.

That will not show the changed shared_buffers. And it (afair) will throw
a WARNING that shared_buffers couldn't be adjusted at this point.

Greetings,

Andres Freund

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