Обсуждение: Can a long running procedure detect when smart shutdown is pending?
My project's DB has a mutli-step stored procedure using Transaction Control that may take 30 minutes or more to complete.I am curious if there is a way to make it more smart shutdown friendly so it can stop between steps?We are using both PG 14 and PG 16 on Rhel 8.Pardon me if it's obvious but is there a function to call or a table that could be checked after a commit within the procedure to determine a shutdown is pending?
Maybe somehow checking the log for a message like :
received smart shutdown request
Or use tail_n_mail against the log and then implement some logic using NOTIFY , ideally you want your procedure to be interrupted rather than do polling.
Thanks,Dennis
-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)
Στις 5/7/24 21:12, ο/η Dennis White έγραψε:My project's DB has a mutli-step stored procedure using Transaction Control that may take 30 minutes or more to complete.I am curious if there is a way to make it more smart shutdown friendly so it can stop between steps?We are using both PG 14 and PG 16 on Rhel 8.Pardon me if it's obvious but is there a function to call or a table that could be checked after a commit within the procedure to determine a shutdown is pending?Maybe somehow checking the log for a message like :
received smart shutdown request
Or use tail_n_mail against the log and then implement some logic using NOTIFY , ideally you want your procedure to be interrupted rather than do polling.
Thanks,Dennis-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)
Thanks for responding.This will be a stored procedure written in plpgsql that's kicked off by pg_cron.I wasn't sure a normal smart shutdown would stop it.
ThanksOn Fri, Jul 5, 2024 at 4:57 PM Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> wrote:Στις 5/7/24 21:12, ο/η Dennis White έγραψε:My project's DB has a mutli-step stored procedure using Transaction Control that may take 30 minutes or more to complete.I am curious if there is a way to make it more smart shutdown friendly so it can stop between steps?We are using both PG 14 and PG 16 on Rhel 8.Pardon me if it's obvious but is there a function to call or a table that could be checked after a commit within the procedure to determine a shutdown is pending?Maybe somehow checking the log for a message like :
received smart shutdown request
Or use tail_n_mail against the log and then implement some logic using NOTIFY , ideally you want your procedure to be interrupted rather than do polling.
Thanks,Dennis-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)
On Fri, 2024-07-05 at 14:12 -0400, Dennis White wrote: > My project's DB has a mutli-step stored procedure using Transaction Control that may take 30 minutes or more to complete. > I am curious if there is a way to make it more smart shutdown friendly so it can stop between steps? I don't think there is a direct way to do that in SQL; that would require a new system function that exposes canAcceptConnections() in SQL. What you could do is use the dblink extension to connect to the local database. If you get an error "the database system is shutting down", there is a smart shutdown in progress. Yours, Laurenz Albe
fast mode will have this effect. smart mode not, from my testing. The running queries do not get interrupted in smart shutdown.so 6. 7. 2024 v 1:36 odesílatel Dennis White <dwhite@seawardmoon.com> napsal:Thanks for responding.This will be a stored procedure written in plpgsql that's kicked off by pg_cron.I wasn't sure a normal smart shutdown would stop it.shutdown try to cancel any query. The plpgsql routine should be canceled without problems.
ThanksOn Fri, Jul 5, 2024 at 4:57 PM Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> wrote:Στις 5/7/24 21:12, ο/η Dennis White έγραψε:My project's DB has a mutli-step stored procedure using Transaction Control that may take 30 minutes or more to complete.I am curious if there is a way to make it more smart shutdown friendly so it can stop between steps?We are using both PG 14 and PG 16 on Rhel 8.Pardon me if it's obvious but is there a function to call or a table that could be checked after a commit within the procedure to determine a shutdown is pending?Maybe somehow checking the log for a message like :
received smart shutdown request
Or use tail_n_mail against the log and then implement some logic using NOTIFY , ideally you want your procedure to be interrupted rather than do polling.
Thanks,Dennis-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)
-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)
Στις 6/7/24 08:39, ο/η Pavel Stehule έγραψε:fast mode will have this effect. smart mode not, from my testing. The running queries do not get interrupted in smart shutdown.so 6. 7. 2024 v 1:36 odesílatel Dennis White <dwhite@seawardmoon.com> napsal:Thanks for responding.This will be a stored procedure written in plpgsql that's kicked off by pg_cron.I wasn't sure a normal smart shutdown would stop it.shutdown try to cancel any query. The plpgsql routine should be canceled without problems.
ThanksOn Fri, Jul 5, 2024 at 4:57 PM Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> wrote:Στις 5/7/24 21:12, ο/η Dennis White έγραψε:My project's DB has a mutli-step stored procedure using Transaction Control that may take 30 minutes or more to complete.I am curious if there is a way to make it more smart shutdown friendly so it can stop between steps?We are using both PG 14 and PG 16 on Rhel 8.Pardon me if it's obvious but is there a function to call or a table that could be checked after a commit within the procedure to determine a shutdown is pending?Maybe somehow checking the log for a message like :
received smart shutdown request
Or use tail_n_mail against the log and then implement some logic using NOTIFY , ideally you want your procedure to be interrupted rather than do polling.
Thanks,Dennis-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)
so 6. 7. 2024 v 8:06 odesílatel Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> napsal:Στις 6/7/24 08:39, ο/η Pavel Stehule έγραψε:fast mode will have this effect. smart mode not, from my testing. The running queries do not get interrupted in smart shutdown.so 6. 7. 2024 v 1:36 odesílatel Dennis White <dwhite@seawardmoon.com> napsal:Thanks for responding.This will be a stored procedure written in plpgsql that's kicked off by pg_cron.I wasn't sure a normal smart shutdown would stop it.shutdown try to cancel any query. The plpgsql routine should be canceled without problems.okbut you can set timeout there. It doesn't help?You can create some aux postgresql connection with specific app name, and then you can monitor if this process still live from pg_stat_activityone processconnectset application_name to 'xxxx';second processif not exists(select * from pg_stat_activity where appname = 'xxxx') thenexitend if;
ThanksOn Fri, Jul 5, 2024 at 4:57 PM Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> wrote:Στις 5/7/24 21:12, ο/η Dennis White έγραψε:My project's DB has a mutli-step stored procedure using Transaction Control that may take 30 minutes or more to complete.I am curious if there is a way to make it more smart shutdown friendly so it can stop between steps?We are using both PG 14 and PG 16 on Rhel 8.Pardon me if it's obvious but is there a function to call or a table that could be checked after a commit within the procedure to determine a shutdown is pending?Maybe somehow checking the log for a message like :
received smart shutdown request
Or use tail_n_mail against the log and then implement some logic using NOTIFY , ideally you want your procedure to be interrupted rather than do polling.
Thanks,Dennis-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)
so 6. 7. 2024 v 8:19 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:so 6. 7. 2024 v 8:06 odesílatel Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> napsal:Στις 6/7/24 08:39, ο/η Pavel Stehule έγραψε:fast mode will have this effect. smart mode not, from my testing. The running queries do not get interrupted in smart shutdown.so 6. 7. 2024 v 1:36 odesílatel Dennis White <dwhite@seawardmoon.com> napsal:Thanks for responding.This will be a stored procedure written in plpgsql that's kicked off by pg_cron.I wasn't sure a normal smart shutdown would stop it.shutdown try to cancel any query. The plpgsql routine should be canceled without problems.okbut you can set timeout there. It doesn't help?You can create some aux postgresql connection with specific app name, and then you can monitor if this process still live from pg_stat_activityone processconnectset application_name to 'xxxx';second processif not exists(select * from pg_stat_activity where appname = 'xxxx') thenexitend if;sorry - it cannot to help tooprobably only one solution can be to write some extension and read some internal state
One idea was to have a process reading the log for " received smart shutdown request " and then send a
NOTIFY shutdown_channel, 'shutdown in progress'
The question I have not yet answered is how to get the asynchronous notification from within plpgsql. I mean this is possible with python or Go or Java / JDBC or some other programming language , but cannot seem to find anything in plpgsql .
ThanksOn Fri, Jul 5, 2024 at 4:57 PM Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> wrote:Στις 5/7/24 21:12, ο/η Dennis White έγραψε:My project's DB has a mutli-step stored procedure using Transaction Control that may take 30 minutes or more to complete.I am curious if there is a way to make it more smart shutdown friendly so it can stop between steps?We are using both PG 14 and PG 16 on Rhel 8.Pardon me if it's obvious but is there a function to call or a table that could be checked after a commit within the procedure to determine a shutdown is pending?Maybe somehow checking the log for a message like :
received smart shutdown request
Or use tail_n_mail against the log and then implement some logic using NOTIFY , ideally you want your procedure to be interrupted rather than do polling.
Thanks,Dennis-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)
-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)
Laurenz Albe <laurenz.albe@cybertec.at> writes: > On Fri, 2024-07-05 at 14:12 -0400, Dennis White wrote: >> My project's DB has a mutli-step stored procedure using Transaction Control that may take 30 minutes or more to complete. >> I am curious if there is a way to make it more smart shutdown friendly so it can stop between steps? > I don't think there is a direct way to do that in SQL; that would require a new > system function that exposes canAcceptConnections() in SQL. It's worse than that: the state variables involved are local to the postmaster, so you wouldn't get the right answer in a backend even if the function were reachable. > What you could do is use the dblink extension to connect to the local database. > If you get an error "the database system is shutting down", there is a smart > shutdown in progress. This'd probably work. Ugly, but ... regards, tom lane
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Fri, 2024-07-05 at 14:12 -0400, Dennis White wrote:
>> My project's DB has a mutli-step stored procedure using Transaction Control that may take 30 minutes or more to complete.
>> I am curious if there is a way to make it more smart shutdown friendly so it can stop between steps?
> I don't think there is a direct way to do that in SQL; that would require a new
> system function that exposes canAcceptConnections() in SQL.
It's worse than that: the state variables involved are local to the
postmaster, so you wouldn't get the right answer in a backend even
if the function were reachable.
> What you could do is use the dblink extension to connect to the local database.
> If you get an error "the database system is shutting down", there is a smart
> shutdown in progress.
This'd probably work. Ugly, but ...
regards, tom lane