Simple way to override reuse=True in GinoEngine methods

[coetzeevs]

Context:
I'm building a GraphQL service using:
Ariadne
FastAPI(Starlette)
Gino
aiodataloader

Gino serves most of my async requirements when it comes to calling the backend for data from an incoming request. However, I'm running into an interesting problem. When the incoming query has nested objects (or would result in multiple DB calls), e.g.

query getoutlets {
  outletObjects {
    id
    card_acceptor_guid
    pos_providers {
      id
      name
    }
    terminals {
      id
      pos_providers {
        id
        name
      }
    }
  }
}

(in this case outlets, terminals, and pos providers are all separate DB calls), I end up getting an error from Asyncpg along the lines of asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress.

As near I can tell from trawling through documentation and code, the combination of Gino with Dataloader results in a single connection being used for the same thread and all its related calls, at the same time. This is my current problem that I'd like help on.

What I've done:
After going through the docs for Gino and then reading through the source code, I found this in engine.py:

async def all(self, clause, *multiparams, **params):
    """
    Acquires a connection with ``reuse=True`` and runs
    :meth:`~.GinoConnection.all` on it. ``reuse=True`` means you can safely
    do this without borrowing more than one underlying connection::

        async with engine.acquire():
            await engine.all('SELECT ...')

    The same applies for other query methods.

    """
    async with self.acquire(reuse=True) as conn:
        return await conn.all(clause, *multiparams, **params)

At the moment this is the only method I'm using. But the crux for me was the reuse flag in self.acquire. From my understanding of the code base and how this is used, I set reuse=False and it solved my issue. Now, even in a same thread for Dataloader, Gino uses separate connections to make the required calls to the DB instead of firing all requests through the same connection at the same time. Plus the app remains performant. So from my understanding this is the solve for my particular case. But there might be something I'm missing.

Question(s):

• Is my base understanding of the inner workings of Gino correct in that for a single thread, when reuse=True and there are multiple simultaneous calls to the DB in that same thread, the same connection will be used to satisfy the requests (all at once)?
• Is there a different interface in the Gino architecture that I can use to set this flag (possibly globally for a given connection pool), at init?
• reuse is by default False, but whenever we run those methods (e.g. snippet above) it's overwritten to True. Should this not be a parameter that can be passed on call? e.g.

async def all(self, clause, reuse=True, *multiparams, **params):
    """
    Acquires a connection with ``reuse=True`` and runs
    :meth:`~.GinoConnection.all` on it. ``reuse=True`` means you can safely
    do this without borrowing more than one underlying connection::

        async with engine.acquire():
            await engine.all('SELECT ...')

    The same applies for other query methods.

    """
    async with self.acquire(reuse=reuse) as conn:
        return await conn.all(clause, *multiparams, **params)

and the method could then be invoked as obj = await query.gino.all(reuse=False)

This doesn't yet solve the issue of setting it at a global level, but it's a start I suppose.

Looking forward to hearing the community's thoughts.

[coetzeevs]

I ended up forking the repo and tweaking the usage of reuse via the API. Since I'm building a FastAPI implementation on the back of Starlette, the way I init Gino is via the API interface. Here's my forked repo with an open PR that will demonstrate what I need and what my potential solve is for this. Perhaps it'll help make clear where my issue is/was. This solves my problem, and still passes all tests (at least for PG). Happy to submit as a PR to the main repo if this passes scrutiny.

[fantix]

Is my base understanding of the inner workings of Gino correct in that for a single thread, when reuse=True and there are multiple simultaneous calls to the DB in that same thread, the same connection will be used to satisfy the requests (all at once)?

Yes and no - GINO's unit for context is asyncio.Task instead of thread, as this is an async environment. Regularly, multiple tasks run in a single thread. If engine.all() is called in a context (Task) that has a reusable connection, it will reuse it; otherwise, it will borrow a new connection from the pool.

Creating a new task by default inherits the context from the current task. So if there's a feel that the whole thread is sharing a single connection, it's likely that there're some "dirty" connections shared in a very root context - e.g. unclosed connection during server startup.

Is there a different interface in the Gino architecture that I can use to set this flag (possibly globally for a given connection pool), at init?

Unfortunately not. This traces all the way back to an early discussion #84 even before PEP-567. In short, a workaround is to reset the context for each request, using contextvars.Context().run to make sure the connection is not inherited (thus not reused).

reuse is by default False, but whenever we run those methods (e.g. snippet above) it's overwritten to True. Should this not be a parameter that can be passed on call?

This is by design this way - if let's say you want to achieve engine.all(..., reuse=False), you should explicitly write:

async with engine.acquire():
    await engine.all(...)

Note the engine.all(...) here reuses the connection we just borrowed from the pool. We assume that any query on Engine or db level is an implicit execution, so it always try to reuse connections from the context; explicit execution should be done on connection level.