On Saturday 05 August 2006 17:42, Michael Bayer wrote:
> well, if your app has SessionTransactions lying around that dont have
> an explicit commit() or rollback(), thats a situation that should be
> corrected in your application. gc.collect() is a heavy operation
> best managed by the python interpreter in normal circumstances.
My framework hosts projects, i.e. it is not exactly sound to assume that all
the projects are bug free. If i had to assume that, the downtime would be
considerable.
Anyway, i am trying to do something with this issue by doing gc.collect()
when no connections are left.
My first question, is how do i make a generic pool. both for PostgreSQL,
mysql, etc... Specifically i am more interested in how to define
max_overflow, pool_size and not necessarily saying what database i am working
with. (though push comes to shove my expertise is on PostgreSQL so...)
And the second question, i wish to block until i get a connection but do
answer to timeouts so my idea is to touch the database when i am creating a
transaction and block until a connection is available:
u1 = None
while not u1:
try:
u1=query.select(User.c.user_id==3)[0]
except TimeoutError:
gc.collect()
What i am seeking is to replace the line:
u1=query.select(User.c.user_id==3)[0]
with a generic touch for all databases method so that the session will attempt
to connect.
p.s.: i tried to do the blocking in a custom pool but the queuepool
checkedin() is apparently unreliable because naturally Queue qsize is
unreliable.
If there was, besides the getconn (custom connection), maybe something like
endedconnection, i could use a BoundedSemaphore on it.
As a last resort, i guess i can subclass the Queuepool but i am averse to
using other lib internals.
10x.
>
> On Aug 5, 2006, at 10:30 AM, Tzahi Fadida wrote:
> > OK, if i use gc.collect() it is released.
> > Though i was very surprised that collect() takes so much time.
> > I wonder if it is reasonable to gc.collect() after each thread ends
> > or at least when the pool manager thinks the rope is getting too
> > short.
> >
> > On Saturday 05 August 2006 16:46, Michael Bayer wrote:
> >> On Aug 4, 2006, at 10:06 PM, Tzahi Fadida wrote:
> >>> Anyway, if i believe there is a bug i am reporting it in case other
> >>> bugs follows.
> >>>
> >>> I have tried what you described with and without threadlocal, and
> >>> with
> >>> explicitly deleting every object i can find, like the transaction,
> >>> session
> >>> etc... and it does not release the connections.
> >>
> >> you should show me a test program that illustrates this. keep in
> >> mind that __del__() is not guaranteed to be called immediately, and
> >> sleeping threads is also no guarantee. while the connection proxy
> >> that you get from a pool is constructed without circular references
> >> and has very good __del__() behavior in cPython, the
> >> SessionTransaction is not meant for that kind of usage so it hasnt
> >> been optimized for that.
> >>
> >> oftentimes you have to wait for the garbage collector to come
> >>
> >> around. so heres my test, which works fine:
> >>>>> from sqlalchemy import *
> >>>>> e = create_engine('sqlite://', echo_pool=True)
> >>>>> s = create_session(bind_to=e)
> >>>>> t = s.create_transaction()
> >>>>> c = t.connection(None)
> >>
> >> [2006-08-05 09:38:15,242] [pool] : Connection
> >> <pysqlite2.dbapi2.Connection object at 0x1059980> checked out from
> >> pool
> >>
> >>>>> c = None
> >>>>> t = None
> >>>>> s = None
> >>>>> import gc
> >>>>> gc.collect()
> >>
> >> [2006-08-05 09:38:28,452] [pool] : Connection
> >> <pysqlite2.dbapi2.Connection object at 0x1059980> being returned
> >> to pool
> >> 28
> >>
> >>> Btw:
> >>> The db engine,mapping,metadata are defined and exists persistently
> >>> outside the
> >>> threads. Sessions and transactions are created inside threads.
> >>> The threads are destroyed immediately after transaction creation
> >>> (and running
> >>> a little select query as i described in previous mails).
> >>> Also, just to be on the safe side :), one thread is sleeping for 60
> >>> secs and
> >>> then try to repeat the transaction creation etc... just to make
> >>> sure if
> >>> SQLAlchemy needs to do anything active to release the connection.
> >>>
> >>> On Saturday 05 August 2006 03:47, Michael Bayer wrote:
> >>>> actually, all connections that are freed by the pool have a
> >>>> rollback
> >>>> () called on them automatically.
> >>>>
> >>>> also, connections are returned to the pool when the proxying object
> >>>> of the connection has its __del__ method called, so the behavior
> >>>> you
> >>>> want is already there. if you lose all references to a
> >>>> SessionTransaction as well as the Session its associated with, when
> >>>> they get garbage collected, so will all connections theyre holding
> >>>> onto, and the connection will be returned to the pool (and the
> >>>> rollback() will be performed). assuming your engine is not
> >>>> using the
> >>>> "threadlocal" strategy.
> >>>>
> >>>> if you are using the "threadlocal" strategy, not as simple; the
> >>>> engine is holding on to the current transaction associated with the
> >>>> current thread; its by design that you dont have to hold on to any
> >>>> transactional object, and the transaction remains open, until
> >>>> explicitly committed or rolled back (which can be performed
> >>>> directly
> >>>> off the engine in the case of 'threadlocal').
> >>>>
> >>>> if you want very explicit management of resources, not using
> >>>> "threadlocal" for your create_engine() strategy, using "default"
> >>>> instead, is the first step.
> >>>>
> >>>> also, even though the functionality you want is present, it strikes
> >>>> me as a poor practice to use a SessionTransaction without a try:/
> >>>> finally: block that explicitly calls rollback() or commit() on it.
> >>>> I cant think of a reason youd want to begin a transaction and then
> >>>> just let it fall away without managing its closure; it seems to
> >>>> defeat the purpose of a transaction, which is, demarcation of a set
> >>>> of operations.
> >>>
> >>> --
> >>> 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
> >
> > --
> > 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
--
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
