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»?