psycopg2 close connection pool

Question

I'm developing a Flask API, and I have the following code to have a connection pool using Psycopg2. I wonder should I consider to close the connection pool when the program terminates and how should I do this?

@contextmanager
def get_cursor(:
    global connection_pool
    if not cls.connection_pool:
        cls.connection_pool = ThreadedConnectionPool(5, 25, dsn=PoolingWrap.generate_conn_string())

    con = cls.connection_pool.getconn()
    try:
        yield con.cursor(cursor_factory=RealDictCursor)
    finally:
        cls.connection_pool.putconn(con)

Answer 1

I believe that so long as you are closing the transactions in each connection correctly, then you should not have any problem just leaving the global pool. I think the worst that can happen in that case is some connections on the DB side take a short while to work out that they've been closed on the client side - but this should not be able to cause any data consistency type issues.

However, if you really want to close the connection pool before you exit, one way to do this is to register an atexit function.

import atexit

@atexit.register
def close_connection_pool():
    global connection_pool
    connection_pool.closeall()