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

On Thu, Aug 29, 2013 at 9:26 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> In the first place, modifying postgresql.conf and not immediately
>> restarting the server to test your changes is probably the single most
>> basic DBA error imaginable.
>
> You're not modifying postgresql.conf with ALTER SYSTEM, now are you?
> Admins are going to realize the need to restart or at least reload the
> service after updating such, but a DBA who has focused mainly on
> normalization, query optimization, etc, isn't going to have the same
> experience that a sysadmin has.

I refuse to reject any feature on the basis of the possibility that
people might use it without reading the documentation. Nearly
anything anyone will ever propose is so idiot-proof that we can't
conceive of a scenario in which someone shoots their foot off with it.
The question is whether the usefulness of the feature exceeds, by a
sufficient amount, the harm it's likely to do, and I think the answer
is clearly yes.

> A DBA updating a GUC, something they are likely to do frequently
> day-in and day-out, isn't necessairly going to consider that it's a
> reboot-needing change. Indeed, it's not unreasonable to consider that
> we may, some day, actually be able to change or increase
> shared_buffers on the fly (or error in the trying); I'd think your
> work with the dynamic backends would actually make that likely to
> happen sooner rather than later.

I wouldn't hold my breath. We have way too many separate,
variable-length data structures in shared memory. Increasing
shared_buffers means making the lwlock array bigger, making the buffer
header array bigger, allocating more space for the buffer mapping
hash, etc. I doubt we'd accept a design that involves each of those
data structures being a separate shared memory segment, but if they're
all in the same segment one after another, then you can't easily
extend them on the fly.

There are also a lot of problems around unmapping-and-remapping a
segment. If the remap fails, it's going to be at least a FATAL, but
if you had any shared memory state in the unmapped segment (like a
buffer pin) that has to be unwound, you have to PANIC; there's no
other way to fix it. My initial design for dynamic shared memory
allowed for an unbounded number of dynamic shared memory segments by
growing the control segment on the fly. However, this introduced
PANIC hazards in corner cases that I couldn't get rid of, so now
there's a fairly generous but fixed-at-startup-time limit on how many
segments you can have. In practice I don't think this matters much,
but it was a sobering reminder that the main shared memory segment,
with all of its inflexibility, has important reliability properties
that are hard to replicate in more dynamic scenarios.

> 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.

Under the currently-proposed design, it can't be used to do any such
thing. It can only be used to modify some auto.conf file which lives
in $PGDATA. It's therefore no different from the ops perspective than
ALTER DATABASE or ALTER USER - except that it allows all settings to
be changed rather than only a subset.

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