Concurrent queries on single connection

[cosmos-97]

• asyncpg version: 0.22.0
• PostgreSQL version: 13.2
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: local PostgreSQL install
• Python version: 3.9
• Platform: centos
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes
• If you built asyncpg locally, which version of Cython did you use?:
• Can the issue be reproduced under both asyncio and uvloop?: uvloop

from asyncpg.exceptions import InterfaceError


class CursorIterator:
    def __init__(self, connection, prefetch, portal_name):
        if prefetch <= 0:
            raise InterfaceError("prefetch argument must be greater than zero")

        self._connection = connection
        self._prefetch = prefetch
        self._portal_name = portal_name
        self.rows = []

    def __aiter__(self):
        return self

    async def __anext__(self):
        if not self.rows:
            self.rows = await self._connection.fetch(
                f"FETCH {self._prefetch} FROM {self._portal_name}"
            )

        if self.rows:
            return self.rows.pop(0)

        raise StopAsyncIteration


class Cursor:
    def __init__(self, connection, query, *args, prefetch=None):
        self._connection = connection
        self._args = args
        self._prefetch = prefetch
        self._query = query
        self._portal_name = connection._get_unique_id("portal")

    async def __aenter__(self):
        await self._connection.execute(
            f"DECLARE {self._portal_name} NO SCROLL CURSOR WITH HOLD FOR {self._query}",
            *self._args,
        )

        prefetch = 50 if self._prefetch is None else self._prefetch
        return CursorIterator(self._connection, prefetch, self._portal_name)

    async def __aexit__(self, *args):
        await self._connection.execute(f"CLOSE {self._portal_name}")

Several queries are run concurrently and I receive the following error. What can I do to fix the problem ?

File "asyncpg/protocol/protocol.pyx", line 321, in query
  File "asyncpg/protocol/protocol.pyx", line 684, in asyncpg.protocol.protocol.BaseProtocol._check_state
asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress

[pauldraper]

You...can't run multiple queries concurrently on a single PostgreSQL connection. It's inherent to the protocol.

[alhashash]

You...can't run multiple queries concurrently on a single PostgreSQL connection. It's inherent to the protocol.

The protocol does not allow parallel queries but "concurrent" queries could be queued. I'd expect asyncpg to wait for the "other operation" instead of raising that exception.

I think it is a very important feature to allow coroutines to run in a single transaction. For example, to fulfill an order, a coroutine is started for each order line, then, after gathering all routines the order status will be updated and transaction get committed. Without running in a single transaction, complex two phase commit would be required.

Of course there would be no benefit of the whole processing is done in SQL, but if the load is both SQL and application, some performance and parallelism would be gained.

@pauldraper

[elprans]

If you want proper concurrency, you should use a connection pool. The decision to crash loudly rather than "waiting" on concurrent queries to the same connection is deliberate. See https://github.com/MagicStack/asyncpg/pull/367 for a previous discussion.

[pablo-sumup]

I am getting the same error with a SELECT * FROM table; This is a problem with CockroachDb. I tested the same code using Postgres 14, and it works fine. I do not think it is cursor related. We are using no cursors, just a simple select with a pool created by databases package in python.

We are evaluating CockroachDB to use in the company, and we can go without store procedures and other things, but this connection confusion is a no-no-no.

What is the priority of this issue? Do you have an ETA about when it is going to be fixed?

[pauldraper]

What is the priority of this issue? Do you have an ETA about when it is going to be fixed?

Not a maintainer, but I imagine this wouldn't be "fixed."

PostgreSQL connections fundamentally run a single query at a time. (It is possible to pipeline queries. But that's relatively uncommon, and there are usually suitable alternatives.)

[elprans]

The Cockroach issue is probably #580 (which should be fixed by cockroachdb/cockroach#83164)