Re: Changing shared_buffers without restart

On Mon, Jul 7, 2025 at 6:36 PM Dmitry Dolgov <9erthalion6@gmail.com> wrote:
>
> > On Mon, Jul 07, 2025 at 01:57:42PM +0200, Tomas Vondra wrote:
> > > It could be potentialy useful for any GUC that controls a resource
> > > shared between backend, and requires restart. To make this GUC
> > > changeable online, every backend has to perform some action, and they
> > > have to coordinate to make sure things are consistent -- exactly the use
> > > case we're trying to address, shared_buffers is just happened to be one
> > > of such resources. While I agree that the currently implemented
> > > interface is wrong (e.g. it doesn't prevent pending GUCs from being
> > > stored in PG_AUTOCONF_FILENAME, this has to happen only when the new
> > > value is actually applied), it still makes sense to me to allow more
> > > flexible lifecycle for certain GUC.
> > >
> > > An example I could think of is shared_preload_libraries. If we ever want
> > > to do a hot reload of libraries, this will follow the procedure above:
> > > every backend has to do something like dlclose / dlopen and make sure
> > > that other backends have the same version of the library. Another maybe
> > > less far fetched example is max_worker_processes, which AFAICT is mostly
> > > used to control number of slots in shared memory (altough it's also
> > > stored in the control file, which makes things more complicated).
> > >
> >
> > Not sure. My concern is the config reload / GUC assign hook was not
> > designed with this use case in mind, and we'll run into issues. I also
> > dislike the "async" nature of this, which makes it harder to e.g. abort
> > the change, etc.
>
> Yes, GUC assing hook was not designed for that. That's why the idea is
> to extend the design and see if it will be good enough.
>
> > > I'm somewhat torn between those two options myself. The more I think
> > > about this topic, the more I convinced that pending GUC makes sense, but
> > > the more work I see needed to implement that. Maybe a good middle ground
> > > is to go with a simple utility command, as Ashutosh was suggesting, and
> > > keep pending GUC infrastructure on top of that as an optional patch.
> > >
> >
> > What about a simple function? Probably not as clean as a proper utility
> > command, and it implies a transaction - not sure if that could be a
> > problem for some part of this.
>
> I'm currently inclined towards this and a new one worker to coordinate
> the process, with everything else provided as an optional follow-up
> step. Will try this out unless there are any objections.

I will reply to the questions but let me summarise my  offlist
discussion with Andres.

I had proposed ALTER SYSTEM ... UPDATE ... approach in pgconf.dev for
any system wide GUC change such as this. However, Andres pointed out
that any UI proposal has to honour the current ability to edit
postgresql.conf and trigger the change in a running server. ALTER
SYSTEM ... UDPATE ... does not allow that. So, I think we have to
build something similar or on top of the current ALTER SYSTEM ... SET
+ pg_reload_conf().

My current proposal is ALTER SYSTEM ... SET + pg_reload_conf() with
pending mark + pg_apply_pending_conf(<name of GUC>, <more
parameters>). The third function would take a GUC name as parameter
and complete the pending application change. If the proposed change is
not valid, it will throw an error. If there are problems completing
the change it will throw an error and keep the pending mark intact.
Further the function can take GUC specific parameters which control
the application process. E.g. for example it could tell whether to
wait for a backend to unpin a buffer or cancel that query or kill the
backend or abort the application itself. If the operation takes too
long, a user may want to cancel the function execution just like
cancelling a query. Running two concurrent instances of the function,
both applying the same GUC won't be allowed.

Does that look good?

-- 
Best Wishes,
Ashutosh Bapat