[Twisted-Python] Adbapi issues

[Dave Peticolas]

Gerrat Rickert wrote:
> I'd like to use twisted's adbapi module (twisted 8.2.0 for python 2.5)
> with cx_Oracle, but I'm having some issues with it.  
> Specifically:
> 
> 1.  It doesn't seem to reconnect (or possibly I just need enlightenment
> on how reconnecting works):
> If I tweak part of the test_adbapi.py script to work for Oracle (using a
> proper "conn_str", and ignoring the irrelevant parts), I get something
> like:
> 
> class OracleTests(unittest.TestCase):
>     """Test adbapi for Oracle"""
>   
>     timeout = 10
>   
>     def setUp(self):
>         self.dbpool = adbapi.ConnectionPool('cx_Oracle', conn_str, 
>                cp_noisy=True, cp_min=1, cp_reconnect=True, cp_max=3,
> cp_good_sql='select * from dual', threaded=True)
>   
>   
>     def test_reconnect(self):
>         d = defer.succeed(None)
>         d.addCallback(self._testPool_1)
>         d.addCallback(self._testPool_2)
>         d.addCallback(self._testPool_3)
>         return d
>     
>     def _testPool_1(self, res):
>         def _success(rslt):
>             self.failUnless(rslt[0][0] == 'X', "Select from dual not
> working")
>     
>         d = self.dbpool.runQuery("select * from dual")
>         d.addCallback(_success)
>         return d
>   
>     def _testPool_2(self, res):
>         self.dbpool.connections.values()[0].close()
>   
>     def _testPool_3(self, res):
>     
>         sql = "select * from dual"
>         d = self.dbpool.runQuery(sql)
>         def _check(row):
>             self.failUnless(row[0][0] == 'X', " Select from dual not
> working ")
>         d.addCallback(_check)
>         return d
> 
> I get this traceback:
> 
> [ERROR]: test_oracle.OracleTests.test_reconnect
> Traceback (most recent call last):
>   File "C:\Python25\Lib\site-packages\twisted\python\threadpool.py",
> line 210, i
> n _worker
>     result = context.call(ctx, function, *args, **kwargs)
>   File "C:\Python25\Lib\site-packages\twisted\python\context.py", line
> 59, in ca
> llWithContext
>     return self.currentContext().callWithContext(ctx, func, *args, **kw)
>   File "C:\Python25\Lib\site-packages\twisted\python\context.py", line
> 37, in ca
> llWithContext
>     return func(*args,**kw)
>   File "c:\python25\lib\site-packages\twisted\enterprise\adbapi.py",
> line 429, i
> n _runInteraction
>     result = interaction(trans, *args, **kw)
>   File "c:\python25\lib\site-packages\twisted\enterprise\adbapi.py",
> line 443, i
> n _runQuery
>     trans.execute(*args, **kw)
> cx_Oracle.InterfaceError: not connected
> 
> ...am I doing something wrong, or is this something specific to Oracle?
> I apologize for the seemingly rhetorical question, but does this
> actually work for other databases (I don't have any others installed, so
> trial just skips most of the tests when I run test_adbapi.py)?

I didn't look at the code too closely, but here's something to note
about the 'reconnect' mode: If the connection fails, the first query
on the closed connection will still fail. Adbapi cannot, in general,
know whether the query went through and then the connection failed,
or not, so to be safe it doesn't retry.


> 2.  ...since I can't get it to reconnect properly, perhaps I could just
> close the connections in the old connection pool, and create a new
> one...
> 
> dbpool = adbapi.ConnectionPool('cx_Oracle', conn_str, 
>                cp_noisy=True, cp_min=1, cp_reconnect=True, cp_max=3,
> cp_good_sql='select * from dual', threaded=True)
> dbpool.close()
> 
> Traceback (most recent call last):
>   File "<stdin>", line 1, in <module>
>   File "c:\python25\lib\site-packages\twisted\enterprise\adbapi.py",
> line 359, i
> n close
>     if self.shutdownID:
> AttributeError: ConnectionPool instance has no attribute 'shutdownID'
> 
> ...shouldn't shutdownID be initialized in the class, or at least in
> __init__ ?

Yes, probably so. Feel submit a ticket, including a patch if you want.


> 3.  ...as stated in PEP 249, under fetchall(), "... Note that the
> cursor's arraysize attribute can affect the performance of this
> operation."  In the PEP, cursor.arraysize defaults to 1, which results
> in absolutely terrible performance when retrieving a large number of
> rows from the database (empirically tested).  Since twisted has already
> gone through the trouble of wrapping simple calls to the database (like
> it's runQuery), it would be ideal if this was an optional parameter that
> could be passed in.  It doesn't look possible to do this in the current
> adbapi module (or perhaps, I might just need enlightening again):
> 
>     def _runQuery(self, trans, *args, **kw):
>         trans.execute(*args, **kw)
>         return trans.fetchall() 
> 
> ...perhaps it could be something more like:
> 
>     def _runQuery(self, trans, *args, **kw):
>         if kw.has_key('arraysize'):
>             trans._cursor.arraysize = kw['arraysize']
>         trans.execute(*args, **kw)
>         return trans.fetchall()

Supporting arraysize in some form is probably a good idea. Another
ticket is in order.


> 
> 4.  Timeouts.  ...well, since Deferred.setTimeout is deprecated, and we
> can't cancel deferreds, most protocols (or asynchronous "mechanisms" if
> protocol isn't the right term here) should probably have a timeout
> mechanism.  (...or is everyone looking at me like I'm from outer-space?)
> Is there a canonical way of timing out a connection?  If I were to do a
> dbpool.runQuery("select some_cols from some_table"); and attach a
> timeout mechanism to the deferred that it returns, is there a way to
> drop/recycle that particular connection from the pool if it didn't
> respond in a timely fashion? ...or (assuming the dbpool.close() issue
> went away), would "best-practice" be just closing the old pool, and
> re-creating it?  

Since the query is running in a blocked thread, there's not much you
can do, as far as I know.


> 
> ...is this module widely used in production, or are most people using
> something like SqlAlchemy (or "rolling their own")?

I use it widely in production with cx_Oracle. I use a subclass so I can
set arraysize. I use it to provide connections to other scripts, so when
the connections go wonky I close and reopen the pool, or just restart
the process.


> Thanks,
> 	Gerrat
> 

dave