public inbox for [email protected]
help / color / mirror / Atom feedFrom: Achilleas Mantzios <[email protected]>
To: Pavel Stehule <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: Can a long running procedure detect when smart shutdown is pending?
Date: Sat, 6 Jul 2024 11:09:50 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFj8pRAJDHtZDNG86OdNWwdqMnbfS8BTpx3NUM4sLgRdzUjE0Q@mail.gmail.com>
References: <CAE=rie-ZcNzzt_8bKrN-7fnnbYL-ofbne4jNurd3HjjW2j9Yaw@mail.gmail.com>
<[email protected]>
<CAE=rie95=YwVnL9ix0Xwp2wfUZY7rSJ4Ji_uQEAiAdgdpOqvfQ@mail.gmail.com>
<CAFj8pRD7WHhsUkOyxw-fCBnPTShQxOeJSTzqa5zt-nzqSU2Rwg@mail.gmail.com>
<[email protected]>
<CAFj8pRA7w_SE0CZo2T1NTRhOZq1vL+4pdnegW32_rWEQLX-TsA@mail.gmail.com>
<CAFj8pRAJDHtZDNG86OdNWwdqMnbfS8BTpx3NUM4sLgRdzUjE0Q@mail.gmail.com>
Στις 6/7/24 09:22, ο/η Pavel Stehule έγραψε:
>
>
> so 6. 7. 2024 v 8:19 odesílatel Pavel Stehule
> <[email protected]> napsal:
>
>
>
> so 6. 7. 2024 v 8:06 odesílatel Achilleas Mantzios
> <[email protected]> napsal:
>
> Στις 6/7/24 08:39, ο/η Pavel Stehule έγραψε:
>>
>>
>> so 6. 7. 2024 v 1:36 odesílatel Dennis White
>> <[email protected]> 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
>> <[email protected]> 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)
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Can a long running procedure detect when smart shutdown is pending?
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox