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)? 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__ ? 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() 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? ...is this module widely used in production, or are most people using something like SqlAlchemy (or "rolling their own")? Thanks, Gerrat
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
On 01:56 am, dave@krondo.com wrote:
Gerrat Rickert wrote: [snip]
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.
This looks a lot like a bug that's fixed in trunk already. I suggest trying this out with a recent checkout of trunk to see if it goes away before spending much time trying to track it down. Jean-Paul
-----Original Message----- From: twisted-python-bounces@twistedmatrix.com [mailto:twisted-python- bounces@twistedmatrix.com] On Behalf Of exarkun@twistedmatrix.com Sent: Thursday, August 06, 2009 10:44 AM To: Twisted general discussion Subject: Re: [Twisted-Python] Adbapi issues
On 01:56 am, dave@krondo.com wrote:
Gerrat Rickert wrote: [snip]
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.
This looks a lot like a bug that's fixed in trunk already. I suggest trying this out with a recent checkout of trunk to see if it goes away before spending much time trying to track it down.
Jean-Paul
Well, before filing this as a bug, I found Ticket #2680, which I think is the same issue. Nothing's been fixed in the trunk though (...as of 30 seconds ago anyway). ...and judging from all the discussion on that ticket, maybe resolving this isn't as trivial as I would have expected. Gerrat
-----Original Message----- From: twisted-python-bounces@twistedmatrix.com [mailto:twisted-python- bounces@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
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",
59, in ca llWithContext return self.currentContext().callWithContext(ctx, func, *args, **kw) File "C:\Python25\Lib\site-packages\twisted\python\context.py",
Gerrat Rickert wrote: line 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.
On 06:57 pm, grickert@coldstorage.com wrote:
[snip]
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.
Great. Thanks a lot! :)
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.
No worries. I've given you some feedback on the ticket (I did that before I noticed this email). Please feel free to ask if anything I wrote is unclear.
[snip]
...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).
It's about equally easy on either platform. However, it's also basically unreliable on both platforms. What happens if the killed thread held a mutex, for example?
...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.
Twisted actually has good support for running child processes already, so we don't have to wait for Python 2.6, if this is a direction that's interesting. Perhaps something based on Ampoule would make sense? It could be an API compatible replacement for twisted.enterprise.adbapi, but use child processes instead of threads, and add one new method for interrupting outstanding work. Jean-Paul
-----Original Message----- From: twisted-python-bounces@twistedmatrix.com [mailto:twisted-python- bounces@twistedmatrix.com] On Behalf Of exarkun@twistedmatrix.com Sent: Thursday, August 06, 2009 3:25 PM To: Twisted general discussion Subject: Re: [Twisted-Python] Adbapi issues
On 06:57 pm, grickert@coldstorage.com wrote:
[snip]
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.
Great. Thanks a lot! :)
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.
No worries. I've given you some feedback on the ticket (I did that before I noticed this email). Please feel free to ask if anything I wrote is unclear.
[snip]
Wow, the feedback on the patch (attempt) had a much gentler tone than I expected ;o) Thanks. I'll see what I can improve.
...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).
It's about equally easy on either platform. However, it's also basically unreliable on both platforms. What happens if the killed thread held a mutex, for example?
...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.
Twisted actually has good support for running child processes already, so we don't have to wait for Python 2.6, if this is a direction that's interesting.
Perhaps something based on Ampoule would make sense? It could be an API compatible replacement for twisted.enterprise.adbapi, but use child processes instead of threads, and add one new method for interrupting outstanding work.
Jean-Paul
I've never looked at Ampoule, but I like the idea and wouldn't mind investigating the API compatible replacement idea (...not promising anything...). Gerrat
participants (3)
-
Dave Peticolas -
exarkun＠twistedmatrix.com -
Gerrat Rickert