Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sPpzv-006Zbm-DI for pgsql-general@arkaria.postgresql.org; Fri, 05 Jul 2024 20:57:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sPpzt-00EDeS-Cq for pgsql-general@arkaria.postgresql.org; Fri, 05 Jul 2024 20:57:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sPpzt-00EDeK-2C for pgsql-general@lists.postgresql.org; Fri, 05 Jul 2024 20:57:37 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sPpzq-000c7j-Sd for pgsql-general@lists.postgresql.org; Fri, 05 Jul 2024 20:57:37 +0000 Content-Type: multipart/alternative; boundary="------------4aDq8FnIa7sobds7thsAKufF" Message-ID: <7b98efa8-9a3e-4012-8dd9-d980b5e40609@cloud.gatewaynet.com> Date: Fri, 5 Jul 2024 23:57:31 +0300 MIME-Version: 1.0 Subject: Re: Can a long running procedure detect when smart shutdown is pending? To: pgsql-general@lists.postgresql.org References: Content-Language: en-US From: Achilleas Mantzios In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------4aDq8FnIa7sobds7thsAKufF Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Στις 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? > > 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) --------------4aDq8FnIa7sobds7thsAKufF Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
Στις 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?

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)
--------------4aDq8FnIa7sobds7thsAKufF--