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

Pavel Stehule <pavel.stehule@xxxxxxxxx> · Sat, 6 Jul 2024 08:19:06 +0200

so 6. 7. 2024 v 8:06 odesílatel Achilleas Mantzios <a.mantzios@xxxxxxxxxxxxxxxxxxxx> napsal:

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

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