Обсуждение: 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.
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.
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.
David J.
"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
Thank you for clarifying. Don't you think this is pertinent information that should be in the ALTER SERVER doc page?
On Tue, Apr 7, 2020, 2:59 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
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