connection record not immediately checked back in when connection fails after failed checkout event handler

[Mike Bayer]

from sqlalchemy import event
import mock
import sys
import sqlalchemy as sa

engine = sa.create_engine(
    "postgresql://scott:tiger@localhost/test",
    pool_size=1, max_overflow=0, pool_timeout=0, pool_recycle=3600)


@event.listens_for(engine, 'checkout')
def handle_checkout_event(dbapi_con, con_record, con_proxy):
    try:
        with dbapi_con.cursor() as cur:
            cur.execute("SELECT 1")
            cur.fetchone()
    except Exception:
        raise sa.exc.DisconnectionError()

# act as though the DB is turned off
conn = engine.connect()
dbapi_conn = conn.connection.connection
conn.close()

dbapi_conn.close()


def shutdown_backend():
    raise dbapi_conn.OperationalError("closed the connection")

patcher = mock.patch.object(engine.pool, "_creator", shutdown_backend)
patcher.start()

try:
    with engine.begin() as conn:
        pass
except sa.exc.OperationalError as e:
    print >>sys.stderr, "Got an (expected) error: ", e

# sys.exc_clear()

try:
    with engine.begin() as conn:
        pass
except sa.exc.OperationalError as e:
    print >>sys.stderr, "Got an (expected) error: ", e

assert True

the pool checkout fails on the second run because the single ConnectionRecord hasn't been checked in.

diff --git a/lib/sqlalchemy/pool.py b/lib/sqlalchemy/pool.py
index 0a4cdad..b38aefb 100644
--- a/lib/sqlalchemy/pool.py
+++ b/lib/sqlalchemy/pool.py
@@ -732,7 +732,13 @@ class _ConnectionFairy(object):
                 pool.logger.info(
                     "Disconnection detected on checkout: %s", e)
                 fairy._connection_record.invalidate(e)
-                fairy.connection = fairy._connection_record.get_connection()
+                try:
+                    fairy.connection = \
+                        fairy._connection_record.get_connection()
+                except:
+                    with util.safe_reraise():
+                        fairy._connection_record.checkin()
+
                 attempts -= 1

         pool.logger.info("Reconnection attempts exhausted on checkout")

[Mike Bayer]

• Fixed bug where in the case that a pool checkout event handler is used and the database can no longer be connected towards, that the checkout handler failure is caught, the attempt to re-acquire the connection also raises an exception, but the underlying connection record is not immediately re-checked in before the exception is propagated outwards, having the effect that the checked-out record does not close itself until the stack trace it's associated with is garbage collected, preventing that record from being used for a new checkout until we leave the scope of the stack trace. This can lead to confusion in the specific case of when the number of current stack traces in memory exceeds the number of connections the pool can return, as the pool will instead begin to raise errors about no more checkouts available, rather than attempting a connection again. The fix applies a checkin of the record before re-raising. fixes #3419

→ <<cset 4a0e51e7d2f3>>