public inbox for [email protected]
help / color / mirror / Atom feedCan a long running procedure detect when smart shutdown is pending?
5+ messages / 4 participants
[nested] [flat]
* Can a long running procedure detect when smart shutdown is pending?
@ 2024-07-05 18:12 Dennis White <[email protected]>
2024-07-05 20:57 ` Re: Can a long running procedure detect when smart shutdown is pending? Achilleas Mantzios <[email protected]>
2024-07-06 05:59 ` Re: Can a long running procedure detect when smart shutdown is pending? Laurenz Albe <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: Dennis White @ 2024-07-05 18:12 UTC (permalink / raw)
To: pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Can a long running procedure detect when smart shutdown is pending?
2024-07-05 18:12 Can a long running procedure detect when smart shutdown is pending? Dennis White <[email protected]>
@ 2024-07-05 20:57 ` Achilleas Mantzios <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Achilleas Mantzios @ 2024-07-05 20:57 UTC (permalink / raw)
To: [email protected]
Στις 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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Can a long running procedure detect when smart shutdown is pending?
2024-07-05 18:12 Can a long running procedure detect when smart shutdown is pending? Dennis White <[email protected]>
@ 2024-07-06 05:59 ` Laurenz Albe <[email protected]>
2024-07-06 13:38 ` Re: Can a long running procedure detect when smart shutdown is pending? Tom Lane <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2024-07-06 05:59 UTC (permalink / raw)
To: Dennis White <[email protected]>; pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Can a long running procedure detect when smart shutdown is pending?
2024-07-05 18:12 Can a long running procedure detect when smart shutdown is pending? Dennis White <[email protected]>
2024-07-06 05:59 ` Re: Can a long running procedure detect when smart shutdown is pending? Laurenz Albe <[email protected]>
@ 2024-07-06 13:38 ` Tom Lane <[email protected]>
2024-07-06 17:17 ` Re: Can a long running procedure detect when smart shutdown is pending? Dennis White <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2024-07-06 13:38 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Dennis White <[email protected]>; pgsql-general <[email protected]>
Laurenz Albe <[email protected]> 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 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.
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 database.
> If you get an error "the database system is shutting down", there is a smart
> shutdown in progress.
This'd probably work. Ugly, but ...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Can a long running procedure detect when smart shutdown is pending?
2024-07-05 18:12 Can a long running procedure detect when smart shutdown is pending? Dennis White <[email protected]>
2024-07-06 05:59 ` Re: Can a long running procedure detect when smart shutdown is pending? Laurenz Albe <[email protected]>
2024-07-06 13:38 ` Re: Can a long running procedure detect when smart shutdown is pending? Tom Lane <[email protected]>
@ 2024-07-06 17:17 ` Dennis White <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Dennis White @ 2024-07-06 17:17 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Laurenz Albe <[email protected]>; pgsql-general <[email protected]>
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
On Sat, Jul 6, 2024 at 9:38 AM Tom Lane <[email protected]> wrote:
> Laurenz Albe <[email protected]> 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
> 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.
>
> 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
> database.
> > If you get an error "the database system is shutting down", there is a
> smart
> > shutdown in progress.
>
> This'd probably work. Ugly, but ...
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-07-06 17:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-05 18:12 Can a long running procedure detect when smart shutdown is pending? Dennis White <[email protected]>
2024-07-05 20:57 ` Achilleas Mantzios <[email protected]>
2024-07-06 05:59 ` Laurenz Albe <[email protected]>
2024-07-06 13:38 ` Tom Lane <[email protected]>
2024-07-06 17:17 ` Dennis White <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox