[Twisted-Python] reconnecting ConnectionPools

In my client's app we've been having problems with database connections going down; so far if that happens we need to restart the server to reconnect to the DB. I looked at adbapi's implementation and noticed that it wasn't doing anything special to notice lost connections. Then I realized DBAPI2 doesn't actually specify a way to tell whether your connection has gone down. I've been trying to think of a way to at least work around this on a per-app basis -- mine is using psycopg. It just raises either a ProgrammingError or an OperationalError on cursor.execute. The brightest idea we've come up with is to reconnect after N (~ 3) consecutive errors. Any other ideas? -- Twisted | Christopher Armstrong: International Man of Twistery Radix | Release Manager, Twisted Project ---------+ http://radix.twistedmatrix.com/

On Sun, 2004-07-25 at 13:28, Christopher Armstrong wrote:
I've been trying to think of a way to at least work around this on a per-app basis -- mine is using psycopg. It just raises either a ProgrammingError or an OperationalError on cursor.execute. The brightest idea we've come up with is to reconnect after N (~ 3) consecutive errors.
Any other ideas?
Upon errors, test a command that will always work ("SELECT 1"), if that fails reconnect.

On Sun, 2004-07-25 at 12:41, Itamar Shtull-Trauring wrote:
On Sun, 2004-07-25 at 13:28, Christopher Armstrong wrote:
I've been trying to think of a way to at least work around this on a per-app basis -- mine is using psycopg. It just raises either a ProgrammingError or an OperationalError on cursor.execute. The brightest idea we've come up with is to reconnect after N (~ 3) consecutive errors.
Any other ideas?
Upon errors, test a command that will always work ("SELECT 1"), if that fails reconnect.
The test command should be easily overridden. You would think all db's would support 'select 1', but they don't. On Oracle, for example, you would need 'select 1 from dual'. dave

Dave Peticolas wrote:
On Sun, 2004-07-25 at 12:41, Itamar Shtull-Trauring wrote:
On Sun, 2004-07-25 at 13:28, Christopher Armstrong wrote:
I've been trying to think of a way to at least work around this on a per-app basis -- mine is using psycopg. It just raises either a ProgrammingError or an OperationalError on cursor.execute. The brightest idea we've come up with is to reconnect after N (~ 3) consecutive errors.
Any other ideas?
Upon errors, test a command that will always work ("SELECT 1"), if that fails reconnect.
The test command should be easily overridden. You would think all db's would support 'select 1', but they don't. On Oracle, for example, you would need 'select 1 from dual'.
Hmm, but can we put this functionality in adbapi? Maybe with an __init__ flag, cp_detectConnectionLost? -- Twisted | Christopher Armstrong: International Man of Twistery Radix | Release Manager, Twisted Project ---------+ http://radix.twistedmatrix.com/

On Sun, 2004-07-25 at 15:49, Christopher Armstrong wrote:
Dave Peticolas wrote:
On Sun, 2004-07-25 at 12:41, Itamar Shtull-Trauring wrote:
On Sun, 2004-07-25 at 13:28, Christopher Armstrong wrote:
I've been trying to think of a way to at least work around this on a per-app basis -- mine is using psycopg. It just raises either a ProgrammingError or an OperationalError on cursor.execute. The brightest idea we've come up with is to reconnect after N (~ 3) consecutive errors.
Any other ideas?
Upon errors, test a command that will always work ("SELECT 1"), if that fails reconnect.
The test command should be easily overridden. You would think all db's would support 'select 1', but they don't. On Oracle, for example, you would need 'select 1 from dual'.
Hmm, but can we put this functionality in adbapi? Maybe with an __init__ flag, cp_detectConnectionLost?
Yeah, that shouldn't be too hard. dave
participants (3)
-
Christopher Armstrong
-
Dave Peticolas
-
Itamar Shtull-Trauring