asyncpg.exceptions.DuplicatePreparedStatementError: prepared statement “stmt_5” already exists

[johannspies]

• asyncpg version:
  0.8.4-1
• PostgreSQL version:

9.6

• Python version:

3.5.3-1

• Platform:

LInux. Debian

• Do you use pgbouncer?:

No

• Did you install asyncpg with pip?:

No

• If you built asyncpg locally, which version of Cython did you use?:
• Can the issue be reproduced under both asyncio and
  uvloop?:

No. It is too complicated.

0
down vote
favorite

The function "runquery" gets called from different parts of a program. I never use "prepare" in my query statements in this case. I have read all the other questions about "prepared statement already exists" and have tried "DEALLOCATE ALL" also. But that results in the opposite error: while the error below complains that the prepared statement already exists, DEALLOCATE ALL results in a complaint that it does not exist.

This is my first attempt to run this type of program using asyncpg.

After many queries as shown below, runquery ends with an error reporting:

Could not complete query "select uuid from wos_2017_1.combined_name where combined_name = 'xxxxxxx';"

Traceback (most recent call last):
  File "update2017.py", line 1206, in runquery
    result = await con.fetch(query)
  File "/usr/lib/python3/dist-packages/asyncpg/connection.py", line 268, in fetch
    stmt = await self._get_statement(query, timeout)
  File "/usr/lib/python3/dist-packages/asyncpg/connection.py", line 212, in _get_statement
    state = await protocol.prepare(None, query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 140, in prepare      (asyncpg/protocol/protocol.c:55210)
  File "/usr/lib/python3.5/asyncio/futures.py", line 380, in __iter__
yield self  # This tells Task to wait for completion.
  File "/usr/lib/python3.5/asyncio/tasks.py", line 304, in _wakeup
    future.result() 
  File "/usr/lib/python3.5/asyncio/futures.py", line 293, in result
raise self._exception
asyncpg.exceptions.DuplicatePreparedStatementError: prepared statement "stmt_7" already exists

$ grep -c  INSERT /tmp/y.log
1006
$ grep -c  SELECT /tmp/y.log
1364
$ grep -ci  UPDATE /tmp/y.log
1044
$ grep -ci  delete /tmp/y.log
2548

    import asyncio,asyncpg

    async def make_pool():
         """Create asyncpg connection pool to database"""

         pool = await asyncpg.create_pool(database='wos',
                                 host = 'localhost',
                                 user = 'xx',
                                 password='xxxxxx',


                     port=5432,
                                 min_size=10,
                                 max_size=50)

         return pool

    async def get_con(pool):
        con = await pool.acquire()
        return con

    async def runquery(query):
        con = await get_con(pool)
        try:
            if query.startswith('delete from') or query.startswith('insert'):
                result = await con.execute(query)
            else:
                result = await con.fetch(query)
        except:
            print('Could not complete query "{}"'.format(query))
            print(traceback.format_exc())
            result = None
            exit(1)
        finally:
            await pool.release(con)
        return result, success

[elprans]

Please try to reproduce with 0.11.0.

[Reskov]

Hi @elprans, I'm facing the same issue 0.12.0, in my case problem is in lock I suppose,
https://github.com/MagicStack/asyncpg/blob/master/asyncpg/connection.py#L285 , to my mind lock is need in _get_statement function, context may switch before cache is set

[1st1]

@Reskov Can you reproduce the bug in code that you can share with us? I don't see how lock can help there.

[Reskov]

@1st1 Yes, you are right, already have _stmt_exclusive_section, my problem was something like pgbouncer, same pg connection between application sessions, sorry for the misunderstanding.

[kxepal]

@Reskov @johannspies
Try to upgrade to 0.13 release. We had similar issue, but that release fixed it for us.