Gino: How do I use Gino without a pool?

[jared-mackey]

I am trying to use Gino alongside of PgBouncer. I am having issues using the Gino pool that I can only solve with PgBouncer. The issue is that Gino pool will take and hold a lot connections even if it is not using them. How can I use Gino in a way that it will create a new connection every time I try to use it?

It seems that the default strategy of Gino does not support sqlalchemy's NullPool. Could this be supported?

[wwwjfy]

May I know why you don't want to reuse the connections? If too many connections in pool is a problem, we can limit the size of the pool.
There is also a parameter in asyncpg called max_inactive_connection_lifetime, which means after some time if the connection is not used, it'll be closed.

[jared-mackey]

I am trying to share a fixed pool size across many replicas of my app that uses Gino. It is hard to scale the replicas up without modifying their fixed pool size. So I am trying to use PgBouncer to limit the total number of connections to my database across all replicas.

I was able to get this working by creating a new Dialect and Pool class.

class FakePool:
    def __init__(self, u: url.URL, dialect, loop):
        self._url: URL = u
        self.dialect = dialect
        self.loop = loop

        self._connections = {}

    async def acquire(self, timeout):
        if timeout is None:
            timeout = 60
        connection = await asyncpg.connect(
            loop=self.loop,
            host=self._url.host,
            port=self._url.port,
            user=self._url.username,
            database=self._url.database,
            password=self._url.password,
            timeout=timeout,
        )
        self._connections[id(connection)] = connection
        return connection

    async def release(self, connection):
        await connection.close()
        del self._connections[id(connection)]

    async def close(self):
        # Wait for all connections to close
        async def _wait():
            if not self._connections:
                return

            logger.info("Waiting for Gino connections to close...")
            while self._connections:
                await asyncio.sleep(0.01, loop=self.loop)

        await asyncio.wait_for(_wait(), timeout=30.0, loop=self.loop)


# noinspection PyAbstractClass
class FakePoolPGDialect(AsyncpgDialect):
    """
    A PG Dialect that overrides init_pool so we can use our FakePool class instead or a real pool"""

    async def init_pool(self, url, loop):
        return FakePool(url, self, loop)

registry.register("postgresql.asyncpgfakepool", "my_app.gino", "FakePoolPGDialect")

[wwwjfy]

I see, so you already have PgBouncer as a connection pool and don't need another one from Gino.
From my view, it looks fine to hold connections to PgBouncer in your app, and another way other than a new pool, can be setting max_inactive_connection_lifetime to 1, so the connection will be released after 1 second.

[jared-mackey]

My plan is to simply start a new connection at the beginning of every request and reuse it throughout the lifetime of the request and then close it.

I could also just pass that arg to the pool. That may be simpler than my FakePool.

[wwwjfy]

I'll see if we can add NullPool. :)

[fantix]

An alternative solution would be not to use an Engine but a Connection as the bind to Gino. However, this is not well-tested as Engine + Pool, so NullPool is still the better approach I think.

For this case, perhaps it makes more sense to use a normal pool in GINO to connect to pgBouncer, if the latter is set in "Transaction pooling" mode and MagicStack/asyncpg#339 gets fixed.