Using statement_cache_size asyncpg setting / prepared statement name for asyncpg w pgbouncer

Hi!

I use sqlalchemy 1.4 with asyncpg driver with pgbouncer.


    from sqlalchemy.ext.asyncio import create_async_engine
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy.ext.asyncio import AsyncSession

    engine = create_async_engine(
        f'postgresql+asyncpg://{username}:{password}@{host}:{port}/{dbname}',
        echo=False,
    )
    session_maker = sessionmaker(
        engine,
        class_=AsyncSession,
    )

I have an error:

asyncpg.exceptions.DuplicatePreparedStatementError: prepared statement "__asyncpg_stmt_a__" 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.

How i can pass this setting (statement_cache_size=0) to asyncpg connection object?

Issue Analytics

State:
Created 2 years ago
Reactions:9
Comments:48 (30 by maintainers)

Top GitHub Comments

5reactions

jacksundcommented, Jul 18, 2022

I do have a working setup in transaction mode using https://github.com/sqlalchemy/sqlalchemy/issues/6467#issuecomment-864943824 though:

from uuid import uuid4
from asyncpg import Connection

class CConnection(Connection):
    def _get_unique_id(self, prefix: str) -> str:
        return f"__asyncpg_{prefix}_{uuid4()}__"

engine = create_async_engine(
    "postgresql+asyncpg://scott:tiger@localhost/test",
    echo=True,
    connect_args={
        "statement_cache_size": 0,
        "prepared_statement_cache_size": 0,
        "connection_class": CConnection,
    },
)

5reactions

SlavaSkvortsovcommented, Jun 21, 2021

We had a similar problem due to multiple web workers. They generated prepared statements with the same names - original function to generate IDs looks like this:

    def _get_unique_id(self, prefix):
        global _uid
        _uid += 1
        return '__asyncpg_{}_{:x}__'.format(prefix, _uid)

So we just changed the Connection class a bit

from uuid import uuid4

from asyncpg import Connection


class CConnection(Connection):
    def _get_unique_id(self, prefix: str) -> str:
        return f'__asyncpg_{prefix}_{uuid4()}__'

You need to provide it when you create the engine

engine = create_async_engine(
    settings.database_url,
    connect_args={
        'connection_class': CConnection,
    },
)

Top Results From Across the Web

Frequently Asked Questions — asyncpg Documentation

To create a cursor usable outside of a transaction, use the DECLARE ... CURSOR WITH HOLD SQL statement directly. Why am I getting...

getting a "prepared statement error" in asyncpg - Stack Overflow

I am trying work with some data from a PostgreSQL server,by using Connection Pools. There's a caveat mentioning this issue and it's ...

1M rows/s from Postgres to Python - magicstack

The most obvious option was psycopg2—the most popular Python driver for ... asyncpg extensively uses PostgreSQL prepared statements.

Getting A "Prepared Statement Error" In Asyncpg - ADocLib

When used with UPDATE and DELETE statements, it's possible to retrieve values of and to handle database errors. sakr@ulb. asyncpg is written almost...

How can I disable prepared statements for using pgbouncer ...

I want to use pgbouncer in transaction pool mode. To do this you cannot use prepared statements. I set the JDBC PSQL connection...