python - sqlalchemy, postgresql and relationship stuck in "idle in transaction" -

i have problem related sqlalchemy , postgresql.

class profile(base):   ...    roles = relationship('role', secondary=role_profiles,                        backref='profiles', lazy='dynamic') 

when running (current_user instance of profile class):

roles = current_user.roles.filter().all() 

using sqlalchemy idle in transaction selects reading profile in postgresql.

edit:

from echoing query see every select starts with:

begin (implicit) 

another edit:

after adding

pool_size=20, max_overflow=0 

to create_engine seems idle in transaction-statements being rolled when number of idle getting big. idea on , bad solution problem?

how manage , how go getting rid of begin selects?

starting sqlalchemy 0.8.2 can disable implicit begin statements when calling create_engine()

engine = create_engine(uri, isolation_level="autocommit") 

there subtle implications change. first, there statements not quietly hid in unterminated transaction quietly ignored

session.execute("delete department department_id=18") sys.exit(0) 

default:

log:  statement: begin log:  statement: show standard_conforming_strings log:  statement: delete department department_id=18 log:  unexpected eof on client connection open transaction 

autocommit:

log:  statement: show standard_conforming_strings log:  statement: delete department department_id=18 

second, updating multiple updates no longer automic, , rollback() conditionally effective:

department = department(u"hr") session.add(department) session.flush() employee = employee(department.department_id, u'bob') session.add(employee) session.rollback() 

default:

log:  statement: begin log:  statement: insert department (name) values ('hr') returning department.department_id log:  statement: rollback 

autocommit:

log:  statement: insert department (name) values ('hr') returning department.department_id 

setting sqlalchemy's isolation_level on engine object effective many applications. unfortunate session.begin() not mean begin transaction;