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

[Achilleas Mantzios]

Στις 6/7/24 08:39, ο/η Pavel Stehule έγραψε:


so 6. 7. 2024 v 1:36 odesílatel Dennis White <dwh...@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.
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
    <a.mantz...@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)