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

From: Dennis White <[email-masked]>
Date: Fri, 5 Jul 2024 14:12:27 -0400

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?

Thanks,
Dennis

---

From: Achilleas Mantzios <[email-masked]>
Date: Fri, 5 Jul 2024 23:57:31 +0300

Στις 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?
>
> 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.

-- 
Achilleas Mantzios
  IT DEV - HEAD
  IT DEPT
  Dynacom Tankers Mgmt (as agents only)

---

From: Laurenz Albe <[email-masked]>
Date: Sat, 06 Jul 2024 07:59:26 +0200

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.

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.

Yours,
Laurenz Albe

---

From: Tom Lane <[email-masked]>
Date: Sat, 06 Jul 2024 09:38:22 -0400

> 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.

> If you get an error "the database system is shutting down", there is a smart

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

			regards, tom lane

---

From: Dennis White <[email-masked]>
Date: Sat, 6 Jul 2024 13:17:08 -0400

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