Asyncpg: cannot perform operation: another operation is in progress

[filantus]

• asyncpg version: 0.14.0
• PostgreSQL version: Postgres in docker, from official docker hub page. Version - PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?:
• Python version: 3.6.4
• Platform: linux debian
• Do you use pgbouncer?: no
• Did you install asyncpg with pip?: yes
• If you built asyncpg locally, which version of Cython did you use?:
• Can the issue be reproduced under both asyncio and
  uvloop?:

Hi! I want make db queries in different async tasks, but then i try, i got this:

Task exception was never retrieved
future: <Task finished coro=<worker() done, defined at /home/.../test.py:15> exception=InterfaceError('cannot perform operation: another operation is in progress',)>
Traceback (most recent call last):
  File "/home/.../test.py", line 18, in worker
    value = await con.fetchval(f'SELECT hash FROM images WHERE url = $1 LIMIT 1;', url)
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/connection.py", line 363, in fetchval
    data = await self._execute(query, args, 1, timeout)
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/connection.py", line 1287, in _execute
    with self._stmt_exclusive_section:
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/connection.py", line 1657, in __enter__
    'cannot perform operation: another operation is in progress')
asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress

Code to reproduce that:

import time
import asyncio
import asyncpg


DB_SETTINGS = {
    'host': 'localhost',
    'port': 5432,
    'database': 'postgres',
    'user': 'root',
    'password': '***',
}


async def worker(str):
    for i in range(4):
        url = f'{str}/{time.time()}'
        value = await con.fetchval(f'SELECT hash FROM images WHERE url = $1 LIMIT 1;', url)
        print(url, value)


if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    con = loop.run_until_complete(asyncpg.connect(**DB_SETTINGS))

    asyncio.async(worker('aaa'))
    asyncio.async(worker('bbb'))
    loop.run_forever()

Please, can you help?

[filantus]

I found solution - need use pool with many connections instead only one connection:

async def worker(str):
    for i in range(4):
        url = f'{str}/{time.time()}'

        async with db_pool.acquire() as conn:
            value = await conn.fetchval(f'SELECT hash FROM images WHERE url = $1 LIMIT 1;', url)
        print(url, value)


if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    db_pool = loop.run_until_complete(asyncpg.create_pool(**DB_SETTINGS))

    asyncio.async(worker('aaa'))
    asyncio.async(worker('bbb'))
    loop.run_forever()

Thank you! =)

[danieljfarrell]

Does this occur because the same connection is used to handle multiple transactions with the database?

[charterchap]

My issue with this same error message was due to the fact that you can't use the same pool in different event loops. I had been using a singleton to simplify using my connection pool. That was a bad idea. Now I am using 1 pool per loop with no issues.