Re: Per-database and per-user GUC settings

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Getting back to propogating SIGHUP to the children, if I have issued a
> SET in my session, does a postmaster SIGHUP wipe that out,

At present, yes it does, if postgresql.conf contains a conflicting
setting. I think everyone agrees that that's wrong. Peter proposes
to fix it by disabling backend response to SIGHUP entirely. I propose
to fix it by changing the code to explicitly understand that SET
overrides postgresql.conf values, regardless of when postgresql.conf
is scanned.

> and even if
> it doesn't, what if I do a SHOW early in my session, see the setting is
> OK, then find later that is is changed, for example, the ONLY
> inheritance setting.

That's a powerful example but I think it's a red herring. Changing the
system-wide default for a semantically critical value is not something
that one would do lightly or without preparation. If your applications
assume a particular inheritance setting, they're not going to be any
less broken if they connect immediately after the postgresql.conf change
occurs than if they connect just beforehand. An app that depends on a
specific setting should SET that value, not just SHOW it ... and if it
has SET the value, then that is what it will get for the duration of
its run, under either my proposal or Peter's.

Similarly, under my proposal per-user or per-database GUC settings would
override postgresql.conf settings even after SIGHUP, because the code
would explicitly treat them as doing so. Under Peter's proposal the
only way to maintain that priority relationship is to disable backends
from responding to SIGHUP altogether.

I believe that disabling SIGHUP response in backends is throwing the
baby out with the bathwater. There are plenty of examples where you
absolutely do want backends to respond to configuration changes: think
about any of the debug-logging or statistics-gathering options. If you
are trying to crank up the debug/stats level in order to find out why
your system is misbehaving *now*, it isn't going to help you if the
existing backends steadfastly ignore your attempt to change the setting.

A possible compromise is to distinguish between options that change
application-visible semantics (like inheritance, datestyle, etc)
and those that do not (logging options, sort_mem, the planner cost
settings, etc). It would be reasonable to treat the former as
"frozen after backend start unless changed via SET". It is not
reasonable to put the same straitjacket on all system settings,
however. I believe that my proposal could easily be extended to
support such behavior. Peter's approach is all-or-nothing.

regards, tom lane