From: Ц <pfunk@mail.ru>
To: Magnus Hagander <magnus@hagander.net>
Cc: pgsql-general@postgresql.org
Subject: Re: Active sessions does not terminated due to statement_timeout
Date: Wed, 27 Mar 2024 08:46:00 +0300
Message-ID: <1711518360.513702077@f330.i.mail.ru> (raw)
In-Reply-To: <CABUevEx0LbynTLU1z3yEsG8rCQnKqDMNjtHXvBCHXicZsb9_Jg@mail.gmail.com>
References: <1711462754.834273243@f193.i.mail.ru>
<CABUevEx0LbynTLU1z3yEsG8rCQnKqDMNjtHXvBCHXicZsb9_Jg@mail.gmail.com>
Magnus,
PostgreSQL 14.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
PRETTY_NAME="Debian GNU/Linux 11 (bullseye)"
It is docker container if it matters and yep, possibly slow and unreliable network also is an issue.
So you're assuming the statement_timeout parameter should have worked fo that and this is a bug?
>Вторник, 26 марта 2024, 17:43 +03:00 от Magnus Hagander <magnus@hagander.net>:
>
>
>On Tue, Mar 26, 2024 at 3:19PM Ц < pfunk@mail.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... -- 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/
> Work: https://www.redpill-linpro.com/
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: pgsql-general@postgresql.org
Cc: pfunk@mail.ru, magnus@hagander.net
Subject: Re: Re: Active sessions does not terminated due to statement_timeout
In-Reply-To: <1711518360.513702077@f330.i.mail.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox