MIME-Version: 1.0
References: <CAE=rie-ZcNzzt_8bKrN-7fnnbYL-ofbne4jNurd3HjjW2j9Yaw@mail.gmail.com>
 <7b98efa8-9a3e-4012-8dd9-d980b5e40609@cloud.gatewaynet.com>
In-Reply-To: <7b98efa8-9a3e-4012-8dd9-d980b5e40609@cloud.gatewaynet.com>
From: Dennis White <dwhite@seawardmoon.com>
Date: Fri, 5 Jul 2024 19:36:07 -0400
Message-ID: <CAE=rie95=YwVnL9ix0Xwp2wfUZY7rSJ4Ji_uQEAiAdgdpOqvfQ@mail.gmail.com>
Subject: Re: Can a long running procedure detect when smart shutdown is pending?
To: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b8d303061c888704"
Archived-At: <https://www.postgresql.org/message-id/CAE%3Drie95%3DYwVnL9ix0Xwp2wfUZY7rSJ4Ji_uQEAiAdgdpOqvfQ%40mail.gmail.com>
Precedence: bulk

--000000000000b8d303061c888704
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFPM Achilleas Mantzios <
a.mantzios@cloud.gatewaynet.com> wrote:

> =CE=A3=CF=84=CE=B9=CF=82 5/7/24 21:12, =CE=BF/=CE=B7 Dennis White =CE=AD=
=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
>
> 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)
>
>

--000000000000b8d303061c888704
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Thanks for responding.</div><div>This will be a store=
d procedure written in plpgsql that&#39;s kicked off by pg_cron.</div><div>=
I wasn&#39;t sure a normal smart shutdown would stop it.</div><div><br></di=
v><div>Thanks<br></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr=
" class=3D"gmail_attr">On Fri, Jul 5, 2024 at 4:57=E2=80=AFPM Achilleas Man=
tzios &lt;<a href=3D"mailto:a.mantzios@cloud.gatewaynet.com">a.mantzios@clo=
ud.gatewaynet.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote"=
 style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex"><u></u>

 =20
   =20
 =20
  <div>
    <div>=CE=A3=CF=84=CE=B9=CF=82 5/7/24 21:12, =CE=BF/=CE=B7 Dennis White
      =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:<br>
    </div>
    <blockquote type=3D"cite">
     =20
      <div dir=3D"ltr">
        <div>My project&#39;s DB has a mutli-step stored procedure using
          Transaction Control that may take 30 minutes or more to
          complete.</div>
        <div>I am curious if there is a way to make it more smart
          shutdown friendly so it can stop between steps?</div>
        <div><br>
        </div>
        <div>We are using both PG 14 and PG 16 on Rhel 8.</div>
        <div>Pardon me if it&#39;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?</div>
      </div>
    </blockquote>
    <p>Maybe somehow checking the log for a message like : <br>
    </p>
    <p><span style=3D"font-family:monospace"><span style=3D"color:rgb(0,0,0=
);background-color:rgb(255,255,255)">received smart
          shutdown request</span></span></p>
    <p><span style=3D"font-family:monospace"><span style=3D"color:rgb(0,0,0=
);background-color:rgb(255,255,255)">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.<br>
        </span></span></p>
    <blockquote type=3D"cite">
      <div dir=3D"ltr">
        <div><br>
        </div>
        <div>Thanks,</div>
        <div>Dennis<br>
        </div>
      </div>
    </blockquote>
    <pre cols=3D"72">--=20
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt (as agents only)</pre>
  </div>

</blockquote></div>

--000000000000b8d303061c888704--