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 1sPyor-007Ewf-Vq for pgsql-general@arkaria.postgresql.org; Sat, 06 Jul 2024 06:22:50 +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 1sPyop-000uxR-Pp for pgsql-general@arkaria.postgresql.org; Sat, 06 Jul 2024 06:22:48 +0000 Received: from makus.postgresql.org ([72.32.157.229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sPyop-000uxI-Aa for pgsql-general@lists.postgresql.org; Sat, 06 Jul 2024 06:22:48 +0000 Received: from mail-yw1-x112c.google.com ([2607:f8b0:4864:20::112c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sPyon-000eXQ-9r for pgsql-general@lists.postgresql.org; Sat, 06 Jul 2024 06:22:46 +0000 Received: by mail-yw1-x112c.google.com with SMTP id 00721157ae682-65011d9bd75so19283087b3.2 for ; Fri, 05 Jul 2024 23:22:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1720246964; x=1720851764; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=fzDBQMYdZ50krbk0ViRimM3Udr/n6k+E9MgbQ7X0/xw=; b=GNHe31a7PZfDetFBUwiDUyW+pXVgEzOBORLhY/psbC3bLH0nHJasEu891/omaAM+LP opt+DzwUFUAxDxaix5Hn1Oa9WECWj1xxTu3ZL0i3K4aTX0p3kALuCHJU9zdbq6S1JDeK NUpWs8Vde2/jMnStutE87btsgLzqKdgNjZIFcDzwzWloQP6S1//jdPcCeoKHQLUpkakB H8HvPYhes71sGl/yKl85nRzkKZkupAysQK9JIVzScse4CE4JrF3uEOSU8gI4wukK5LMl FYhJ8CmUg4YB9Gq4MCMkOUKxaB5MvphwheX4nNFdM33SG6dR53IXMIZ2ZleVPF426EJ3 5EIw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1720246964; x=1720851764; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=fzDBQMYdZ50krbk0ViRimM3Udr/n6k+E9MgbQ7X0/xw=; b=msK9encF65ZjTVG2sFDhTK/UZutq+OxCxbMiJa3tkow4m6dzKWPBPXj7h4XdHVRfVe SGz5UrfSQHMVQ46DPwHspjBwkHvWv4WR2hxrbOMsi5kdv3okgZwvPB3IlwSiUEuCw5Xx SuKqL5JgeGcnitwa05p7XMUxov2uWZ3jDhDhYlNvS0iaAZbddoVYb5pqKoioz8Vdf2ZO 2zqRYsrV/gmoHv8l2antmhXgBV6omkONOYy6jR9UJtw0L/Q2uTGqEFnGEnrRI7lzBPxG R4rdACO4q1nazSSsawTy6wWq2FYeZBbCd7/dg05t75VouIen0XNtCurLQAI+cF94B89r 8bYw== X-Forwarded-Encrypted: i=1; AJvYcCUjrdOGe90NnqJMVOm/Mzzzpqt17aZ5vAcXxSn3bmTdwuB6tT6MzeSx85MAFgomsSZfRymRwr5ZjZMC8TeU/bXnkQUSZmF3erjkXqeZJQbAIqxG X-Gm-Message-State: AOJu0YyAloNE5rnecCyYXjGbo7lbQdpjBHj4bdVLk0cXUXdVROHjCTba 6KxH8BQRExhQRNS9gSEaTK5Nil2H37Ie1B8dFbRuWdYRcukHsLjIqoj1X8xCQConoKTA3XXo+O0 3dyYVBSN4u29qmfWe1cx8X9nIhXg= X-Google-Smtp-Source: AGHT+IHKOrndxYLK+x+Ix+gv9t+E35uFEk7+98fH4p3OgnOkjXOWbdO4Gcygppff7Od9Lo2eXL8G7qBsvBiuCy7xHk4= X-Received: by 2002:a05:690c:dc6:b0:620:2cfb:7a0e with SMTP id 00721157ae682-652d7871cc1mr82429087b3.40.1720246964611; Fri, 05 Jul 2024 23:22:44 -0700 (PDT) MIME-Version: 1.0 References: <7b98efa8-9a3e-4012-8dd9-d980b5e40609@cloud.gatewaynet.com> <4d1943be-b6f5-4b9b-92fb-9bd7d9ba07e5@cloud.gatewaynet.com> In-Reply-To: From: Pavel Stehule Date: Sat, 6 Jul 2024 08:22:08 +0200 Message-ID: Subject: Re: Can a long running procedure detect when smart shutdown is pending? To: Achilleas Mantzios Cc: dwhite@seawardmoon.com, pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000040762b061c8e359c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000040762b061c8e359c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable so 6. 7. 2024 v 8:19 odes=C3=ADlatel Pavel Stehule napsal: > > > so 6. 7. 2024 v 8:06 odes=C3=ADlatel Achilleas Mantzios < > a.mantzios@cloud.gatewaynet.com> napsal: > >> =CE=A3=CF=84=CE=B9=CF=82 6/7/24 08:39, =CE=BF/=CE=B7 Pavel Stehule =CE= =AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5: >> >> >> >> so 6. 7. 2024 v 1:36 odes=C3=ADlatel 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 =3D 'xxxx') th= en > exit > end if; > > sorry - it cannot to help too probably only one solution can be to write some extension and read some internal state > > > >> >> >> >>> >>> Thanks >>> >>> On Fri, Jul 5, 2024 at 4:57=E2=80=AFPM Achilleas Mantzios < >>> a.mantzios@cloud.gatewaynet.com> wrote: >>> >>>> =CE=A3=CF=84=CE=B9=CF=82 5/7/24 21:12, =CE=BF/=CE=B7 Dennis White =CE= =AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5: >>>> >>>> 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) >> >> --00000000000040762b061c8e359c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
so 6. 7. 2024 v=C2=A08:19 odes=C3=ADl= atel Pavel Stehule <pavel.ste= hule@gmail.com> napsal:


so 6. 7. 2024 v=C2= =A08:06 odes=C3=ADlatel Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> napsal:
=20 =20 =20
=CE=A3=CF=84=CE=B9=CF=82 6/7/24 08:39, =CE=BF/=CE=B7 Pavel Stehule =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
=20


so 6. 7. 2024 v=C2=A01:36 odes=C3=ADlatel 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 se= t timeout there. It doesn't help?

You can crea= te 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 applicati= on_name to 'xxxx';

second process
if not exists(select * from pg_stat_activity where appname =3D 'xxxx&= #39;) then
=C2=A0 exit
end if;


sorry - it cannot to help too

probably only one solution can be to write some extens= ion and read some internal state
=C2=A0

=C2=A0

=C2=A0

Thanks

On Fri, Jul 5, 2024 at 4:57=E2=80=AFPM Achilleas Mantzios <a.mantzios@cloud.gatewayne= t.com> wrote:
=CE=A3=CF=84=CE=B9=CF=82 5/7/24 21:12, =CE=BF/=CE=B7= Dennis White =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
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
--=20
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt (as agents only)
--=20
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt (as agents only)
--00000000000040762b061c8e359c--