From: Ц <pfunk@mail.ru>
To: pgsql-general@postgresql.org
Subject: Active sessions does not terminated due to statement_timeout
Date: Tue, 26 Mar 2024 17:19:14 +0300
Message-ID: <1711462754.834273243@f193.i.mail.ru> (raw)
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»?
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
Subject: Re: Active sessions does not terminated due to statement_timeout
In-Reply-To: <1711462754.834273243@f193.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