MIME-Version: 1.0
References: <CAE=rie-ZcNzzt_8bKrN-7fnnbYL-ofbne4jNurd3HjjW2j9Yaw@mail.gmail.com>
 <7e8b6d1951cf55c534966eb4a346add6fa420da7.camel@cybertec.at> <471035.1720273102@sss.pgh.pa.us>
In-Reply-To: <471035.1720273102@sss.pgh.pa.us>
From: Dennis White <dwhite@seawardmoon.com>
Date: Sat, 6 Jul 2024 13:17:08 -0400
Message-ID: <CAE=rie9qABQffaU+ZQ2FnDtK3Sf9KaG4uruzFYR3AWCqJ=Va5g@mail.gmail.com>
Subject: Re: Can a long running procedure detect when smart shutdown is pending?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003ce714061c975a54"
Archived-At: <https://www.postgresql.org/message-id/CAE%3Drie9qABQffaU%2BZQ2FnDtK3Sf9KaG4uruzFYR3AWCqJ%3DVa5g%40mail.gmail.com>
Precedence: bulk

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

Thanks everyone. A more direct way to check via a sql function would be
better but I suppose the dblink extension method will work.

Thanks again,
Dennis

On Sat, Jul 6, 2024 at 9:38=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > On Fri, 2024-07-05 at 14:12 -0400, Dennis White wrote:
> >> 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?
>
> > I don't think there is a direct way to do that in SQL; that would
> require a new
> > system function that exposes canAcceptConnections() in SQL.
>
> It's worse than that: the state variables involved are local to the
> postmaster, so you wouldn't get the right answer in a backend even
> if the function were reachable.
>
> > What you could do is use the dblink extension to connect to the local
> database.
> > If you get an error "the database system is shutting down", there is a
> smart
> > shutdown in progress.
>
> This'd probably work.  Ugly, but ...
>
>                         regards, tom lane
>

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

<div dir=3D"ltr"><div>Thanks everyone. A more direct way to check via a sql=
 function would be better but I suppose the dblink extension method will wo=
rk.</div><div><br></div><div>Thanks again,</div><div>Dennis<br></div></div>=
<br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sat=
, Jul 6, 2024 at 9:38=E2=80=AFAM Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh=
.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex">Laurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cy=
bertec.at" target=3D"_blank">laurenz.albe@cybertec.at</a>&gt; writes:<br>
&gt; On Fri, 2024-07-05 at 14:12 -0400, Dennis White wrote:<br>
&gt;&gt; My project&#39;s DB has a mutli-step stored procedure using Transa=
ction Control that may take 30 minutes or more to complete.<br>
&gt;&gt; I am curious if there is a way to make it more smart shutdown frie=
ndly so it can stop between steps?<br>
<br>
&gt; I don&#39;t think there is a direct way to do that in SQL; that would =
require a new<br>
&gt; system function that exposes canAcceptConnections() in SQL.<br>
<br>
It&#39;s worse than that: the state variables involved are local to the<br>
postmaster, so you wouldn&#39;t get the right answer in a backend even<br>
if the function were reachable.<br>
<br>
&gt; What you could do is use the dblink extension to connect to the local =
database.<br>
&gt; If you get an error &quot;the database system is shutting down&quot;, =
there is a smart<br>
&gt; shutdown in progress.<br>
<br>
This&#39;d probably work.=C2=A0 Ugly, but ...<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>

--0000000000003ce714061c975a54--