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

[Achilleas Mantzios]

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


so 6. 7. 2024 v 8:19 odesílatel Pavel Stehule 
<pavel.steh...@gmail.com> napsal:



    so 6. 7. 2024 v 8:06 odesílatel Achilleas Mantzios
    <a.mantz...@cloud.gatewaynet.com> napsal:

        Στις 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.


    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

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
            <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)

--
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt (as agents only)