asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress

[yjdwbj]

Hi, here is my code ,please help resovle the problem.thanks a lot.

from sanic import Sanic
from sanic.response import json
import asyncpg
import asyncio
import uvloop
loop = uvloop.new_event_loop()
app = Sanic()
app.debug = True

async def initdb_pool():
    dbdict = {"database":"mqtt","user":"mqtt","password":"mqtt123",
            "host":"192.168.25.100","port":5433}
    return await asyncpg.create_pool(**dbdict)

@app.route("/iot/v1.0/app/auth/<user:[A-z0-9]\w+>/<pwd:[A-z0-9]\w+>/")
async def applogin(request,user,pwd):
    async with engine.acquire() as connection:
        #async with connection.transaction():
        stmt   = await connection.prepare('select key from user_manager_appuser where uname = $1 or email = $2 or phone = $3')
        result =  await stmt.fetchval(user,user,user)
        #    result = await connection.fetchval('select key from user_manager_appuser where uname = $1 or email = $2 or phone = $3',
        #                                        user,user,user)
        if not result:
            return json({'ok':False,'err':'Pwd error'})
        print("result is ",result)
        return json({'ok':True,'data':str(result)})

if __name__ == "__main__":
    engine = loop.run_until_complete(initdb_pool())
    app.run(host="0.0.0.0",port=8000,debug=True)

---

 curl "http://127.0.0.1:8000/iot/v1.0/app/auth/abc/123456/"
Error: cannot perform operation: another operation is in progress
Exception: Traceback (most recent call last):
  File "/media/yjdwbj/E/py360dev/lib/python3.6/site-packages/sanic/sanic.py", line 194, in handle_request
    response = await response
  File "/opt/python360/lib/python3.6/asyncio/coroutines.py", line 128, in throw
    return self.gen.throw(type, value, traceback)
  File "main.py", line 32, in applogin
    return json({'ok':True,'data':str(result)})
  File "/opt/python360/lib/python3.6/asyncio/coroutines.py", line 109, in __next__
    return self.gen.send(None)
  File "/media/yjdwbj/E/py360dev/lib/python3.6/site-packages/asyncpg/pool.py", line 252, in __aexit__
    await self.pool.release(con)
  File "/opt/python360/lib/python3.6/asyncio/coroutines.py", line 109, in __next__
    return self.gen.send(None)
  File "/media/yjdwbj/E/py360dev/lib/python3.6/site-packages/asyncpg/pool.py", line 184, in release
    await connection.reset()
  File "/opt/python360/lib/python3.6/asyncio/coroutines.py", line 109, in __next__
    return self.gen.send(None)
  File "/media/yjdwbj/E/py360dev/lib/python3.6/site-packages/asyncpg/connection.py", line 411, in reset
    ''')
  File "/opt/python360/lib/python3.6/asyncio/coroutines.py", line 109, in __next__
    return self.gen.send(None)
  File "/media/yjdwbj/E/py360dev/lib/python3.6/site-packages/asyncpg/connection.py", line 170, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 247, in query (asyncpg/protocol/protocol.c:51830)
  File "asyncpg/protocol/protocol.pyx", line 352, in asyncpg.protocol.protocol.BaseProtocol._ensure_clear_state (asyncpg/protocol/protocol.c:54136)
asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress

[elprans]

I'm not sure your snippet is complete. Where does engine come from? Are you using asyncpgsa, if so, do you get the same result by using asyncpg directly?

Thanks.

[yjdwbj]

Hi, this code is simple clearly, and it was connected database, just use asyncpg to create_pool .

if __name__ == "__main__":
    engine = loop.run_until_complete(initdb_pool())
    app.run(host="0.0.0.0",port=8000,debug=True)

[jbcurtin]

@yjdwbj, you'll have to pass the loop into sanic manually.

if __name__ == "__main__":
    engine = loop.run_until_complete(initdb_pool())
    app.run(host="0.0.0.0",port=8000, debug=True, loop=loop)

[cctse]

@yjdwbj why closed？I met the same problem

[yjdwbj]

@cctse Because the authors not care this bugs.I found openresty instead of it.

[cctse]

@elprans please reopen this issue, full code as follows

from sanic import Sanic
from sanic.response import text
import asyncio
import uvloop
import asyncpg


asyncio.set_event_loop_policy(uvloop.EventLoopPolicy())
loop = asyncio.get_event_loop()


app = Sanic(__name__)

async def init_pgpool():
    return await asyncpg.create_pool('postgres://postgres:ssdd@localhost:5433/postgres')

pgpool = loop.run_until_complete(init_pgpool())


@app.route('/')
async def index(req):
    # async with asyncpg.create_pool('postgres://postgres:ssdd@localhost:5433/tmp') as pgpool:   # this works fine
    async with pgpool.acquire() as conn:
        rs = await conn.fetchval('select 1')
        print('----', rs)
    return text('done')

app.run(port=8084)

error when curl http://127.0.0.1:8084

ERROR: Traceback (most recent call last):
  File "/usr/local/var/pyenv/versions/anaconda3-2.5.0/lib/python3.5/site-packages/sanic/sanic.py", line 329, in handle_request
    response = await response
  File "/Users/akc/project/py/tsweb/sweb.py", line 25, in index
    print('----', rs)
  File "/usr/local/var/pyenv/versions/anaconda3-2.5.0/lib/python3.5/site-packages/asyncpg/pool.py", line 252, in __aexit__
    await self.pool.release(con)
  File "/usr/local/var/pyenv/versions/anaconda3-2.5.0/lib/python3.5/site-packages/asyncpg/pool.py", line 184, in release
    await connection.reset()
  File "/usr/local/var/pyenv/versions/anaconda3-2.5.0/lib/python3.5/site-packages/asyncpg/connection.py", line 411, in reset
    ''')
  File "/usr/local/var/pyenv/versions/anaconda3-2.5.0/lib/python3.5/site-packages/asyncpg/connection.py", line 170, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 249, in query (asyncpg/protocol/protocol.c:57280)
  File "asyncpg/protocol/protocol.pyx", line 354, in asyncpg.protocol.protocol.BaseProtocol._ensure_clear_state (asyncpg/protocol/protocol.c:59586)
asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress

[amsb]

@cctse what version of sanic are you using? It still looks like asyncpg and sanic are running a different loops.

[cctse]

@amsb
sanic 0.3.1
I think i found the problem, sanic tries to create the loop https://github.com/channelcat/sanic/blob/master/sanic/server.py#L295

[seemethere]

Hey guys, I'm one of the maintainers of Sanic. Is there no way to pass a loop to asyncpg? You could use a server event to pass the loop sanic creates to asyncpg and establish the db connection like so.

[seemethere]

We even have an asyncpg example on our main repo: https://github.com/channelcat/sanic/blob/master/examples/sanic_asyncpg_example.py

[elprans]

Hey guys, I'm one of the maintainers of Sanic. Is there no way to pass a loop to asyncpg?

Both create_pool and Connection accept loop as argument.

[seemethere]

That's what I thought. So it's really a non-issue here. Just a confusion on implementation.

[jbcurtin]

Hey @seemethere, there was a time where Sanic supported passing a loop into the run command. After doing so searching, I've found that @1st1 has commented in Sanics issues about a common issue.

The problem maybe lies in the uvloop library?

There were a couple things I ran into while trying to utilize Sanic and asyncPG. You'll have to manually pass in a loop into Sanic. The problem being Sanic pulls a new loop out from asyncio. asyncio in turn calls get_event_loop_policy. uvloop as the default policy, returns a new event_loop while asyncio would return the a running event_loop