Обсуждение: what happens when you issue ALTER SERVER in a hot environment?

If you issue an  ALTER SERVER command and there are active connections with that server in use or new ones are coming in, what happens? Docs on this command say nothing regarding active processing using the server context and changes to it. So I assume it's just handled.

For example if you alter user/password, I assume that as long as prior user password is still good that actively running processes will keep going.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Monday, April 6, 2020, AC Gomez <antklc@gmail.com> wrote:
>> If you issue an  ALTER SERVER command and there are active connections
>> with that server in use or new ones are coming in, what happens? Docs on
>> this command say nothing regarding active processing using the server
>> context and changes to it. So I assume it's just handled.

> Not sure if there are exceptions but assume that nothing external will
> change your current active session’s settings out from underneath you.

Couple of comments here:

* ALTER SERVER changes nothing until "pg_ctl reload" or similar is
issued.  The same goes for manually editing the config file (which
is more or less the same thing, though on a different file).

* Once you do issue a reload, individual sessions will adopt the
new setting reasonably promptly, though not necessarily all at the
same instant (assuming it's a setting that doesn't require a
postmaster restart to take effect, in which case nothing happens).

* A session that has a higher-priority source for the setting of
a given GUC, such as a locally-issued SET, is not going to adopt
the new setting from the config file.  But it *will* adopt that
setting on-the-fly if it has no other source of the setting.
The priority rules are explained in the fine manual, IIRC.
Possibly some clarification is needed?

>> For example if you alter user/password, I assume that as long as prior
>> user password is still good that actively running processes will keep going.

> If you alter a password the old one is by definition no longer good...but
> it doesn’t matter because authentication only happens once - during login.

Right.  Also, passwords are *not* managed through the GUC (server
parameter) mechanism; what I just said about GUCs doesn't apply
to them.

            regards, tom lane

On Tue, 2020-04-07 at 00:53 -0400, Tom Lane wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Monday, April 6, 2020, AC Gomez <antklc@gmail.com> wrote:
> > > If you issue an  ALTER SERVER command and there are active connections
> > > with that server in use or new ones are coming in, what happens? Docs on
> > > this command say nothing regarding active processing using the server
> > > context and changes to it. So I assume it's just handled.
> > Not sure if there are exceptions but assume that nothing external will
> > change your current active session’s settings out from underneath you.
> 
> Couple of comments here:
> 
> * ALTER SERVER changes nothing until "pg_ctl reload" or similar is
> issued.

I think you both mixed up ALTER SYSTEM and ALTER SERVER.

The details of when exactly an ALTER SERVER will affect a query that uses
a foreign table on the server will vary depending on the implementation
of the foreign data wrapper, but typically the settings that were in effect
when the query was *planned* will be the ones used.

Typically, queries are planned right before they are executed.  Any query
that is currently executing will continue to do so, but queries planned
after the ALTER SERVER will use the new values.

Sometimes plans are cached, but all cached plans that involve the changed
server will be invalidated and re-planned after the ALTER SERVER.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com