I am trying to use sessions on a certain connection pool like so:
from sqlalchemy import *
import sqlalchemy.pool as pool
import psycopg2 as psycopg
psycopg = pool.manage(psycopg,pool_size=2,max_overflow=1)
db =
create_engine('postgres://localhost/test',pool=psycopg,strategy='threadlocal')
metadata = BoundMetaData(db)
users_table = Table('users', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', String(40)),
Column('password', String(10)))
class User(object):
pass
usermapper = mapper(User, users_table)
Then i create loads of threads and in run() of each thread:
session = create_session()
transaction = session.create_transaction()
query = session.query(User)
u1=query.select(User.c.user_id==3)[0]
Obviously a transaction must block a connection.
so after 3 connection we are blocking.
OK, so after 30 sec of course there is a timeout exception and it says so...
However, after a few more timeout exceptions in a few threads,
There is an error:
OperationalError: FATAL: sorry, too many clients already
And all heck breaks loose, and SQLAlchemy tries to create as many connections
as there are threads which breaks my connection pool policy.
what to do?
Tell postgresql to allow more clients. That's what's causing your problem, although SA shouldn't spaz out when pg blocks you.
Also two questions:
- Can i set a timeout for a transaction or the time a session can occupy
a connection?
No.
i.e. that i can declare a connection dead.
At the very least, can i free a connection explicitly directly?
Sure, just "del conn" and the refcounting will take care of the rest.
- how do i associate a session to a pool (for example, i assign a pool for
operations that are expected to be very long like reports etc... that can
occupy a connection for minutes or even hrs. and a pool
for regular requests).
By default each engine has its own pool, and it's easy to bind Sessions to engines, so I think creating a separate engine would be the way to go. See
--
Jonathan Ellis
http://spyced.blogspot.com
------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys -- and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
