Re: Per-database and per-user GUC settings

Tom Lane writes:

> One thing that's bothered me for awhile is that GUC doesn't retain
> any memory of how a variable acquired its present value. It tries to
> resolve conflicts between different sources of values just by processing
> the sources in "the right order". However this cannot work in general.
> Some examples:
>
> 1. postgresql.conf contains a setting for some variable, say
> sort_mem=1000. DBA starts postmaster with a command-line option to
> override the variable, say --sort_mem=2000. Works fine, until he
> SIGHUPs the postmaster for some unrelated reason, at which point
> sort_mem snaps back to 1000.

This sort of thing was once considered a feature, until someone came along
and overloaded SIGHUP for unreleated things. ;-)

However, possibly this could be covered if we just didn't propagate the
SIGHUP signal to the postmaster's children. For pg_hba.conf and friends,
you don't need it at all once a session is started, and for
postgresql.conf, the value of the "feature" is at least doubtful.
Intuitively, the admin would probably expect his new settings to take
effect in newly started sessions. If he wants to alter existing sessions
it's probably best to signal the backend processes explicitly.

> Another thought: DBAs will probably expect that if they change
> per-database/per-user GUC settings, they can SIGHUP to make existing
> backends take on those settings.

I must disagree with this expectation.

SIGHUP is restricted to re-reading configuations files. The
per-database/per-user settings behave, in my mind, like SET commands
executed immediately before the session loop starts. So once that session
has started, they are no longer relevant. Imagine, a user edits his own
convenience settings and the admin jumps in and edits some other unrelated
setting in the same array -- all the sudden the user's new settings get
activated.

Admins probably don't want to interfere with users' running sessions,
especially not asynchronously (from the user's point of view). If they
don't like what the user is doing, kill the session. Otherwise they may
do more harm than good. Now that I write this, not propagating the SIGHUP
is something I would really favour.

> The whole subject of reacting to errors in the per-database/per-user GUC
> settings needs more thought, too. Worst case scenario: superuser messes
> up his own per-user GUC settings to the point that he can't log in
> anymore.

Yes, that is one of my concerns too, but I don't see me rewiring the whole
exception handling in the backend because of this.

Consider, what is "messed up"? We don't have options that prevent login.
Invalid option strings are effectively ignored. If you cannot read
pg_database or pg_shadow you have more fundamental problems and you most
likely won't get to the options processing at all.

> Can we provide an escape hatch, or is he looking at an initdb
> situation (without even the chance to run pg_dump first :-()?

If the database settings are still messed up, you still have template1.
(You generally wouldn't put actual settings into template1. -- You might
as well put them into postgresql.conf then.)

If template1 is blocked or the user's settings are messed up, you have a
more fundamental problem, but it's not dissimilar to deleting all your
users. We have an escape hatch for that: Start a standalone backend.
(No options would be processed in that case.)

--
Peter Eisentraut peter_e(at)gmx(dot)net