Connections leak from a Psycopg connection pool

Question

Connections leak with resulting timeout error at the connection get. I am using connection pool configured as follows:

from psycopg_pool import ConnectionPool

pool = ConnectionPool(conninfo=config['postgres']['url'])

@atexit.register
def pool_close():
    pool.close()

Then I use the pool's connections to query like this:

def load_smth(...):
    with pool.getconn() as conn:
        with conn.cursor() as cur:
           cur.execute("select cfg from ...",(..))
           return smth

The problem is that at 5th request the connection pool doesn't return a connection and my app fails with a connection pool timeout error:

psycopg_pool.PoolTimeout: couldn't get a connection after 30.0 sec

SQL queries are OK, tested with the PostgreSQL server. Moreover, if I reorder queries, failed query works. So it's a problem of getting a connection from the pool.

upd

After some research I tried to use a wrapper to commit after each call, in the following way:

def run_cursor(sql, params=(), callback=lambda x: x):
    with pool.getconn() as conn:
        with conn.cursor() as cur:
           res = callback(cur.execute(sql, params))
           conn.commit()
           cur.close()
           return res

But connections are still leaking out from the pool.

I changed the tag `from psycopg_pool import ConnectionPool` is from `psycopg(3)` not `psycopg2`. — Adrian Klaver, May 25 '23 at 22:16
From docs [ConnectionPool](https://www.psycopg.org/psycopg3/docs/api/pool.html#psycopg_pool.ConnectionPool): The default for `min_size=4` and is `max_size=None`. Then further on: *max_size (int, default: None) – The maximum number of connections the pool will hold. If None, or equal to min_size, the pool will not grow or shrink.*. So you are getting to the max(4) connections and then failing. You will need to tweak either the `min_size` or `max_size`. — Adrian Klaver, May 25 '23 at 22:22
FYI, you can use `pool.resize(min_size, max_size=None)` to change the pool size on the fly. — Adrian Klaver, May 25 '23 at 22:29
Thank you very much for your comments. The problem is that connections seem not returned to the pool after a simple select (not change!). I studied materials and docs, tried to add commit in the way I stated in the udated question, but still no success — Dmitry, May 26 '23 at 09:53
I was not paying enough attention. Since you not using the pool as context manager it is your responsibility to return the connections to the pool. You need to add a `pool.putconn()` after the `cur.execute("select cfg from ...",(..))` in the first example and the `cur.close()` in the second. If you are doing just a `SELECT`(that is not doing something with side effects like calling a function) you don't need the `commit()`. I would suggest using the pool as a context manager as shown here [pool](https://www.psycopg.org/psycopg3/docs/api/pool.html). — Adrian Klaver, May 26 '23 at 15:18
Could you please explain how to use it as a context manager? — Dmitry, May 26 '23 at 15:22
It s is shown in link I posted as update to my comment. Short version `with pool.connection() as con: ...` for automatically returning a connection. For pool as whole `with ConnectionPool(...) as pool:` — Adrian Klaver, May 26 '23 at 15:25

Connections leak from a Psycopg connection pool

0 Answers0