[sqlalchemy] Cannot use pgbouncer's statement mode

2014-02-17 Thread Claudio Freire 
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.

Re: [sqlalchemy] Cannot use pgbouncer's statement mode

2014-02-17 Thread Michael Bayer 

On Feb 17, 2014, at 7:31 PM, Michael Bayer mike...@zzzcomputing.com wrote:

 
 O
 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()


oh and also if you are on 0.8 (not 0.7), you can just send AUTOCOMMIT to 
create_engine:

eng = create_engine(postgresql+psycopg2://scott:tiger@localhost/test,
echo=True,
isolation_level='AUTOCOMMIT'
)

much easier




signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [sqlalchemy] Cannot use pgbouncer's statement mode

2014-02-17 Thread Claudio Freire 
On Mon, Feb 17, 2014 at 9:31 PM, Michael Bayer mike...@zzzcomputing.com wrote:
 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()

This is exactly what I need, thanks. Furthermore, with psycopg2 level
autocommit, I don't think I even need a second statement-based pool,
it will decrease idle-in-transaction time on its own. In fact, I
thought this is what execution_options(autocommit=True) did, though
looking at the code, now I realize it wasn't.

Many thanks :)

PS: Yeah, I know I have to update to 8.x ;-)

-- 
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.

Re: [sqlalchemy] Cannot use pgbouncer's statement mode

2014-02-17 Thread Michael Bayer 

On Feb 17, 2014, at 9:27 PM, Claudio Freire klaussfre...@gmail.com wrote:

 
 This is exactly what I need, thanks. Furthermore, with psycopg2 level
 autocommit, I don't think I even need a second statement-based pool,
 it will decrease idle-in-transaction time on its own. In fact, I
 thought this is what execution_options(autocommit=True) did, though
 looking at the code, now I realize it wasn’t.

right, we currently don’t link the SQLAlchemy-level “autocommit” things with a 
DBAPI-level autocommit.  Not all DBAPIs have an autocommit option as it isn’t 
in the spec and I’m generally just concerned about how wide of a behavioral 
difference there’d be between different backends if we sometimes used their 
autocommit, sometimes, ours, etc.   


signature.asc
Description: Message signed with OpenPGP using GPGMail