python - SQLAlchemy connection errors -

i'm experiencing strange bugs seem caused connections used sqlalchemy, can't pin down exactly.. hoping has clue whats going on here.

we're working on pyramid (version 1.5b1) , use sqlalchemy (version 0.9.6) our database connectivity. errors related db connection or session, of time cursor closed or this connection closed error, other related exceptions too:

(operationalerror) connection pointer null (interfaceerror) cursor closed parent instance <...> not bound session, , no contextual session established; lazy load operation of attribute '...' cannot proceed  conflicting state present in identity map key (<class '...'>, (1001l,)) connection closed (original cause: resourceclosederror: connection closed) (interfaceerror) cursor closed parent instance <...> not bound session; lazy load operation of attribute '...' cannot proceed parent instance <...> not bound session, , no contextual session established; lazy load operation of attribute '...' cannot proceed 'nonetype' object has no attribute 'twophase' (operationalerror) connection pointer null session in 'prepared' state; no further 

there no silver bullet reproduce them, refreshing many times bound happen 1 @ point. made script using multi-mechanize spam different urls concurrently , see , when happens.

it appears url triggered doesn't matter, errors happen when there concurrent requests span longer time (and other requests served in between). seems indicate there kind of threading problem; either session or connection shared among different threads.

after googling these issues found lot of topics, of them tell use scoped sessions, thing use them already:

db_session = scoped_session(sessionmaker(extension=zopetransactionextension(), autocommit=false, autoflush=false)) db_meta = metadata() 

• we have basemodel our orm objects:

  basemodel = declarative_base(cls=basemodelobj, metaclass=basemeta, metadata=db_meta)

• we use pyramid_tm tween handle transactions during request

• we hook db_session.remove() pyramid newresponse event (which fired after has run). tried putting in seperate tween running after pyramid_tm or not doing @ all, none of these seem have effect, response event seemed clean place put it.

• we create engine in our main entrypoint of our pyramid project , use nullpool , leave connection pooling pgbouncer. configure session , bind our basemodel here:

  engine = engine_from_config(config.registry.settings, 'sqlalchemy.', poolclass=nullpool) db_session.configure(bind=engine, query_cls=filterquery) basemodel.metadata.bind = engine config.add_subscriber(cleanup_db_session, newresponse) return config.make_wsgi_app()

• in our app access db operation using:

  from project.db import db_session ... db_session.query(mymodel).filter(...) db_session.execute(...)

• we use psycopg2==2.5.2 handle connection postgres pgbouncer in between

• i made sure no references db_session or connections saved anywhere (which result in other threads reusing them)

i tried spamming test using different webservers, using waitress , cogen got errors easily, using wsgiref unsurprisingly have no errors (which singlethreaded). using uwsgi , gunicorn (4 workers, gevent) didn't errors.

given differences in webserver used, thought either has webservers handling requests in threads , using new processes (maybe forking problem)? complicate matters more, when time went on , did new tests, problem had gone away in waitress happened gunicorn (when using gevent)! have no clue on how go debugging this...

finally, test happens connection, attached attribute connection @ start of cursor execute , tried read attribute out @ end of execute:

@event.listens_for(engine, "before_cursor_execute") def _before_cursor_execute(conn, cursor, stmt, params, context, execmany):   conn.pdtb_start_timer = time.time()  @event.listens_for(engine, "after_cursor_execute") def _after_cursor_execute(conn, cursor, stmt, params, context, execmany):   print conn.pdtb_start_timer 

surprisingly raised exception: 'connection' object has no attribute 'pdtb_start_timer'

which struck me strange.. found 1 discussion similar: https://groups.google.com/d/msg/sqlalchemy/gqzsjhagkwm/rdfljvuywnej , tried adding strategy='threadlocal' engine, understand should force 1 connection tread. didn't have effect on errors im seeing.. (besides unittests failing because need 2 different sessions/connections tests , forces 1 connection associated)

does have idea might go on here or have more pointers on how attack problem?

thanks in advance!

matthijs blaas

update: errors caused multiple commands send in 1 prepared sql statement. psycopg2 seems allow this, apparently can cause strange issues. pg8000 connector more strict , bailed out on multiple commands, sending 1 command fixed issue!