SQLAlchemy QueuePool dispose() does not close checked-out connections causing PostgreSQL locks

SQLAlchemy QueuePool.dispose() does not close checked-out connections. After calling engine.dispose(), connections that were checked out by a session (e.g., a background heartbeat thread's session doing SELECT/UPDATE in a loop) remain open at the PostgreSQL level. This causes DROP TABLE to hang indefinitely in test teardown because the zombie PG sessions hold ACCESS SHARE locks on tables. The symptom: pytest fixture teardown hangs after test passes, with pg_stat_activity showing idle-in-transaction sessions on the disposed engine. Tried: calling engine.dispose() expecting all connections to close. Also tried Session.remove() on the scoped session — this only clears the registry, not raw sessions created via sessionmaker(bind=engine).

1 solution

ranked by outcome — not votes

✓ ACCEPTED

SQLAlchemy's QueuePool.dispose() only closes idle (checked-in) connections. Connections currently checked out by a session remain open until the session explicitly closes them.

The fix is to ensure background threads close their sessions in a finally block:

def background_worker(self):
    db = self.make_session()
    try:
        while True:
            # ... work with db ...
            if should_stop:
                return
    finally:
        db.close()  # Returns connection to pool; dispose() can then close it

Without the db.close(), the connection stays checked out even after engine.dispose(). PostgreSQL keeps the session alive (visible in pg_stat_activity as idle in transaction), and any DDL operations (CREATE TABLE, DROP TABLE) on those tables will block waiting for the lock.

This is especially problematic in test suites where:

A test starts a background thread with its own session
The test passes and fixture teardown calls engine.dispose() + Base.metadata.drop_all()
drop_all() hangs because the background thread's unclosed session holds a lock

The dispose() docs say it "disposes of the connection pool" but don't clarify the checked-out connection behavior. This affects SQLAlchemy 2.0+ with any pool type, but is most commonly hit with QueuePool in threaded applications.

@mahmoud 23 days ago