PGDialect_asyncpg.do_ping occasionally uses prepared statements #10226
Description
Describe the bug
I upgraded from 1.4 to 2.0 and noticed a problem with connecting to PgBouncer in transactional mode.
The problem occurs when engine configured to use asyncpg with pool_pre_ping = True.
After a small lookup I found the commit that brings this problem 2e302b2
This is how temporary working solution can look like:
from sqlalchemy.dialects.postgresql.asyncpg import PGDialect_asyncpg
del PGDialect_asyncpg.do_pingOptional link from https://docs.sqlalchemy.org which documents the behavior that is expected
No response
SQLAlchemy Version in Use
2.0.19
DBAPI (i.e. the database driver)
asyncpg
Database Vendor and Major Version
PostgreSQL 14
Python Version
3.10
Operating system
OSX, Linux
To Reproduce
import asyncio
import uuid
from sqlalchemy import text
from sqlalchemy.ext.asyncio import (
AsyncSession,
create_async_engine,
)
async def main():
dsn = 'postgresql+asyncpg://postgresql:[email protected]:5432/test'
engine = create_async_engine(
dsn,
future=True,
echo=True,
connect_args={
'prepared_statement_name_func': lambda: f'__asyncpg_{uuid.uuid4()}__',
'statement_cache_size': 0,
'prepared_statement_cache_size': 0,
},
pool_pre_ping=True,
)
async with AsyncSession(bind=engine) as session:
await session.execute(text('SELECT 1'))
async with AsyncSession(bind=engine) as session:
await session.execute(text('SELECT 1'))
if __name__ == '__main__':
asyncio.run(main())Error
2023-08-11 17:25:53,739 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2023-08-11 17:25:53,740 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-08-11 17:25:54,010 INFO sqlalchemy.engine.Engine select current_schema()
2023-08-11 17:25:54,010 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-08-11 17:25:54,416 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2023-08-11 17:25:54,416 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-08-11 17:25:54,689 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-08-11 17:25:54,689 INFO sqlalchemy.engine.Engine SELECT 1
2023-08-11 17:25:54,689 INFO sqlalchemy.engine.Engine [generated in 0.00016s] ()
2023-08-11 17:25:54,952 INFO sqlalchemy.engine.Engine ROLLBACK
Traceback (most recent call last):
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 821, in ping
_ = self.await_(self._connection.fetchrow(";"))
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 125, in await_only
return current.driver.switch(awaitable) # type: ignore[no-any-return]
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 185, in greenlet_spawn
value = await result
File "/user/test/venv/lib/python3.10/site-packages/asyncpg/connection.py", line 678, in fetchrow
data = await self._execute(
File "/user/test/venv/lib/python3.10/site-packages/asyncpg/connection.py", line 1658, in _execute
result, _ = await self.__execute(
File "/user/test/venv/lib/python3.10/site-packages/asyncpg/connection.py", line 1683, in __execute
return await self._do_execute(
File "/user/test/venv/lib/python3.10/site-packages/asyncpg/connection.py", line 1730, in _do_execute
result = await executor(stmt, None)
File "asyncpg/protocol/protocol.pyx", line 201, in bind_execute
asyncpg.exceptions.InvalidSQLStatementNameError: unnamed prepared statement does not exist
HINT:
NOTE: pgbouncer with pool_mode set to "transaction" or
"statement" does not support prepared statements properly.
You have two options:
* if you are using pgbouncer for connection pooling to a
single server, switch to the connection pool functionality
provided by asyncpg, it is a much better option for this
purpose;
* if you have no option of avoiding the use of pgbouncer,
then you can set statement_cache_size to 0 when creating
the asyncpg connection object.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
self._dbapi_connection = engine.raw_connection()
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3288, in raw_connection
return self.pool.connect()
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 452, in connect
return _ConnectionFairy._checkout(self)
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1366, in _checkout
with util.safe_reraise():
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1304, in _checkout
result = pool._dialect._do_ping_w_event(
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 708, in _do_ping_w_event
return self.do_ping(dbapi_connection)
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 1117, in do_ping
dbapi_connection.ping()
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 823, in ping
self._handle_exception(error)
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 802, in _handle_exception
raise translated_error from error
sqlalchemy.dialects.postgresql.asyncpg.AsyncAdapt_asyncpg_dbapi.Error: <class 'asyncpg.exceptions.InvalidSQLStatementNameError'>: unnamed prepared statement does not exist
HINT:
NOTE: pgbouncer with pool_mode set to "transaction" or
"statement" does not support prepared statements properly.
You have two options:
* if you are using pgbouncer for connection pooling to a
single server, switch to the connection pool functionality
provided by asyncpg, it is a much better option for this
purpose;
* if you have no option of avoiding the use of pgbouncer,
then you can set statement_cache_size to 0 when creating
the asyncpg connection object.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/user/test//script.py", line 32, in <module>
asyncio.run(main())
File "/user/.pyenv/versions/3.10.3/lib/python3.10/asyncio/runners.py", line 44, in run
return loop.run_until_complete(main)
File "/user/.pyenv/versions/3.10.3/lib/python3.10/asyncio/base_events.py", line 646, in run_until_complete
return future.result()
File "/user/test//script.py", line 28, in main
await session.execute(text('SELECT 1'))
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/ext/asyncio/session.py", line 439, in execute
result = await greenlet_spawn(
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 190, in greenlet_spawn
result = context.throw(*sys.exc_info())
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2246, in execute
return self._execute_internal(
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2131, in _execute_internal
conn = self._connection_for_bind(bind)
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1998, in _connection_for_bind
return trans._connection_for_bind(engine, execution_options)
File "<string>", line 2, in _connection_for_bind
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
ret_value = fn(self, *arg, **kw)
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1123, in _connection_for_bind
conn = bind.connect()
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3264, in connect
return self._connection_cls(self)
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 147, in __init__
Connection._handle_dbapi_exception_noconnection(
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2426, in _handle_dbapi_exception_noconnection
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
self._dbapi_connection = engine.raw_connection()
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3288, in raw_connection
return self.pool.connect()
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 452, in connect
return _ConnectionFairy._checkout(self)
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1366, in _checkout
with util.safe_reraise():
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1304, in _checkout
result = pool._dialect._do_ping_w_event(
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 708, in _do_ping_w_event
return self.do_ping(dbapi_connection)
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 1117, in do_ping
dbapi_connection.ping()
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 823, in ping
self._handle_exception(error)
File "/user/test/venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 802, in _handle_exception
raise translated_error from error
sqlalchemy.exc.DBAPIError: (sqlalchemy.dialects.postgresql.asyncpg.Error) <class 'asyncpg.exceptions.InvalidSQLStatementNameError'>: unnamed prepared statement does not exist
HINT:
NOTE: pgbouncer with pool_mode set to "transaction" or
"statement" does not support prepared statements properly.
You have two options:
* if you are using pgbouncer for connection pooling to a
single server, switch to the connection pool functionality
provided by asyncpg, it is a much better option for this
purpose;
* if you have no option of avoiding the use of pgbouncer,
then you can set statement_cache_size to 0 when creating
the asyncpg connection object.
(Background on this error at: https://sqlalche.me/e/20/dbapi)
Additional context
No response