Hello there. I'm using SQLAlchemy 0.7.10 with postgres+pgbouncer, and
I'm trying to move from a transaction-mode pool towards a
statement-mode pool.
The reason for this move, is that I've got quite a high volume of
reads that cannot be quickly serviced in transaction mode, since many
connections are left idling in a transaction for perhaps up to 1
second. A combination of network roundtrips and GIL-related delays
conspire to get me into this position.
So, I've been refactoring my application so that I can use two kinds
of sessions, read-write normal transactional ones, and read-only ones
that use autocommit and a different pgbouncer configured for statement
pooling.
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()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/opt/jampp/analytics/matlogbrowser/model/base.py", line 80, in
connection
return sqlalchemy.orm.Session.connection(self, *p, **kw)
File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line
784, in connection
File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line
788, in _connection_for_bind
File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py", line
313, in _connection_for_bind
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line
2489, in contextual_connect
File "build/bdist.linux-x86_64/egg/sqlalchemy/pool.py", line 236, in connect
File "build/bdist.linux-x86_64/egg/sqlalchemy/pool.py", line 401, in __init__
File "build/bdist.linux-x86_64/egg/sqlalchemy/pool.py", line 746, in _do_get
File "build/bdist.linux-x86_64/egg/sqlalchemy/pool.py", line 189, in
_create_connection
File "build/bdist.linux-x86_64/egg/sqlalchemy/pool.py", line 287, in __init__
File "build/bdist.linux-x86_64/egg/sqlalchemy/event.py", line 380,
in exec_once
File "build/bdist.linux-x86_64/egg/sqlalchemy/event.py", line 389, in __call__
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()' {}
Here, get_slave_session returns a subclass of Session that will
override connection() to return a connection that has
execution_options(autocommit=True) applied (because for pgbouncer,
even the implicit transaction for selects is a transaction). The
session itself is also built with a autocommit=True.
Now, it seems the session is using a connection that is indepepndent
of what connection() returns. What do I have to override to get the
desired behavior? (that is: one query per transaction no matter which
type of query).
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
