MIME-Version: 1.0
References: <CAE=rie-ZcNzzt_8bKrN-7fnnbYL-ofbne4jNurd3HjjW2j9Yaw@mail.gmail.com>
 <7b98efa8-9a3e-4012-8dd9-d980b5e40609@cloud.gatewaynet.com> <CAE=rie95=YwVnL9ix0Xwp2wfUZY7rSJ4Ji_uQEAiAdgdpOqvfQ@mail.gmail.com>
In-Reply-To: <CAE=rie95=YwVnL9ix0Xwp2wfUZY7rSJ4Ji_uQEAiAdgdpOqvfQ@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Sat, 6 Jul 2024 07:39:38 +0200
Message-ID: <CAFj8pRD7WHhsUkOyxw-fCBnPTShQxOeJSTzqa5zt-nzqSU2Rwg@mail.gmail.com>
Subject: Re: Can a long running procedure detect when smart shutdown is pending?
To: dwhite@seawardmoon.com
Cc: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000005229bb061c8d9df5"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRD7WHhsUkOyxw-fCBnPTShQxOeJSTzqa5zt-nzqSU2Rwg%40mail.gmail.com>
Precedence: bulk

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

so 6. 7. 2024 v 1:36 odes=C3=ADlatel Dennis White <dwhite@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.


>
> 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 s=
o
>> 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 tha=
t
>> 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 d=
o
>> polling.
>>
>>
>> Thanks,
>> Dennis
>>
>> --
>> Achilleas Mantzios
>>  IT DEV - HEAD
>>  IT DEPT
>>  Dynacom Tankers Mgmt (as agents only)
>>
>>

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">so 6. 7. 2024 v=C2=A01:36 odes=C3=ADl=
atel Dennis White &lt;<a href=3D"mailto:dwhite@seawardmoon.com">dwhite@seaw=
ardmoon.com</a>&gt; napsal:<br></div><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex"><div dir=3D"ltr"><div>Thanks for responding.</div><div>This wil=
l be a stored 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></blockquote><div><br></div><div>shutdown try to cancel any query. The=
 plpgsql routine should be canceled without problems.<br></div><div><br></d=
iv><div>=C2=A0<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0=
px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><=
div dir=3D"ltr"><div><br></div><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 Mantzios &lt;<a href=3D"mailto:a.mantzios@cloud.g=
atewaynet.com" target=3D"_blank">a.mantzios@cloud.gatewaynet.com</a>&gt; wr=
ote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
 0.8ex;border-left:1px solid rgb(204,204,204);padding-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>
</blockquote></div></div>

--0000000000005229bb061c8d9df5--