Question 250452 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Disabling cache in asyncpg

I want to disable cache in asyncpg+postgresql to get timely results from the database and not outdated ones. However, I encounter an issue that doesn’t allow me to do so:

RuntimeWarning: coroutine 'AsyncConnection.execution_options' was never awaited
  async with engine.connect().execution_options(compiled_cache=None) 
RuntimeWarning: Enable tracemalloc to get the object allocation traceback

My code is the following:

...
query = select(CardBase).where(CardBase.user_id==str(message.from_user.id))
current_cards = await retrieve_data_from_db(query, engine)
current_cards = current_cards.fetchone()
...

retrieve_data_from_db:

async def retrieve_data_from_db(query, engine):

""" Connects to the database and returns the results by a given query """

    async with engine.connect().execution_options(compiled_cache=None) 
        as conn:
        try:
            executed_query = await conn.execute(query)
            await conn.commit()
        except SQLAlchemyError as exc:
            print(exc)
            raise
        finally:
            await conn.close()
        return executed_query

Everything is awaited, I don’t get what the problem is. So, how to write cache disabling using asyncpg+postgresql properly? Stating cache size in engine results in nothing.

engine = create_async_engine(f'postgresql+asyncpg://{username}:{password}'
                   f'@{host}/{database}?prepared_statement_cache_size=0',
                   pool_pre_ping=True,
                   echo=True,
                   )
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    As @snakecharmerb pointed out in our discussion, the proper way of disabling cache is the following:

    engine = create_async_engine(
"postgresql+asyncpg://souser:password@localhost/test",
echo=True,
query_cache_size=0

    AND

    engine.execution_options(compiled_cache=None)

    (As I figured out, seems to work both with session.begin() and engine.connect())


  2. 0

    To disable the the cache in sqlachemy properly when using asyncpg, you need to specify the compiled_cache option in the execution_options, try modifying your code;

    async with engine.connect() as conn:
    try:
        executed_query = await conn.execution_options(compiled_cache=None).execute(query)
        await conn.commit()
    except SQLAlchemyError as exc:
        print(exc)
        raise
    finally:
        await conn.close()
    return executed_query
    Login or Signup to reply.
  3. 0

    While SQLAlchemy does not maintain a cache of results, it does maintain a cache of compiled SQL statements. This is to prevent SQLAlchemy having to repeatedly convert the same SQLAlchemy statements into strings. It is this statement caching that emits messages like these when cache entries are created or retrieved:

    INFO:sqlalchemy.engine.Engine:[generated in 0.00012s] {}
INFO:sqlalchemy.engine.Engine:[cached since 0.0008133s ago] {}

    The size of the statement cache can be configured when the engine is created; setting it to zero disables statement caching in SQLALchemy globally.

    create_engine('postgresql+psycopg2:///test', query_cache_size=0)

    Log output like this will then be emitted:

    INFO:sqlalchemy.engine.Engine:[caching disabled 0.00008s] {}

    A statement cache may also be defined at the connection level, and setting it to None will prevent statement caching for that connection checkout.

    with engine.connect().execution_options(compiled_cache=None) as conn:
    conn.execute(sa.select(users))

    If using an async engine, the execution options must be awaited separately to avoid the error in the question:

    async with engine.connect() as conn:
    await conn.execution_options(compiled_cache=None)

    In this particular case, there is yet another statement cache to consider – that maintained by asyncpg. It can be disabled like this, according to the SQLAlchemy docs:

    engine = create_async_engine("postgresql+asyncpg://user:pass@hostname/dbname?prepared_statement_cache_size=0")
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search