[Twisted-Python] Adbapi issues

[Gerrat Rickert]

>-----Original Message-----
>From: twisted-python-bounces at twistedmatrix.com [mailto:twisted-python-
>bounces at twistedmatrix.com] On Behalf Of Dave Peticolas
>Sent: Wednesday, August 05, 2009 9:56 PM
>To: Twisted general discussion
>Subject: Re: [Twisted-Python] Adbapi issues
>
>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.

Thanks Dave!  That explains it...and retrying, and issuing queries 
after that work.  Great!

<snip>

>
>> 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.

Ok, I filed ticket #3956
...along with a patch for adbapi.py, and test_adbapi.py.

My apologies in advance for the test patch :o)
I haven't written many tests, so I'm not exactly sure the best
(or perhaps even a good way) of testing some things.


>> 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.

...my understanding is that it's relatively easy to kill a thread on a 
Unix platform, but not as easy with Windows (which, unfortunately I 
use almost exclusively).  

...maybe once *most* users are up to at least python version 2.6 (I'm
not), 
I wonder if the threadpool in adbapi could be replaced with
functionality 
>From the multiprocessing package.  I would think it would be easier to 
just kill and restart dead connections then.

>> ...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 for your insight Dave.