On Thursday 03 August 2006 17:47, Jonathan Ellis wrote:
> On 8/3/06, Tzahi Fadida <[EMAIL PROTECTED]> wrote:
> > 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='threadlo
> >cal') 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.
No, that's not normal. The policy i set (as can be seen above) is to have 3
connections, which for a certain time it is, but after a while it breaks that
policy and opens new connection in an uncontrolled manner disregarding
this policy. That's a bug. If i am not blocking using the unclosed
transaction, then everything is ok and the 3 connections policy is kept.
Ok, i c what is the problem, i forgot to give you a crucial fact. after
this line "u1=query.select(User.c.user_id==3)[0]" i put a "lock.acquire()"
to simulate a block for a long time which creates the timeouts for any
attempt to create a new session.
I am pretty convinced this is a bug.
The normal way it should have worked is that all the new sessions should
have got the timeouts exception. The reason they did not is because
suddenly after a few timeout exceptions, SQLAlchemy breaks the policy
and tries to open new connections overflowing the overflow :), which
triggers this message "OperationalError: FATAL: sorry, too many clients
already" eventually (but that's after the bug of new connections).
>
> 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 know which connection a session is operating on?
>
> - 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
>
> http://www.sqlalchemy.org/docs/plugins.myt#plugins_sessioncontext
--
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
