'idle in transaction' PostgreSQL queries when using Flask-SQLAlchemy with async Flask views

Question

I have a legacy Flask project which now has a few async views that also make a synchronous database call using Flask-SQLAlchemy. My expectation is that these will be blocking calls, but that they should otherwise work fine. However, I've noticed that when the Flask-SQLAlchemy session is used in a async view, the transaction gets stuck as 'idle in transaction' in the database.

This issue can be replicated by running the following sample code:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://127.0.0.1:5432/temp"
app.config["SQLALCHEMY_ECHO"] = True
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

db = SQLAlchemy(app)

class SomeModel(db.Model):
    """Simplest possible model."""
    id = db.Column(db.Integer, primary_key=True)

@app.route("/async")
async def hello_async():
    """Sample asynchronous view."""
    SomeModel.query.all()
    return "Hello"

@app.route("/sync")
def hello_sync():
    """Sample synchronous view."""
    SomeModel.query.all()
    return "Hello"

if __name__ == "__main__":
    db.create_all()
    app.run()

When the /sync endpoint is loaded, the terminal output looks like:

2022-09-26 09:28:21,300 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-26 09:28:21,300 INFO sqlalchemy.engine.Engine SELECT some_model.id AS some_model_id
FROM some_model
2022-09-26 09:28:21,300 INFO sqlalchemy.engine.Engine [cached since 15.05s ago] {}
2022-09-26 09:28:21,303 INFO sqlalchemy.engine.Engine ROLLBACK
127.0.0.1 - - [26/Sep/2022 09:28:21] "GET /sync HTTP/1.1" 200 -

However, when the /async endpoint is accessed I see:

2022-09-26 09:28:46,277 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-26 09:28:46,277 INFO sqlalchemy.engine.Engine SELECT some_model.id AS some_model_id
FROM some_model
2022-09-26 09:28:46,277 INFO sqlalchemy.engine.Engine [cached since 40.03s ago] {}
127.0.0.1 - - [26/Sep/2022 09:28:46] "GET /async HTTP/1.1" 200 -

Notice that this second set of logs is missing the INFO sqlalchemy.engine.Engine ROLLBACK that is emitted in the synchronous version. As a result, if I query Postgres with:

select query, state from pg_stat_activity where state = 'idle in transaction'

I will see one idle query for every request I've made to the async endpoint. If I make additinal requests to the /async endpoint these queries will eventualy saturate the SQLAlchemy connection pool causing TimeoutError: QueuePool limit errors and the app will 500.

Looking through the Flask-SQLAlchemy source code, I can see that the ROLLBACK is normally emitted by the call to self.session.remove() on this line of code in the @app.teardown_appcontext block. I can confirm that this line is also called at the end of each async view, but it doesn't emit any SQL or ends the session.

My question is: is there a way for me to use my existing synchronous Flask-SQLAlchemy session in a async Flask view and have it close out the session correctly?

Answer 1

For posterity, this was a bug in Flask-SQLAlchemy that was fixed in version 3.0.0