Subject: Re: Can a long running procedure detect when smart shutdown is pending?

From: Pavel Stehule <[email-masked]>
Date: Sat, 6 Jul 2024 08:22:08 +0200

so 6. 7. 2024 v 8:19 odesílatel Pavel Stehule <[email-masked]>
napsal:

>
>
> so 6. 7. 2024 v 8:06 odesílatel Achilleas Mantzios <
> [email-masked]> napsal:
>
>> Στις 6/7/24 08:39, ο/η Pavel Stehule έγραψε:
>>
>>
>>
>> so 6. 7. 2024 v 1:36 odesílatel Dennis White <[email-masked]>
>> 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;
>
>
sorry - it cannot to help too

probably only one solution can be to write some extension and read some
internal state

>
>
>
>>
>>
>>
>>>
>>> Thanks
>>>
>>> On Fri, Jul 5, 2024 at 4:57 PM Achilleas Mantzios <
>>> [email-masked]> 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)
>>
>>

---

From: Achilleas Mantzios <[email-masked]>
Date: Sat, 6 Jul 2024 11:09:50 +0300

Στις 6/7/24 09:22, ο/η Pavel Stehule έγραψε:
>
>
> so 6. 7. 2024 v 8:19 odesílatel Pavel Stehule 
> <[email-masked]> napsal:
>
>
>
>     so 6. 7. 2024 v 8:06 odesílatel Achilleas Mantzios
>     <[email-masked]> napsal:
>
>>         so 6. 7. 2024 v 1:36 odesílatel Dennis White
>>         <[email-masked]> napsal:
>>
>>             Thanks for responding.
>>             This will be a stored procedure written in plpgsql that's
>>             kicked off by pg_cron.
>>         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
>
>     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
> probably 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 .

>
>
>>
>>
>>
>>             Thanks
>>
>>             On Fri, Jul 5, 2024 at 4:57 PM Achilleas Mantzios
>>             <[email-masked]> 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?
>>>
>>>                 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?
>>
>>                 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