AsyncPG with twitchAPI error

[l3keboy]

• asyncpg version: 0.27.0
• PostgreSQL version: 14
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?: No
• Python version: 3.10.2
• Platform: Windows
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes
• If you built asyncpg locally, which version of Cython did you use?: No
• Can the issue be reproduced under both asyncio and
  uvloop?: Not tested

Hello! This is not really a bug report but more of a help wanted. I am trying to use the twitchAPI library to listen to stream online events (whenever a streamer goes online). This requires a callback in which i want to access my database to retrieve some information which I need to use (I am making a discord bot btw). Whenever I try to call my database I get the following error:

(<class 'asyncpg.exceptions.ConnectionDoesNotExistError'>, ConnectionDoesNotExistError('connection was closed in the middle of operation'), None)
cannot perform operation: another operation is in progress Traceback (most recent call last):
  File "C:\Users\User\Documents\Coding Projects\Project\project\utils\DatabaseHandler.py", line 1683, in get_guilds_by_twitch_account
    async with conn.transaction():
  File "C:\Users\User\Documents\Coding Projects\Project\.venv\lib\site-packages\asyncpg\transaction.py", line 62, in __aenter__
    await self.start()
  File "C:\Users\User\Documents\Coding Projects\Project\.venv\lib\site-packages\asyncpg\transaction.py", line 138, in start
    await self._connection.execute(query)
  File "C:\Users\User\Documents\Coding Projects\Project\.venv\lib\site-packages\asyncpg\connection.py", line 317, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg\protocol\protocol.pyx", line 338, in query
RuntimeError: Task <Task pending name='Task-118' coro=<TwitchHandler.on_stream_online() running at C:\Users\User\Documents\Coding Projects\Project\project\extentions\module_interactions\module_socials\TwitchHandler.py:104>> got Future <Future pending cb=[Protocol._on_waiter_completed()]> attached to a different loop

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\User\Documents\Coding Projects\Project\project\extentions\module_interactions\module_socials\TwitchHandler.py", line 104, in on_stream_online
    all_corresponding_guilds = await self.db.get_guilds_by_twitch_account(twitch_account=twitch_account)
  File "C:\Users\User\Documents\Coding Projects\Project\project\utils\DatabaseHandler.py", line 1682, in get_guilds_by_twitch_account
    async with self._pool.acquire() as conn:
  File "C:\Users\User\Documents\Coding Projects\Project\.venv\lib\site-packages\asyncpg\pool.py", line 220, in release
    raise ex
  File "C:\Users\User\Documents\Coding Projects\Project\.venv\lib\site-packages\asyncpg\pool.py", line 210, in release
    await self._con.reset(timeout=budget)
  File "C:\Users\User\Documents\Coding Projects\Project\.venv\lib\site-packages\asyncpg\connection.py", line 1366, in reset
    await self.execute(reset_query, timeout=timeout)
  File "C:\Users\User\Documents\Coding Projects\Project\.venv\lib\site-packages\asyncpg\connection.py", line 317, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg\protocol\protocol.pyx", line 323, in query
  File "asyncpg\protocol\protocol.pyx", line 707, in asyncpg.protocol.protocol.BaseProtocol._check_state
asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress

Is there a way to fix this issue or somekind of workaround?

[empicano]

Have a look at #695 (comment) and #258, they seem similar to your problem.

[l3keboy]

Hey @empicano! Thanks for the reply, I took a little break from the project but I am now working on it again, so sorry for the late response (and ping)! I looked into the mentioned solutions but I have the connection pool in place (or at least I think I have) but I keep getting the same issue. I am using this for a Discord bot, when I run the bot I create a databasehandler using the following code (The connect function is called outside of this class, but it is 100% called):

class DatabaseHandler:
    def __init__(self) -> None:
        pass

    # ------------------------------------------------------------------------- #
    # Connection methods #
    # ------------------------------------------------------------------------- #
    async def connect(self) -> None:
        """Initialize asyncpg pool"""
        try:
            self._pool = await asyncpg.create_pool(
                user=DB_USER,
                password=DB_PASSWORD,
                database=DB_DATABASENAME,
                host=DB_HOST,
                port=int(DB_PORT),
            )
            logging.getLogger(f"{BOT_NAME.lower()}.core.database").info(
                f"Succesfully created connection pool to database: {DB_HOST} - {DB_DATABASENAME}! "
                f"Signed in as user: {DB_USER}"
            )
        except Exception as e:
            logging.getLogger(f"{BOT_NAME.lower()}.core.database").error(
                f"Connection pool to database: {DB_HOST} - {DB_DATABASENAME} failed! Got error: {e}"
            )

This keeps resulting in the error above.
I can't seem to figure out what I am doing wrong or how I can fix this, if you have time, I appreciate the help!

edit: when interacting with the database, I do:

        async with self._pool.acquire() as conn:
            async with conn.transaction():
                await conn.execute() or await conn.fetch()