Trouble in pooled connection with SQLAlchemy
I'm trying to use a pooled connection with SQLAlchemy 2.0 but it keeps pointing that i need to disable the prepared statement cache size, which i already did. Anyone know what im doing wrong??
The error:
My code:
Edit:
My Requirements:
The error:
`
sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.DuplicatePreparedStatementError'>: prepared statement "__asyncpg_stmt_16__" already exists
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.`
sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.DuplicatePreparedStatementError'>: prepared statement "__asyncpg_stmt_16__" already exists
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.My code:
class Postgres(metaclass=Singleton):
def __init__(self) -> None:
DATABASE_URL = "postgresql+asyncpg://user:password@pooled-host.neon.tech/neondb?prepared_statement_cache_size=0"
self.engine = create_async_engine(
DATABASE_URL
)
async def connect(self):
async with self.engine.begin() as conn:
await conn.run_sync(metadata.create_all)
async with self.engine.connect() as conn:
await conn.execution_options(compiled_cache=None)class Postgres(metaclass=Singleton):
def __init__(self) -> None:
DATABASE_URL = "postgresql+asyncpg://user:password@pooled-host.neon.tech/neondb?prepared_statement_cache_size=0"
self.engine = create_async_engine(
DATABASE_URL
)
async def connect(self):
async with self.engine.begin() as conn:
await conn.run_sync(metadata.create_all)
async with self.engine.connect() as conn:
await conn.execution_options(compiled_cache=None)Edit:
My Requirements:
asyncpg==0.29.0
SQLAlchemy==2.0.23
fastapi==0.95.0
gunicorn==21.2.0
uvicorn==0.23.2asyncpg==0.29.0
SQLAlchemy==2.0.23
fastapi==0.95.0
gunicorn==21.2.0
uvicorn==0.23.2
