Need a way to remove 'bad' connections from a connection pool
Reported by Sam Morris | June 30th, 2011 @ 10:45 AM
In the following code:
c = pool.getconn() try: f(c) finally: pool.putconn()
If the database is restarted while f is executing then an OperationalError will be raised. The connection will then be put back into the pool in a bad state; when the code is run subsequently, f will receive the bad connection.
I'd like to be able to catch the OperationalError and tell the pool to remove the bad connection, but rummaging around inside used, rused and _pool seems impolite.
Comments and changes to this ticket
- Tag set to pool
- State changed from new to open
I see other problems in the pool too: if a connection is returned to the pool while a transaction is open, the connection is left idle in transaction.
A robust way to handle all the cases I think would be to check the transaction status, so cases such as lost connection or connection in transaction/error can be handled properly.
Looking at the pool code, there is a way to discard the connection: use putconn(cnn, close=True), but it is not documented: I will fix the documentation.
However I think the pool should be more careful on the connection it receives: I will fix it to detect broken/intrans/error connections and deal with them.
Ah, thanks for that information.
Does this look like the right thing to do in order to get a connection from the pool? Sorry if this sounds like a support request, but it will be a while until updated versions of psycopg2 filter down to end-users, so some example code might be helpful:
conn = None while conn is None: c = pool.getconn() try: c.cursor().execute('SELECT 0') except: pool.putconn(c,close=True) else: c.rollback() conn = c try: f(conn) except: conn.rollback() raise finally: pool.putconn(conn)
Yes, what you propose is a robust way to get a connection from the pool guarding from a server disconnection. I don't want to add a probe query into the pool code: the code will have a guard on putconn() discarding a broken connection but the f() could still receive a connection we don't know if broken. If your f() can't cope with it, you'd better keep your probing pattern even with future psycopg releases.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
Psycopg is the most used PostgreSQL adapter for the Python programming language. At the core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.