On Feb 17, 2014, at 7:03 PM, Claudio Freire <[email protected]> wrote:
>
> Problem I run into, is that even in autocommit mode, SQLAlchemy itself
> issues multi-statement transactions, like this:
>
>>>> s = get_slave_session(autocommit=True)
>>>> c = s.connection()
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/strategies.py",
> line 167, in first_connect
> File "build/bdist.linux-x86_64/egg/sqlalchemy/dialects/postgresql/base.py",
> line 1000, in initialize
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py",
> line 171, in initialize
> File "build/bdist.linux-x86_64/egg/sqlalchemy/dialects/postgresql/base.py",
> line 1184, in _get_server_version_info
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line
> 1449, in execute
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line
> 1628, in _execute_text
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line
> 1698, in _execute_context
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line
> 1691, in _execute_context
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py",
> line 331, in do_execute
> sqlalchemy.exc.DatabaseError: (DatabaseError) Long transactions not allowed
> ERROR: Long transactions not allowed
> 'select version()' {}
OK well when you have a dialect, the first time you connect it runs a series of
statements to get basic information like server version info, unicode behavior,
other settings, and it would be quite silly for it to connect individually
and/or emit a ROLLBACK after each of those statements.
the Python DBAPI as you know returns a connection that’s in a transaction. So
if you are using an environment that has zero tolerance for even two statements
in one transaction, you should turn on “autocommit” at the psycopg2 level,
using the “connection.autocommit” flag. you can achieve this with an
on-connect event, with some tricks currently needed to absolutely force it as
the first thing that happens (I need to either document this or make a nicer
API, but this works for now), like this:
from sqlalchemy import event, create_engine
eng = create_engine("postgresql+psycopg2://scott:tiger@localhost/test",
echo=True)
@event.listens_for(eng, "first_connect", insert=True)
@event.listens_for(eng, "connect", insert=True)
def setup_autocommit(dbapi_conn, rec):
dbapi_conn.autocommit = True
conn = eng.connect()
print conn.execute("select 1").fetchall()
if you aren’t using psycopg2-level autocommit, a lot of things might end up
running two statements, of course a session.flush() will run many statements in
a transaction, and any kind of subquery or immediate eager loading will as
well. This approach of course means you need to link those Session objects
(which need no changes at all!) to one engine or another.
Now if you want to do this more at the SQLAlchemy level and still have
DBAPI-level transactions happening with only one statement each (I’m not sure
why you’d need that), you can do that too! you can implement the
after_cursor_execute() event and just call commit() on the DBAPI connection.
This can have complications as the results on the cursor are still pending but
I think psycopg2 is OK with that. Though in 0.7 I’m not sure that you’re
really going to catch every single execution. I think turning off
transactions entirely with psycopg2 autocommit is the best approach.
signature.asc
Description: Message signed with OpenPGP using GPGMail
