Donchack
Donchack

Reputation: 1

How can I reuse the existing asyncpg connection pool?

asyncpg.pool should be created at the first request and then reused for the next groups of requests. After the first successful use of the pool, an error occurs during reuse.

Environment:

Windows 10 22 H2
python 3.10.13

async-timeout==5.0.1
asyncpg==0.30.0

This code works:

import asyncio
import random


async def d(c: int):
    await asyncio.sleep(random.random())
    return c**2


async def main(input: list[any]):
    result = await asyncio.gather(*(d(i) for i in input))
    return result


if __name__ == "__main__":
    print(f"Start 1 asyncio.run()")
    result = asyncio.run(main([1, 2, 3]))
    print(f"{result = }")
    print(f"Start 2 asyncio.run()")
    result = asyncio.run(main(result))
    print(f"{result = }")

Result:

Start 1 asyncio.run()
result = [1, 4, 9]
Start 2 asyncio.run()
result = [1, 16, 81]

I add asyncpg.pool:

import asyncio
import asyncpg


async def d(c: str, pool: asyncpg.Pool):
    async with pool.acquire() as con:
        return await con.fetch("SELECT $1", c)


async def main(input: list[str], pool: asyncpg.Pool = None):
    if not pool:
        pool = await asyncpg.create_pool(dsn="Connection arguments")
    result = await asyncio.gather(*(d(i, pool) for i in input))
    print(f"{result = }")
    return pool


if __name__ == "__main__":
    print(f"Start 1 asyncio.run()")
    pool = asyncio.run(main(["1", "2", "3"]))
    print(f"{pool.is_closing() = }")
    print(f"{pool.get_size() = }")
    print(f"{pool.get_idle_size() = }")
    print(f"Start 2 asyncio.run()")
    asyncio.run(main(["2", "3", "4"], pool))

and get an error:

Start 1 asyncio.run()
result = [[<Record ?column?='1'>], [<Record ?column?='2'>], [<Record ?column?='3'>]]
pool.is_closing() = False
pool.get_size() = 10
pool.get_idle_size() = 10
Start 2 asyncio.run()
Traceback (most recent call last):
  File "c:\python310\lib\asyncio\base_events.py", line 753, in call_soon    
    self._check_closed()
  File "c:\python310\lib\asyncio\base_events.py", line 515, in _check_closed
    raise RuntimeError('Event loop is closed')
RuntimeError: Event loop is closed

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "d:\ProgProject\python\learn_asyncpg\111.py", line 7, in d
    return await con.fetch("SELECT $1", c)
  File "d:\ProgProject\python\learn_asyncpg\venv\lib\site-packages\asyncpg\connection.py", line 690, in fetch
    return await self._execute(
  File "d:\ProgProject\python\learn_asyncpg\venv\lib\site-packages\asyncpg\connection.py", line 1864, in _execute
    result, _ = await self.__execute(
  File "d:\ProgProject\python\learn_asyncpg\venv\lib\site-packages\asyncpg\connection.py", line 1961, in __execute
    result, stmt = await self._do_execute(
  File "d:\ProgProject\python\learn_asyncpg\venv\lib\site-packages\asyncpg\connection.py", line 2024, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg\\protocol\\protocol.pyx", line 206, in bind_execute
  File "asyncpg\\protocol\\protocol.pyx", line 192, in asyncpg.protocol.protocol.BaseProtocol.bind_execute
  File "asyncpg\\protocol\\coreproto.pyx", line 1020, in asyncpg.protocol.protocol.CoreProtocol._bind_execute
  File "asyncpg\\protocol\\coreproto.pyx", line 1008, in asyncpg.protocol.protocol.CoreProtocol._send_bind_message
  File "asyncpg\\protocol\\protocol.pyx", line 967, in asyncpg.protocol.protocol.BaseProtocol._write
  File "c:\python310\lib\asyncio\proactor_events.py", line 365, in write
    self._loop_writing(data=bytes(data))
  File "c:\python310\lib\asyncio\proactor_events.py", line 401, in _loop_writing
    self._write_fut = self._loop._proactor.send(self._sock, data)
AttributeError: 'NoneType' object has no attribute 'send'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "d:\ProgProject\python\learn_asyncpg\111.py", line 25, in <module>
    asyncio.run(main(["2", "3", "4"], pool))
  File "c:\python310\lib\asyncio\runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "c:\python310\lib\asyncio\base_events.py", line 649, in run_until_complete
    return future.result()
  File "d:\ProgProject\python\learn_asyncpg\111.py", line 13, in main
    result = await asyncio.gather(*(d(i, pool) for i in input))
  File "d:\ProgProject\python\learn_asyncpg\111.py", line 6, in d
    async with pool.acquire() as con:
  File "d:\ProgProject\python\learn_asyncpg\venv\lib\site-packages\asyncpg\pool.py", line 228, in release
    raise ex
  File "d:\ProgProject\python\learn_asyncpg\venv\lib\site-packages\asyncpg\pool.py", line 218, in release
    await self._con.reset(timeout=budget)
  File "d:\ProgProject\python\learn_asyncpg\venv\lib\site-packages\asyncpg\connection.py", line 1562, in reset
    await self.execute(reset_query)
  File "d:\ProgProject\python\learn_asyncpg\venv\lib\site-packages\asyncpg\connection.py", line 349, in execute
    result = await self._protocol.query(query, timeout)
  File "asyncpg\\protocol\\protocol.pyx", line 360, in query
  File "asyncpg\\protocol\\protocol.pyx", line 745, in asyncpg.protocol.protocol.BaseProtocol._check_state
asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress

Why do I get the error 'another operation is in progress' when I reuse a pool that is not closed and has idle connections? What kind of operation is meant?

Upvotes: 0

Views: 68

Answers (0)

Related Questions