tortoise / tortoise/tortoise-orm

How to handle "connection has been released back to the pool"?

Open
#413 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

enhancement
Dominant language
Python
Stars
5.6k
Forks
516
Avg merge
2d 21h
Merged PRs (30d)
9

Description

Hello:
I wrote a retry logic but get some issue. I want when failed execute sql then disconenct and reconnect but get error "connection has been released back to the pool".
How should i retry execute sql or reconnect?

Thank you

async def _retry_frame(self, coroutine):
    while True:
        try:
            async with in_transaction():
                return await coroutine
        except  Exception:
            await Tortoise.close_connections()
            await Tortoise.init(......)
            await Tortoise.generate_schemas()

Here is traceback.

2020-05-20 18:22:26.937 | ERROR    | tortest.db_handle:_retry_frame:51 - error occur
File /home/tortest/db_handle.py, line 49, in _retry_frame
    45   async def _retry_frame(self, coroutine):
    46       while True:
    47           try:
    48               async with in_transaction():
--> 49                   return await coroutine
    50           except Exception:
    ..................................................
     self = <tortest.db_handle.TorDB object at 0x
             7f7e1461b4f0>
     coroutine = <tortoise.queryset.QuerySet object at 0x7f7e115a7380>
     OperationalError = <class 'tortoise.exceptions.OperationalError'>
     ConnectionDoesNotExistError = <class 'asyncpg.exceptions.ConnectionDoesNotExistError'>
     gaierror = <class 'socket.gaierror'>
    ..................................................

File /usr/local/lib/python3.8/dist-packages/tortoise/queryset.py, line 706, in _execute
    705  async def _execute(self) -> List[MODEL]:
--> 706      instance_list = await self._db.executor_class(
    707          model=self.model,
    ..................................................
     self = <tortoise.queryset.QuerySet object at 0x7f7e115a7380>
     List = typing.List
     MODEL = ~MODEL
     self._db.executor_class = <class 'tortoise.backends.asyncpg.executor.AsyncpgExecutor'>
     self.model = <class 'tortest.models.File'>
    ..................................................

File /usr/local/lib/python3.8/dist-packages/tortoise/backends/base/executor.py, line 108, in execute_select
    107  async def execute_select(self, query: Query, custom_fields: Optional[list] = None) -> list:
--> 108      _, raw_results = await self.db.execute_query(query.get_sql())
    109      instance_list = []
    ..................................................
     self = <tortoise.backends.asyncpg.executor.AsyncpgExecutor object a
             t 0x7f7e10f36ca0>
     query = SELECT "file_size","file_path","id","file_sha256_hash","magn
              et_id" FROM "files" WHERE "id"=362163 LIMIT 1
     Query = <class 'pypika.queries.Query'>
     custom_fields = []
     Optional = typing.Optional
     self.db.execute_query = <method 'AsyncpgDBClient.execute_query' of <tortoise.backend
                              s.asyncpg.client.TransactionWrapper object at 0x7f7e11d3c730
                              > client.py:33>
     query.get_sql = <method 'PostgreQueryBuilder.get_sql' of SELECT "file_size",
                      "file_path","id","file_sha256_hash","magnet_id" FROM "files"
                       WHERE "id"=362163 LIMIT 1 dialects.py:523>
    ..................................................

File /usr/local/lib/python3.8/dist-packages/tortoise/backends/asyncpg/client.py, line 36, in translate_exceptions_
    33   @wraps(func)
    34   async def translate_exceptions_(self, *args):
    35       try:
--> 36           return await func(self, *args)
    37       except (asyncpg.SyntaxOrAccessError, asyncpg.exceptions.DataError) as exc:
    ..................................................
     func = <function 'AsyncpgDBClient.execute_query' client.py:154>
     self = <tortoise.backends.asyncpg.client.TransactionWrapper object
             at 0x7f7e11d3c730>
     args = ('SELECT "file_size","file_path","id","file_sha256_hash","ma
             gnet_id" FROM "files" WHERE "id"=362163 LIMIT 1', )
     asyncpg.SyntaxOrAccessError = <class 'asyncpg.exceptions.SyntaxOrAccessError'>
     asyncpg.exceptions.DataError = <class 'asyncpg.exceptions.DataError'>
    ..................................................

File /usr/local/lib/python3.8/dist-packages/tortoise/backends/asyncpg/client.py, line 172, in execute_query
    155  async def execute_query(
    156      self, query: str, values: Optional[list] = None
    157  ) -> Tuple[int, List[dict]]:
 (...)
    168              except Exception:  # pragma: nocoverage
    169                  rows_affected = 0
    170              return rows_affected, []
    171
--> 172          rows = await connection.fetch(*params)
    173          return len(rows), rows
    ..................................................
     self = <tortoise.backends.asyncpg.client.TransactionWrapper object
             at 0x7f7e11d3c730>
     query = 'SELECT "file_size","file_path","id","file_sha256_hash","mag
              net_id" FROM "files" WHERE "id"=362163 LIMIT 1'
     values = None
     Optional = typing.Optional
     Tuple = typing.Tuple
     List = typing.List
     connection.fetch = <method 'Connection.fetch' of <PoolConnectionProxy [released
                         ] 0x7f7e10efdd60> pool.py:50>
     params = ['SELECT "file_size","file_path","id","file_sha256_hash","ma
               gnet_id" FROM "files" WHERE "id"=362163 LIMIT 1', ]
    ..................................................

File /usr/local/lib/python3.8/dist-packages/asyncpg/pool.py, line 53, in call_con_method
    50   def call_con_method(self, *args, **kwargs):
    51       # This method will be owned by PoolConnectionProxy class.
    52       if self._con is None:
--> 53           raise exceptions.InterfaceError(
    54               'cannot call Connection.{}(): '
    ..................................................
     self = <PoolConnectionProxy [released] 0x7f7e10efdd60>
     args = ('SELECT "file_size","file_path","id","file_sha256_hash","ma
             gnet_id" FROM "files" WHERE "id"=362163 LIMIT 1', )
     kwargs = {}
     self._con = None
     exceptions.InterfaceError = <class 'asyncpg.exceptions._base.InterfaceError'>
    ..................................................

---- (full traceback above) ----
File /home/tortest/db_handle.py, line 49, in _retry_frame
    return await coroutine
File /usr/local/lib/python3.8/dist-packages/tortoise/queryset.py, line 706, in _execute
    instance_list = await self._db.executor_class(
File /usr/local/lib/python3.8/dist-packages/tortoise/backends/base/executor.py, line 108, in execute_select
    _, raw_results = await self.db.execute_query(query.get_sql())
File /usr/local/lib/python3.8/dist-packages/tortoise/backends/asyncpg/client.py, line 36, in translate_exceptions_
    return await func(self, *args)
File /usr/local/lib/python3.8/dist-packages/tortoise/backends/asyncpg/client.py, line 172, in execute_query
    rows = await connection.fetch(*params)
File /usr/local/lib/python3.8/dist-packages/asyncpg/pool.py, line 53, in call_con_method
    raise exceptions.InterfaceError(

InterfaceError: cannot call Connection.fetch(): connection has been released back to the pool

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the retry wrapper in db_handle.py and trace the QuerySet execution through tortoise/queryset.py, the asyncpg executor, and asyncpg/client.py. Reproduce the released PoolConnectionProxy failure and determine the intended retry or reconnection behavior; the issue currently names no test or acceptance criteria for defining when this is done.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, python
Domain
backend, databases
Issue type
Bug
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.