From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
cc: Dennis White <dwhite@seawardmoon.com>,
        pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Can a long running procedure detect when smart shutdown is pending?
In-reply-to: <7e8b6d1951cf55c534966eb4a346add6fa420da7.camel@cybertec.at>
References: <CAE=rie-ZcNzzt_8bKrN-7fnnbYL-ofbne4jNurd3HjjW2j9Yaw@mail.gmail.com> <7e8b6d1951cf55c534966eb4a346add6fa420da7.camel@cybertec.at>
Comments: In-reply-to Laurenz Albe <laurenz.albe@cybertec.at>
	message dated "Sat, 06 Jul 2024 07:59:26 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <471034.1720273102.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 06 Jul 2024 09:38:22 -0400
Message-ID: <471035.1720273102@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/471035.1720273102%40sss.pgh.pa.us>
Precedence: bulk

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 Con=
trol 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 requir=
e 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 da=
tabase.
> If you get an error "the database system is shutting down", there is a s=
mart
> shutdown in progress.

This'd probably work.  Ugly, but ...

			regards, tom lane