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:26:32 -0400, Stephen Frost wrote:
> * Andres Freund (andres@2ndquadrant.com) wrote:
> > On 2013-08-29 21:26:48 -0400, Stephen Frost wrote:
> > > It's not the OPs guy that I'm worried about using ALTER SYSTEM- I don't
> > > expect them to have any clue about it or care about it, except where it
> > > can be used to modify things under /etc which they, rightfully, consider
> > > their domain.
> > 
> > I think for the scenarios you describe it makes far, far much more sense
> > to add the ability to easily monitor for two things:
> > * on-disk configuration isn't the same as the currently loaded (not
> >   trivially possible yet)
> 
> That would certainly help.  I don't know that it needs to be technically
> 'trivial', but at least having check_postgres.pl or something which can
> alert on it would be an improvement.  Clearly, that would be valuable
> today (assuming it doesn't already exist somewhere..  it might).

Technically trivial in the sense that it should be queryable from SQL
without having to write code in an untrusted PL ;).

I guess storing the file modification date along the file/location a GUC
is originating from would be good enough. Then you could write a query
using pg_stat_file() to make sure they are up2date.

> > * Configuration variables only come from locations that are approved for
> >   in your scenario (Already possible, we might want to make it even easier)
> 
> That an interesting notion; do you have something specific in mind..?
> The easiest, imv anyway, would be that options set in postgresql.conf
> can't be overridden, but that gets us into the bootstrap problem that
> people seem to be concerned about.  It would also be a change to how
> postgresql.conf is parsed today which some people would be annoyed by.
> Having some configuration option which says what can be modified by
> alter system doesn't strike me as a terribly good solution either.

I think changing the precedence of options in postgresql.conf has about
zero chance.

Currently you can do something like:
SELECT name FROM pg_settings WHERE sourcefile IS NOT NULL AND sourcefile <>
'/etc/postgresql/9.2/main/postgresql.conf';

To be easily usable we should have two new columns in pg_settings:
* reset_sourcefile
* reset_sourceline
And an easy way to get the absolute path of
* postgresql.conf
* auto.conf or whatever we name it.

That would make it possible to easily write a query that works across
intallation that warns about any values stored in auto.conf, even if
they are overwritten by a per-user config or similar.

Greetings,

Andres Freund

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