It looks like for prepared statements, statements have to be in the form 'select $1 from $2 where baz=$3 with the arguments ('foo', 'bar', 'baz')

It is common in python database api's to support the pyformat int PEP 249. http://legacy.python.org/dev/peps/pep-0249/#paramstyle

Can this support at least one of the param styles in the pep? Preferably pyformat.

Ideally, the parameters could then be passed in as a dictionary instead of a ordered list.

conn.fetch(myquery, {bar='bar', foo='foo'})

• 13.0:
• 9.6.1:
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install? Yes, Heroku.:
• 3.6.3:
• Windows 10:
• Do you use pgbouncer? No:
• Did you install asyncpg with pip? Yes:

I was getting the same issue as #119 . I enabled the ssl=True in the await asyncpg.create_pool(self.dsn, ssl=True) - and the same occurs with connect.

So then I used the SSL keyword param, and got ssl.SSLError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:777).

Almost all explanation is written in the commit message.

The rest I want to mention is at least one feature is left untouched. It is the BaseCursor where the bind_execute and bind calls should be wrapped by the same way the bind_execute in the PreparedStatement.__bind_execute was, but I'd leave it for the other commit (may be by the other author).

• asyncpg version: 0.9.0, 0.10.1
• PostgreSQL version: 9.6
• Python version: 3.6
• Platform: FreeBSD 10.2
• Do you use pgbouncer?: ~no~ yes
• Did you install asyncpg with pip?: yes
• If you built asyncpg locally, which version of Cython did you use?: yes
• Can the issue be reproduced under both asyncio and uvloop?: yes

Hi!

We notice a problem that issue name describes pretty well. How did we get it?

We have a small aiohttp service which runs a static query to PostgreSQL database on every handled request. Like SELECT foo, bar FROM table ORDER BY baz. No parameters, nothing all. That's the only query we executre to database. We're using connection pool sized from 1 to 4 connections as well, the rest parameters are default (except connection one of course).

Everything works fine until eventually we starting get the InvalidSQLStatementNameError exception on connection.fetch call. It doesn't happens instantly, it occurs after few hours of service uptime. Sometimes every .fetch call ends with that exception. Sometimes every even only. The number of __asyncpg_stmt_ token is always different, but stable until restart when error occurs.

We tried to disable statements cache at all, set it to zero. This didn't happened. We thought that 0.10 release will fix that problem. Suddenly, it doesn't. We played around the cache size, connection pool size, connection life time, but all ended with the same issue. Difference may be in dozen of minutes, but all ends similar.

It's quite hard for me to provide any example to reproduce the problem because everything just works for a quite long time. Except that particular service, which isn't quite different, doesn't uses any hacks, globals, pretty trivial. Just no idea what to try next.

The last bits of the traceback are here:

      File "/usr/local/lib/python3.6/site-packages/asyncpg/connection.py", line 340, in fetch
        return await self._execute(query, args, 0, timeout)
      File "/usr/local/lib/python3.6/site-packages/asyncpg/connection.py", line 651, in _execute
        return await self._do_execute(query, executor, timeout)
      File "/usr/local/lib/python3.6/site-packages/asyncpg/connection.py", line 672, in _do_execute
        result = await executor(stmt, None)
      File "asyncpg/protocol/protocol.pyx", line 170, in bind_execute (asyncpg/protocol/protocol.c:57679)
    asyncpg.exceptions.InvalidSQLStatementNameError: prepared statement "__asyncpg_stmt_37__" does not exist

Any ideas how to debug this issue? Or workaround it? Would be grateful for any help.

Add session parameter to connection() with default value True. When set to False the client will limit usage of prepared statements to unnamed in a transaction only.

As a side effect handling of unnamed prepared statements improved irrespective of session value - they are automatically reprepared if something invalidating them occurs.

Refs #289, this is a rewrite of bind_execute_many():

• Batch bind args into 4 x 32KB buffers, sent with writelines() once flow control turns green.
• Send one SYNC at the very last - making use of the implicit transaction for the whole call to executemany().
• Abort immediately on either client error (with explicit rollback) or server error.
• ~Support async iterable.~
• Add executemany() to prepared statement.

• [x] test_server_failure_during_writes is failing on Windows
• [x] Fix unstable test_timeout (test_execute.TestExecuteMany)

This is a breaking change due to the implicit transaction.

The 3-level infinite loop keeps filling buffers and send them under flow control, the only exit is raising StopIteration with 3 possible values:

• True means there was data sent in the last loop
• False means the last loop sent nothing
• exception means the data source raised an exception

Please note, during the loop, if server-side error is detected before exhausting the data source, it is also considered as StopIteration(True), ending the process early with a SYNC_MESSAGE.

(I wrote a post about the details of implicit transaction.)

