That's an easy way to accidentally leave a xact open way too long. You might have enough connections in to support this normally, but when things go slightly wrong, they go very wrong.
I still have to get a connection from the pool, I just do it inside the function body now, right?
So this
@app.get("/users")
def get_users(conn = Depends[get_db_conn]):
users = conn.execute("SELECT * FROM users")
return users
@app.get("/users")
def get_users(pool = Depends[get_db_pool]):
with pool.get_conn() as conn:
users = conn.execute("SELECT * FROM users")
return users
Your Python application maintains a connection to Pgbouncer during the lifecycle of the request, but the physical Postgres connection is allocated only during the DB transaction. You will need open/close transactions in your code though.
I also get it, xact should be 1:1 with connection in a lot of these backend applications. Sometimes I have a few little helpers for that, like pool.sql() will take conn, open xact, execute, close xact, return conn. If the DB driver doesn't already have that.
[send Query 1] -> [send Query 2] -> [send Query 3] -> [receive Result 1] -> [receive Result 2] -> [receive Result 3]
But in Postgres and MySQL, pipelined queries are not executed in parallel. They're just queued up for a single thread (per connection) to execute sequentially. Thus, if Query 1 is a transaction that takes too long, then it ends up blocking the execution of Query 2 and Query 3.