sqlalchemy.dialects.postgresql.asyncpg.InterfaceError - cannot perform operation: another operation is in progress

asyncpg version: 0.25.0
PostgreSQL version: 13
Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: I’m using local PostgreSQL
Python version: 3.8.0
Platform: Linux
Do you use pgbouncer?: no
Did you install asyncpg with pip?: No, poetry
If you built asyncpg locally, which version of Cython did you use?:
Can the issue be reproduced under both asyncio and uvloop?:

I’m using async sqlalchemy with postgresql+asyncpg.

I’ve created a FastAPI.

Here’s my code snippet.

DATABASE_URL = (
    f"postgresql+asyncpg://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
)

metadata = MetaData()
Base = declarative_base()
async_engine = create_async_engine(DATABASE_URL, pool_size=20)
async_session = AsyncSession(async_engine)

async with async_session as session:
    query = (
        nemo_user_analytics.select()
        .with_only_columns(
            [
                func.to_char(nemo_user_analytics.c.full_date, "Mon DD").label(
                    "weekday"
                ),
                func.sum(nemo_user_analytics.c.duration).label("total_count"),
            ]
        )
        .where(
            and_(
                nemo_user_analytics.c.full_date > seven_day_interval_before,
                nemo_user_analytics.c.google_id == google_id,
            )
        )
        .group_by("weekday")
        .order_by("weekday")
    )
    result = await session.execute(query)
    result = result.fetchall()
    return result

This works fine when only one request is called. But simultaneously calling the API results in this error

sqlalchemy.exc.InterfaceError: (sqlalchemy.dialects.postgresql.asyncpg.InterfaceError) <class 'asyncpg.exceptions._base.InterfaceError'>: cannot perform operation: another operation is in progress

I’m using different session for each request with

 async with async_session as session:

But it still doesn’t work.

Issue Analytics

State:
Created 2 years ago
Comments:9 (1 by maintainers)

Top GitHub Comments

1reaction

sk-commented, Aug 27, 2022

We were hit by this issue as well and the reason was due to pytest creating a different event loop for each test. By forcing the same event loop for all tests (see https://github.com/pytest-dev/pytest-asyncio/issues/38#issuecomment-264418154) we were able to fix the problem.

Note though, that the problem does not happen when one uses a NullPool.

@zzzeek do you have any ideas how could we solve this without forcing a unique event loop?

Below is the code that can replicate the issue

from sqlalchemy.sql import text
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.pool import NullPool

engine = create_async_engine(
    "postgresql+asyncpg://user:password@localhost:5432/db_name", future=True#, poolclass=NullPool
)


async def test_echo():
    async with engine.begin() as conn:
        r = await conn.execute(text("SELECT 'hello' as echo"))
        assert r.scalar() == "hello"

async def test_echo2():
    async with engine.begin() as conn:
        r = await conn.execute(text("SELECT 'hello' as echo"))
        assert r.scalar() == "hello"

async def test_echo3():
    async with engine.begin() as conn:
        r = await conn.execute(text("SELECT 'hello' as echo"))
        assert r.scalar() == "hello"

1reaction

zzzeekcommented, Jan 3, 2022

this usually is someone using the same AsyncSession object in more than one async task at a time, not any different from if they did this with the asyncpg connection directly.

Top Results From Across the Web

asyncpg - another operation is in progress - Stack Overflow

I am attempting to resolve the following error: asyncpg.exceptions._base.InterfaceError: cannot perform operation ...

r/Python - SQLAlchemy with asyncpg cannot perform operation

InterfaceError : (sqlalchemy.dialects.postgresql.asyncpg. ... InterfaceError'>: cannot perform operation: another operation is in progress.

How to use the asyncpg.exceptions.UniqueViolationError ...

InterfaceError as ex: if "another operation is in progress" in ex.args[0]: raise ConflictError( f"asyncpg error, another operation in progress.

Source code for asyncpg.connection

Specifically, we want to make the following # operation atomic: ... async def run(): ... con = await asyncpg.connect(user='postgres') ... result = await ......

Pytest+FastAPI+SQLAlchemy+Postgres InterfaceError

The basic error is sqlalchemy.exc.InterfaceError('cannot perform operation: another operation is in progress') . This may be related to the app/DB ...