UPDATED - pgbench results of inserting 1000 rows per query with executemany() on Python 3.6 of 2.2GHz 2015 MacBook Air (best out of 5 runs):

asyncpg 0.18.2

710 queries in 30.31 seconds
Latency: min 341.88ms; max 636.29ms; mean 425.022ms; std: 39.782ms (9.36%)
Latency distribution: 25% under 401.67ms; 50% under 414.26ms; 75% under 435.37ms; 90% under 478.39ms; 99% under 576.638ms; 99.99% under 636.299ms
Queries/sec: 23.42
Rows/sec: 23424.32

This PR:

4125 queries in 30.02 seconds
Latency: min 23.14ms; max 734.91ms; mean 72.723ms; std: 49.226ms (67.69%)
Latency distribution: 25% under 59.958ms; 50% under 65.414ms; 75% under 71.538ms; 90% under 80.95ms; 99% under 175.375ms; 99.99% under 734.912ms
Queries/sec: 137.39
Rows/sec: 137389.64

Most branches are covered by tests, overall coverage 85% (1352 miss) -> 85% (1356 miss).

UPDATED - Python 3.8 of 2.3GHz 2020 MacBook Pro:

I'm trying to execute a couple of sql queries with arguments, in a context of a transaction.

I used this code:

 async def execute_many_in_transaction(self, queries_and_args):
    pool = await get_pool() # returns a pool of clients
    async with pool.acquire() as connection:
        async with connection.transaction():
            for query, args in queries_and_args:
                await connection.execute(
                    query, *args
                )

When I call it with a list with one tuple [("SQL STATEMENT", [arg1, arg2])] all is good.

The moment I try to execute more than one, it just hangs there forever. For example:

[
    ("SQL STATEMENT 1", [arg1, arg2]),
    ("SQL STATEMENT 2", [arg1, arg3]),
]

I know that there is execute_many method, but that only seems to work on the same sql statement with multiple lists of arguments.

Is there something I'm misunderstanding ?

Thanks !

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

Is it a good idea to combine all the args in executemany() into a ~single network packet~ single buffer to send (thx Yury) with a list of Bind and Execute command pairs? And, is it a good idea to make this method atomic? Please kindly give me some advices when time. Many thanks!

• Ends with only one Sync command, that means executemany() will be atomic in an implicit transaction, if not called from an existing transaction.
• This should reduce round-trip time to database back and forth, with a cost that ...
• memory usage is no longer smooth - it will have to encode all the bind args into memory before sending to database in a batch.
  • Theoretically it is possible to combine the args into small groups, in order for a balance between reduced RTT and memory usage, as well as to detect an error earlier.
• Assuming that reducing RTT is helpful, this RFC also added executemany() to prepared statement.

References:

• https://www.postgresql.org/docs/10/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY
• https://www.pgcon.org/2014/schedule/attachments/330_postgres-for-the-wire.pdf
• https://www.youtube.com/watch?v=qa22SouCr5E
• https://github.com/MagicStack/asyncpg/issues/36

• asyncpg version: 0.9.0
• PostgreSQL version: Cockroachdb beta-20170223
• Python version: Python 3.6.0
• Platform: latest OS X
• Do you use pgbouncer?: no
• Did you install asyncpg with pip?: yes
• If you built asyncpg locally, which version of Cython did you use?: 0.25.2
• Can the issue be reproduced under both asyncio and uvloop?: not related

I'm trying to use cockroachdb with asyncpg.

With plain connection it works like a charm. On the other hand using connection pool fails with the following error:

