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 1sQ0UZ-007NNm-R4 for pgsql-general@arkaria.postgresql.org; Sat, 06 Jul 2024 08:10:00 +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 1sQ0UV-001fzM-UX for pgsql-general@arkaria.postgresql.org; Sat, 06 Jul 2024 08:09:56 +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 1sQ0UV-001fzC-H1 for pgsql-general@lists.postgresql.org; Sat, 06 Jul 2024 08:09:56 +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 1sQ0UT-000gg4-Rx for pgsql-general@lists.postgresql.org; Sat, 06 Jul 2024 08:09:55 +0000 Content-Type: multipart/alternative; boundary="------------HJFqM8UCs5qW0acS0vOEVYNo" Message-ID: <0a261a0b-2a45-497a-a0a2-11364b7363ed@cloud.gatewaynet.com> Date: Sat, 6 Jul 2024 11:09:50 +0300 MIME-Version: 1.0 Subject: Re: Can a long running procedure detect when smart shutdown is pending? To: Pavel Stehule Cc: dwhite@seawardmoon.com, pgsql-general@lists.postgresql.org References: <7b98efa8-9a3e-4012-8dd9-d980b5e40609@cloud.gatewaynet.com> <4d1943be-b6f5-4b9b-92fb-9bd7d9ba07e5@cloud.gatewaynet.com> 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. --------------HJFqM8UCs5qW0acS0vOEVYNo Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Στις 6/7/24 09:22, ο/η Pavel Stehule έγραψε: > > > so 6. 7. 2024 v 8:19 odesílatel Pavel Stehule > napsal: > > > > so 6. 7. 2024 v 8:06 odesílatel Achilleas Mantzios > napsal: > > Στις 6/7/24 08:39, ο/η Pavel Stehule έγραψε: >> >> >> so 6. 7. 2024 v 1:36 odesílatel Dennis White >> 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. > fast mode will have this effect. smart mode not, from my > testing. The running queries do not get interrupted in smart > shutdown. > > > ok > > but you can set timeout there. It doesn't help? > > You can create some aux postgresql connection with specific app > name, and then you can monitor if this process still live from > pg_stat_activity > > one process > > connect > set application_name to 'xxxx'; > > second process > if not exists(select * from pg_stat_activity where appname = > 'xxxx') then >   exit > end if; > > > sorry - it cannot to help too > > probably only one solution can be to write some extension and read > some internal state One idea was to have a process reading the log for " received smart shutdown request " and then send a NOTIFY shutdown_channel, 'shutdown in progress' The question I have not yet answered is how to get the asynchronous notification from within plpgsql. I mean this is possible with python or Go or Java / JDBC or some other programming language , but cannot seem to find anything in plpgsql . > > >> >> >> >> Thanks >> >> On Fri, Jul 5, 2024 at 4:57 PM Achilleas Mantzios >> wrote: >> >> Στις 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) >> > -- > Achilleas Mantzios > IT DEV - HEAD > IT DEPT > Dynacom Tankers Mgmt (as agents only) > -- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only) --------------HJFqM8UCs5qW0acS0vOEVYNo Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
Στις 6/7/24 09:22, ο/η Pavel Stehule έγραψε:


so 6. 7. 2024 v 8:19 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:


so 6. 7. 2024 v 8:06 odesílatel Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> napsal:
Στις 6/7/24 08:39, ο/η Pavel Stehule έγραψε:


so 6. 7. 2024 v 1:36 odesílatel 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.
fast mode will have this effect. smart mode not, from my testing. The running queries do not get interrupted in smart shutdown.

ok

but you can set timeout there. It doesn't help?

You can create some aux postgresql connection with specific app name, and then you can monitor if this process still live from pg_stat_activity

one process

connect
set application_name to 'xxxx';

second process
if not exists(select * from pg_stat_activity where appname = 'xxxx') then
  exit
end if;


sorry - it cannot to help too

probably only one solution can be to write some extension and read some internal state

One idea was to have a process reading the log for " received smart shutdown request " and then send a 

NOTIFY shutdown_channel, 'shutdown in progress'

The question I have not yet answered is how to get the asynchronous notification from within plpgsql. I mean this is possible with python or Go or Java / JDBC or some other programming language , but cannot seem to find anything in plpgsql .

 

 

 

Thanks

On Fri, Jul 5, 2024 at 4:57 PM Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> wrote:
Στις 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)
-- 
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt (as agents only)
-- 
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt (as agents only)
--------------HJFqM8UCs5qW0acS0vOEVYNo--