Issue #121 resolved
Anonymous created an issue

(original reporter: Justin) SQLAlchemy should support reconnecting to the backend if the connection dies for whatever reason.

Comments (8)

  1. Anonymous

    (original author: Justin) without the

    + + try: + del self._threadconnsthread.get_ident() + except KeyError: + pass

    It would never reconnect. The interesting thing is that self._threadconns always seemed to be empty until c.parent.invalidate() was called. After that it would have the (now broken) connection in it

    I have it running a query in a loop while postgresql is restarted every 2 seems to work.... other than the fact that it generates an exception from the outer execute()...

    'NoneType' object has no attribute 'rollback'

  2. Michael Bayer repo owner

    we can add the removal from the self._threadconns, but the following unit test should pass without it:

    1. create pool (not an engine, just a plain QueuePool)
    2. get connection, log the ID of the underlying connection.connection
    3. call invalidate() on that connection
    4. remove all references to that connection
    5. get another connection again from pool, ID of the underlying connection.connection should be different from the previous

    the "del self._threadconns" logic will help in the case that you dont remove the reference to the connection, which should be unit test #2 in this group.

    also hypothetical unit-tests #3 and #4 should be the same thing as well, without the "invalidate()", and testing that the underlying ID does not change when u get the same connection twice....

  3. Michael Bayer repo owner

    After adding "recycle" support for #224, I also changed the semantics of invalidate() so that a connection-holding _ConnectionRecord can invalidate and reopen a new connection in place, without the Pool having anything to do with it. Identifying an actual "closed" situation is very tough, and the ones in your patch didnt really work. it seems like one place to really decide that a connection is "bad" is when you call a cursor off of it; so i have added logic to the underlying call to cursor to invalidate the connection. if this works it would be great since its independent of all db's and error messages. it was also the only thing that I could actually get to work while testing with stopping and starting a postgres database.

    you might want to try it out....this is very hard to test. when the database is stopped, youll definitely get a lot of nasty errors and stack traces (because the execute call fails, then hits all the auto-rollback stuff which also fails, and its hard to see what actually went wrong); but the main idea is that when the db starts again, calling pool.connect() should immediately resume giving you fresh new connections, and an application would not have to be restarted. reopen the ticket if it totally doesnt work.


  4. Log in to comment