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

Question

I have a problem related to sqlalchemy and postgresql.

class Profile(Base):
  ...

  roles = relationship('Role', secondary=role_profiles,
                       backref='profiles', lazy='dynamic')

When running (current_user is an instance of the Profile class):

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

using sqlalchemy I get idle in transaction for all the selects for reading the profile in postgresql.

Edit:

From echoing the query I see that every select starts with:

BEGIN (implicit)

Another edit:

After adding

pool_size=20, max_overflow=0

to the create_engine it seems like the idle in transaction-statements are being rolled back when the number of idle are getting to big. Any idea on this and would this be a bad solution to the problem?

How do I manage this and how would I go about getting rid of the BEGIN for selects?

Answer 1

SQLA by default always operates in a transaction (some info here). In a web context most frameworks would handle committing this transaction for you at the end of the request (e.g. pyramid_tm). If you're not using a framework, or this is another type of application, you'll want to commit or rollback when you're finished, or at an appropriate point.

It may be possible to configure SQLA such that it doesn't automatically start a transaction but as far as I can see it's not how it is intended to be used so you'll probably have better luck not trying to fight it :).

Answer 2

Starting with SQLAlchemy 0.8.2 you can disable the implicit BEGIN statements when calling create_engine()

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

There are some subtle implications to this change. First, there statements that were not quietly hid in unterminated transaction will be quietly ignored

session.execute("DELETE FROM department WHERE department_id=18")
sys.exit(0)

default:

LOG:  statement: BEGIN
LOG:  statement: show standard_conforming_strings
LOG:  statement: DELETE FROM department WHERE department_id=18
LOG:  unexpected EOF on client connection with an open transaction

autocommit:

LOG:  statement: show standard_conforming_strings
LOG:  statement: DELETE FROM department WHERE department_id=18

Second, updating multiple updates are no longer automic, and rollback() is only 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 INTO department (name) VALUES ('HR') RETURNING department.department_id
LOG:  statement: ROLLBACK

autocommit:

LOG:  statement: INSERT INTO department (name) VALUES ('HR') RETURNING department.department_id

Setting SQLAlchemy's isolation_level on the Engine object is effective for many applications. It unfortunate that Session.begin() does not always mean BEGIN TRANSACTION;