public inbox for [email protected]
help / color / mirror / Atom feedRe: Can a long running procedure detect when smart shutdown is pending?
4+ messages / 3 participants
[nested] [flat]
* Re: Can a long running procedure detect when smart shutdown is pending?
@ 2024-07-05 23:36 Dennis White <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Dennis White @ 2024-07-05 23:36 UTC (permalink / raw)
To: Achilleas Mantzios <[email protected]>; +Cc: [email protected]
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.
Thanks
On Fri, Jul 5, 2024 at 4:57 PM Achilleas Mantzios <
[email protected]> 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)
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can a long running procedure detect when smart shutdown is pending?
@ 2024-07-06 05:39 Pavel Stehule <[email protected]>
parent: Dennis White <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Pavel Stehule @ 2024-07-06 05:39 UTC (permalink / raw)
To: [email protected]; +Cc: Achilleas Mantzios <[email protected]>; [email protected]
so 6. 7. 2024 v 1:36 odesílatel Dennis White <[email protected]>
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.
>
> Thanks
>
> On Fri, Jul 5, 2024 at 4:57 PM Achilleas Mantzios <
> [email protected]> 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)
>>
>>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can a long running procedure detect when smart shutdown is pending?
@ 2024-07-06 06:06 Achilleas Mantzios <[email protected]>
parent: Pavel Stehule <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Achilleas Mantzios @ 2024-07-06 06:06 UTC (permalink / raw)
To: Pavel Stehule <[email protected]>; [email protected]; +Cc: [email protected]
Στις 6/7/24 08:39, ο/η Pavel Stehule έγραψε:
>
>
> so 6. 7. 2024 v 1:36 odesílatel Dennis White <[email protected]>
> 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.
fast mode will have this effect. smart mode not, from my testing. The
running queries do not get interrupted in smart shutdown.
>
>
>
> Thanks
>
> On Fri, Jul 5, 2024 at 4:57 PM Achilleas Mantzios
> <[email protected]> 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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can a long running procedure detect when smart shutdown is pending?
@ 2024-07-06 06:19 Pavel Stehule <[email protected]>
parent: Achilleas Mantzios <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2024-07-06 06:19 UTC (permalink / raw)
To: Achilleas Mantzios <[email protected]>; +Cc: [email protected]; [email protected]
so 6. 7. 2024 v 8:06 odesílatel Achilleas Mantzios <
[email protected]> napsal:
> Στις 6/7/24 08:39, ο/η Pavel Stehule έγραψε:
>
>
>
> so 6. 7. 2024 v 1:36 odesílatel Dennis White <[email protected]>
> 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.
>
> fast mode will have this effect. smart mode not, from my testing. The
> running queries do not get interrupted in smart shutdown.
>
ok
but 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_activity
one process
connect
set application_name to 'xxxx';
second process
if not exists(select * from pg_stat_activity where appname = 'xxxx') then
exit
end if;
>
>
>
>>
>> Thanks
>>
>> On Fri, Jul 5, 2024 at 4:57 PM Achilleas Mantzios <
>> [email protected]> 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)
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-07-06 06:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-05 23:36 Re: Can a long running procedure detect when smart shutdown is pending? Dennis White <[email protected]>
2024-07-06 05:39 ` Pavel Stehule <[email protected]>
2024-07-06 06:06 ` Achilleas Mantzios <[email protected]>
2024-07-06 06:19 ` Pavel Stehule <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox