Description

I have been using this application for a while and noticed that it was becoming increasingly memory-hungry.
Poking around the codebase, I identified two categories of memory leaks in the database helper implementations used throughout the application.

1. asyncpg pool leaked on exception (ChirpHeliumKeysRpc.py)

async_db_fetch and async_db_transaction created a brand-new asyncpg connection pool on every call and failed to close it if an exception occurred.

# Before — pool leaked if anything between connect() and close() raised
async def async_db_fetch(self, query: str):
    db = Database()
    await db.connect()       # creates a 10-connection pool
    async with db.pool.acquire() as conn:
        async with conn.transaction():
            cur = await conn.fetch(query)
    await db.close()         # never reached on exception
    return cur

async def async_db_transaction(self, query: str):
    db = Database()
    await db.connect()
    async with db.pool.acquire() as conn:
        async with conn.transaction():
            await conn.execute(query)
    # no close() at all — pool always leaked

helium_skfs_update calls async_db_fetch every 5 minutes inside async_run_every, which silently swallows exceptions. Each leaked pool holds ~10 open TCP connections and background asyncio tasks that are never cleaned up.

2. psycopg2 connections not closed (ChirpHeliumKeysRpc, ChirpHeliumRequestsRpc, ChirpHeliumJoinRpc, ChirpHeliumTenant)

All psycopg2 helpers used the pattern:

with psycopg2.connect(self.postgres) as con:
    with con.cursor() as cur:
        cur.execute(query)

The with psycopg2.connect(...) as con: context manager only commits or rolls back the transaction on exit — it does not close the connection. The underlying socket is only released when Python's garbage collector finalises the object.

In high-frequency paths like meta_up (called on every device uplink) and update_device_status (called every 5 minutes across all devices), this causes connections to accumulate faster than GC reclaims them, exhausting PostgreSQL's max_connections and growing the process's memory footprint over time.