CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Kevin Vasko
Kevin Vasko

Reputation: 1655

SQLAlchemy leaving sessions in 'idle' state even after session.close() and pool_size=5

I'm using SQLAlchemy and psycopg2 with PostgreSQL. I'm having sessions stay in the idle state when I look at the pg_stat_activity table with SELECT * FROM pg_stat_activity. Over the course of a few minutes I'll get up to 15-20 sessions in the "idle" state. These connections are all < 5min old.

My code is performing a dbsession.commit() and dbsession.close() and my pool_size is the default of 5. How am I getting 15-20 sessions open on the database side when the pool_size is 5? How can I limit the number?

The following are my dbsessions and create_engine functions. 

def processor(self, ids):
    logger.debug("Starting database connection")
    dbmanager = DatabaseManager(self.db_config)
    dbsession = dbmanager.get_db_session()
    logger.debug("Database session successfully created")
    rows = dbsession.query(TableA, TableB)\
        .filter(and_(TableA.id.is_(None), TableA.id.in_([ids])))\
        .outerjoin(TableB)\
        .all()
    for index, row in enumerate(rows):
        //processing code
    logger.debug("Committing data to database")
    dbsession.commit()
    dbsession.close()

My dbmanager class

from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL
from sqlalchemy.orm import sessionmaker

class DatabaseManager(object):

    def __init__(self, configuration):

        self.config = configuration

    def get_database_connection_string(self):

        db_url = {'drivername': self.config["dialect"],
                  'username': self.config["username"],
                  'password': self.config["password"],
                  'host': self.config["host"],
                  'database': self.config["database"],
                  'port': 5432}
        return URL(**db_url)

    def create_db_engine(self):
        connection_url = self.get_database_connection_string()
        engine = create_engine(connection_url)
        return engine

    def create_db_session(self, dbengine):
        Session = sessionmaker()
        Session.configure(bind=dbengine)
        dbsession = Session()
        return dbsession

    def get_db_session(self):
        engine = self.create_db_engine()
        session = self.create_db_session(engine)
        return session

What I'll see when I run a select from pg_stat_activity.

'Client','ClientRead','idle',,,'COMMIT','client backend'
'Client','ClientRead','idle',,,'COMMIT','client backend'
'Client','ClientRead','idle',,,'COMMIT','client backend'
'Client','ClientRead','idle',,,'COMMIT','client backend'
'Client','ClientRead','idle',,,'COMMIT','client backend'
'Client','ClientRead','idle',,,'COMMIT','client backend'
'Client','ClientRead','idle',,,'COMMIT','client backend'
'Client','ClientRead','idle',,,'COMMIT','client backend'
'Client','ClientRead','idle',,,'COMMIT','client backend'
'Client','ClientRead','idle',,,'COMMIT','client backend'

Upvotes: 4

Views: 2531

Answers (1)

Ricky Levi
Ricky Levi

Reputation: 8007

I believe it's because you're creating an Engine every-time you're initiating a new Session vs creating an Engine once and generate Sessions from it.

This will cause all sessions to be proxied via a single Engine

So the class should look like this

class DatabaseManager(object):

    def __init__(self, configuration):

        self.config = configuration
        self.engine = this.create_db_engine()

    def get_database_connection_string(self):

        db_url = {'drivername': self.config["dialect"], ....
        return URL(**db_url)

    def create_db_engine(self):
        connection_url = self.get_database_connection_string()          
        return create_engine(connection_url)

    def create_db_session(self):
        Session = sessionmaker()
        Session.configure(bind=self.engine)
        dbsession = Session()
        return dbsession

    def get_db_session(self):
        session = self.create_db_session()
        return session

I usually also re-use the same transaction( Session ) vs re-generating a Session every-time

class DatabaseManager(object):

    def __init__(self, configuration):
        ...
        self.session = None   // Added

    def get_db_session(self):
        if not self.session:
            self.session = self.create_db_session()

        return self.session

And never close it actually, because I'm working with Apache in a CGI mode ...

Upvotes: 1

Related Questions