#62 ✓ resolved
Sam Morris

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

  • Daniele Varrazzo

    Daniele Varrazzo June 30th, 2011 @ 02:05 PM

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

  • Daniele Varrazzo

    Daniele Varrazzo June 30th, 2011 @ 03:23 PM

    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.

  • Sam Morris

    Sam Morris June 30th, 2011 @ 05:07 PM

    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)
    
  • Daniele Varrazzo

    Daniele Varrazzo July 1st, 2011 @ 10:52 AM

    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.

  • Daniele Varrazzo

    Daniele Varrazzo August 9th, 2011 @ 10:39 AM

    • State changed from “open” to “resolved”

    The pool now is able to watch for the connection returned, caring to rolling back an eventual open connection or to dispose broken ones.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

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.

Shared Ticket Bins

People watching this ticket

Tags

Referenced by

Pages