ERROR:sanic:Traceback (most recent call last):
  File "/Users/krisz/.pyenv/versions/miniconda3-latest/envs/ml3/lib/python3.6/site-packages/sanic/app.py", line 391, in handle_request
    response = await response
  File "/Users/krisz/Workspace/papi/papi/app.py", line 34, in accounts_create
    print(results)
  File "/Users/krisz/.pyenv/versions/miniconda3-latest/envs/ml3/lib/python3.6/site-packages/asyncpg/pool.py", line 257, in __aexit__
    await self.pool.release(con)
  File "/Users/krisz/.pyenv/versions/miniconda3-latest/envs/ml3/lib/python3.6/site-packages/asyncpg/pool.py", line 189, in release
    await connection.reset()
  File "/Users/krisz/.pyenv/versions/miniconda3-latest/envs/ml3/lib/python3.6/site-packages/asyncpg/connection.py", line 412, in reset
    ''')
  File "/Users/krisz/.pyenv/versions/miniconda3-latest/envs/ml3/lib/python3.6/site-packages/asyncpg/connection.py", line 171, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 255, in query (asyncpg/protocol/protocol.c:56799)
asyncpg.exceptions.InternalServerError: syntax error at or near "DO"

Caused by the resetting the connection :

await self.execute('''
            DO $$
            BEGIN
                PERFORM * FROM pg_listening_channels() LIMIT 1;
                IF FOUND THEN
                    UNLISTEN *;
                END IF;
            END;
            $$;
            SET SESSION AUTHORIZATION DEFAULT;
            RESET ALL;
            CLOSE ALL;
            SELECT pg_advisory_unlock_all();
        ''')

I don't know the exact solution, but I'd like to use Cockroach with asyncpg :)

I've created an issue for cockroach developers too https://github.com/cockroachdb/examples-orms/issues/19

Closes #547 and possibly #467

When wait_for is cancelled, there is a chance that the waited task has already been completed, leaving the connection looking like it is in use. This fix ensures that the connection is returned to the pool in this situation.

A similar bug was fixed in #468, also with wait_for. The python issue was https://bugs.python.org/issue37658, although this is arguably not a solvable bug in Python itself as it can't know how to undo a completed task.

• asyncpg version: 0.26.0
• PostgreSQL version: 12.0
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce : NO, from pg docker the issue with a local PostgreSQL install?: YES,
• Python version: Python 3.9.13
• Platform: MAC
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: From tortoise-orm
• If you built asyncpg locally, which version of Cython did you use?: No
• Can the issue be reproduced under both asyncio and uvloop?: YES

I am using Sanic + tortoise-orm,

the test code as below:

when runs a query during the Request handler, it runs successfully

while runs in a background task, it report this error:

asyncpg.exceptions._base.InterfaceError: cannot call Connection.fetch(): connection has been released back to the pool

I found when the first time query, through the pool.acquire, got the right <PoolConnectionProxy <asyncpg.connection.Connection object at 0x7fd42102f9e0> 0x7fd3d018e550>

but when go to the background task query , the pool.acquire, got an "released" <PoolConnectionProxy [released] 0x7fd3d018e550>

I am confused how can I got an released PoolConnectionProxy from the same code

[query in the get Request method]

[query in the background task]

[the pool connection acquire method]

[the pool connection release method]

Recreating this PR because it seems to have gone silent. The target is to implement the target_session_attribute keyword when connecting to a database cluster. Supported options are

• any
• primary
• standy
• prefer_standby
• read-write
• read-only

• 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?

Hello,

While trying to configure a Cockroach DB cluster async connection, there seems to be missing support for usual PostgreSQL parameters (or renamed) or supported options not frequently used.

I don't know why the connection part does not seems to follow other library but it would be nice to at least support all options existing in postgresql client (even renamed ^^).

For example, the part I am missing to connect is options keyword. It is used to provide complementary informations on the connection (in my case a cluster id to identify exact connection). Guess it is not mandatory by itself but it fells strange to have the issue only on asyncpg :/

• asyncpg version: Version: 0.26.0
• PostgreSQL version:
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: Cockroach Cloud
• Python version: 3.10.7 (venv)
• Platform: Linux (Docker VSCode python dev container)
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes (using setuptools)
• If you built asyncpg locally, which version of Cython did you use?:
• Can the issue be reproduced under both asyncio and uvloop?:

• asyncpg version: 0.25.0
• PostgreSQL version: 12.11
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?:
• Python version: 3.9
• Platform: Linux
• 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?: NA

Receiving this error

asyncio.exceptions.TimeoutError
[View similar errors](https://link.datadoghq.com/apm/error-tracking?issueId=...471-da7ad0900002)

Traceback (most recent call last):
  File /usr/local/lib/python3.9/site-packages/ddtrace/contrib/asyncpg/patch.py, line 89, in _traced_query
    return await method(*args, **kwargs)
File asyncpg/protocol/protocol.pyx, line 338, in query
asyncio.exceptions.TimeoutError

Due to internal query call: SELECT pg_advisory_unlock_all ( ) CLOSE ALL UNLISTEN * RESET ALL

Happening quite frequently

• asyncpg version: 0.26.0
• PostgreSQL version: Google AlloyDB, postgres 14.4 compatible
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: I cannot reproduce the issue on a local postgres, no.
• Python version: 3.10
• Platform: Macos
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes
• If you built asyncpg locally, which version of Cython did you use?: NA
• Can the issue be reproduced under both asyncio and uvloop?: I have not tried to reproduce under uvloop

I am connecting to an AlloyDB instance on Google Cloud (currently, these are available for free testing). I can connect and queries run as expected. However, connection.disconnect() hangs. I cannot reproduce this behavior locally; disconnection happens as expected. Synchronous database work with psycopg2, for example, works as expected.

This code reproduces the behavior I am seeing (running through the alloydb-auth-proxy as is required):

import asyncpg
conn = await asyncpg.connect(ALLOYDB_URI)
conn.disconnect() # hangs

I'll put in a ticket with Google as well if nothing obvious turns up here.