Re: Active sessions does not terminated due to statement_timeout

On Tue, Mar 26, 2024 at 3:19 PM Ц <pfunk(at)mail(dot)ru> wrote:

> Greetings!
> I’ve faced with strange behavior when I see a lot of active sessions
> started hours ago while statement_timeout = '30min'.
> All of them are fetching from cursors.
>
> Typical session looks like:
> backend_start | 2024-03-26 14:34:20.552594+03
> xact_start | 2024-03-26 14:34:54.974628+03
> query_start | 2024-03-26 14:35:02.024133+03
> state_change | 2024-03-26 14:35:02.024134+03
> wait_event_type | Client
> wait_event | ClientWrite
> state | active
> backend_xid | 23240392
> backend_xmin | 23226474
> query | fetch all from "<unnamed portal 20>"
> backend_type | client backend
>
>
> They are accumulating up to tens by the end of the day with all negative
> impacts on performance.
> Initially I thought that clients already died but due to network issues
> database considers them to be alive. So I set tcp_keepalive GUCs to nonzero
> values. Without success.
> Then I checked connections from the app server side and found them in
> ESTABLISHED state.
> It's certainly an application fault and it should not hold cursor
> forever...but
>
> Is the any GUC parameters to fight with such «clients»?
>
>
I wonder if this might be the bug I saw in
https://www.postgresql.org/message-id/CABUevExBm_va9+iW0kgVuZbrLDUZ8VnL2wo2ig7jqqdGsy8ZKQ@mail.gmail.com
-- basically that there's some path when we're in ClientWrite that it
doesn't check for interrupts properly. I've unfortunately not had time to
dig into that one anymore.

What version of PostgreSQL and what platform are you on?

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>