Re-try connect in case of (specific?) errors with new connection?

[blueyed]

I am seeing sporadic errors when trying to get a new connection on Google Cloud Run with PostgreSQL, and wondered what you think about a patch to re-try in case of those when acquiring a new connection.

Those are OperationalErrors, e.g.:

connection to server at "10.171.0.32", port 5432 failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

Traceback:

…
File "django/db/backends/base/base.py", line 260, in _cursor
    self.ensure_connection()
  File "django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "django/db/backends/base/base.py", line 243, in ensure_connection
    with self.wrap_database_errors:
  File "django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "django/db/backends/base/base.py", line 244, in ensure_connection
    self.connect()
  File "django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "django/db/backends/base/base.py", line 225, in connect
    self.connection = self.get_new_connection(conn_params)
  File "dj_db_conn_pool/core/mixins.py", line 76, in get_new_connection
    conn = db_pool.connect()
  File "sqlalchemy/pool/base.py", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File "sqlalchemy/pool/base.py", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "sqlalchemy/pool/base.py", line 476, in checkout
    rec = pool._do_get()
  File "sqlalchemy/pool/impl.py", line 145, in _do_get
    with util.safe_reraise():
  File "sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "sqlalchemy/pool/base.py", line 256, in _create_connection
    return _ConnectionRecord(self)
  File "sqlalchemy/pool/base.py", line 371, in __init__
    self.__connect()
  File "sqlalchemy/pool/base.py", line 665, in __connect
    with util.safe_reraise():
  File "sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "sqlalchemy/pool/base.py", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "sqlalchemy/pool/base.py", line 241, in <lambda>
    return lambda crec: creator()
  File "dj_db_conn_pool/core/mixins.py", line 60, in <lambda>
    lambda: self._get_new_connection(conn_params),
  File "dj_db_conn_pool/core/mixins.py", line 15, in _get_new_connection
    return super(PooledDatabaseWrapperMixin, self).get_new_connection(conn_params)
  File "django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "django/db/backends/postgresql/base.py", line 203, in get_new_connection
    connection = Database.connect(**conn_params)
  File "__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

[altairbow]

This traceback shows error happens when django are acquiring database connection while database connection pool was empty. this means this is the first time to try connecting to your database,

I don't think it is a good idea to retry connecting by django-db-connection-pool or SQLAlchemy while pool is still empty, you should catch OperationalErrors in your code.

PS: Assume after invoking creator (dj_db_conn_pool.core.mixins.PooledDatabaseWrapperMixin._get_new_connection), SQLAlchemy receives dbapi connection successfully, If django requires database connection again, SQLAlchemy will try to get a DBAPI connection from the pool, if this connection was abnormal, SQLAlchemy will make three attempts to re-connect to database.

[blueyed]

I don't think it is a good idea to retry connecting by django-db-connection-pool or SQLAlchemy while pool is still empty, you should catch OperationalErrors in your code.

Ok. While there still might be a transitional error in this case (so that it might help to retry), it is not that much an issue then.

Thanks for the additional explanation!

[blueyed]

Well, after all it happens with every new connection, not just the first one / if the pool is empty, doesn't it?

[altairbow]

Well, after all it happens with every new connection, not just the first one / if the pool is empty, doesn't it?

"pool is empty" has two states:

1. no connections has been checked-out from pool, in this case, the pool has just been created, it's empty
2. all connections in the pool has been checked-out, the pool is also empty

while "pool is empty", If the limit of pool is not exceeded, SQLAlchemy will make a new connection.

• SQLAlchemy doesn't retry if new connection fails: code
• SQLAlchemy checks connection got from pool, if connection is abnormal, three reconnection attempts will be made at maximum: code