Leaking database connections: PostgreSQL, SQLAlchemy, Flask

Question

I'm running PostgreSQL 9.3 and SQLAlchemy 0.8.2 and experience database connections leaking. After deploying the app consumes around 240 connections. Over next 30 hours this number gradually grows to 500, when PostgreSQL will start dropping connections.

I use SQLAlchemy thread-local sessions:

from sqlalchemy import orm, create_engine

engine = create_engine(os.environ['DATABASE_URL'], echo=False)
Session = orm.scoped_session(orm.sessionmaker(engine))

For the Flask web app, the .remove() call to the Session proxy-object is send during request teardown:

@app.teardown_request
def teardown_request(exception=None):
    if not app.testing:
        Session.remove()

This should be the same as what Flask-SQLAlchemy is doing.

I also have some periodic tasks that run in a loop, and I call .remove() for every iteration of the loop:

def run_forever():
    while True:
        do_stuff(Session)
        Session.remove()

What am I doing wrong which could lead to a connection leak?

Answer 1

If I remember correctly from my experiments with SQLAlchemy, the scoped_session() is used to create sessions that you can access from multiple places. That is, you create a session in one method and use it in another without explicitly passing the session object around. It does that by keeping a list of sessions and associating them with a "scope ID". By default, to obtain a scope ID, it uses the current thread ID; so you have session per thread. You can supply a scopefunc to provide – for example – one ID per request:

# This is (approx.) what flask-sqlalchemy does:
from flask import _request_ctx_stack as context_stack

Session = orm.scoped_session(orm.sessionmaker(engine),
                             scopefunc=context_stack.__ident_func__)

Also, take note of the other answers and comments about doing background tasks.