Cursors persist on connections between pgcat and postgres after client application is killed

[egor-georgiev]

Versions

• postgres: 13.4
• pgcat: successfully reproduced with 1.2.0 and 1.1.1

Description

We have an application that uses postgres as a dbms and pgcat for pooling connections to it. We use both session and transaction pool modes, and we have reproduced this issue in both cases. We use server-side cursors in order to fetch query results iteratively.

When our applications opens a connection, creates a cursor and exits abnormally (or is killed), the connection between our application and pgcat gets terminated, and the connection between pgcat and postgres gets reused, but the cursor persists. When this connection is given to another worker of our application, we can see that it contains a cursor from previous worker.

This is an issue for us because with time, cursors accumulate on the connections, and memory usage of postgres grows.

How to reproduce

I have created a repository with a compose file, configs and instructions needed in order to reproduce the issue. Below is a short version:

1. configure pgcat w/ pool size of 1 (in order to always receive the same connection)
2. start postgres and pgcat
3. connect to pgcat using psql, create a cursor: declare cursor_name no scroll cursor with hold for select 1;
4. kill the psql process in another terminal: pkill psql
5. re-connect to pgcat using psql and confirm that the cursor still exists: select * from pg_cursors;

DISCARD ALL vs RESET ALL and pgcat version
I have seen in the release notes of the version 1.2.0 a merged pull request that replaces DISCARD ALL with RESET ALL. I have confirmed that DISCARD ALL does remove the hanging cursor, while RESET ALL does not. After consulting the postgresql docs, I have found that DISCARD ALL does CLOSE ALL under the hood, which RESET ALL does not seem to do. I have confirmed that CLOSE ALL does close the hanging cursors.

With this knowledge, I checked whether this issue can be reproduced on pgcat 1.1.1. Sadly, it can be reproduced, therefore downgrading does not fix the issue. In my repo with issue reproduction instructions, I have left a comment with 1.1.1 version of pgcat in compose. You can comment out the 1.2.0 and uncomment the 1.1.1 version in order to reproduce the issue on it as well.

Expected behavior
Cursors do not persist on connections between pgcat and postgres after client is killed or exits abnormally

Thanks for your help in advance and please let me know if you need any additional details, I will add them asap.
Cheers :)

[egor-georgiev]

Update: I have pushed a branch with modified configuration to my repo, specifically, I replace pgcat with pgbouncer in order to illustrate that this behavior does not occur with pgbouncer.

[egor-georgiev]

Update: I have pushed a branch with instructions on how to reproduce the same issue with pg_doorman, a fork of pgcat