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?
Also two questions:
- Can i set a timeout for a transaction or the time a session can occupy
a connection? i.e. that i can declare a connection dead.
At the very least, can i free a connection explicitly directly?
- 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).
10x.
--
Regards,
Tzahi.
--
Tzahi Fadida
Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info
WARNING TO SPAMMERS: see at
http://members.lycos.co.uk/my2nis/spamwarning.html
-------------------------------------------------------------------------
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
