Playing with postgres notify

Yesterday I got the basics in place with world/base creation so today i wanted to see how postgres notify/listen worked. My thinking is that this can form the basis of communication betwen the process that updates the base populations and the server process that talks to the frontend clients.

I found a useful looking library https://pypi.org/project/broadcaster/ but I would rather play around with postgres notify myself.

My first attempt at implementing the example here worked as soon as I remembered to set autocommit=True.

Now I just need this to work async so the fastapi server can listen to this in the background. For that I found this useful stackoverflow discussion on github issues https://github.com/tiangolo/fastapi/issues/5015

After some back and forward and reading up about asyncio again, I ended up with a snippet that looks and feels right

async def listen_to_channel(channel):
    async with await psycopg.AsyncConnection.connect(
        postgres_url, autocommit=True
    ) as aconn:
        await aconn.execute(f"LISTEN {channel};")
        async for msg in aconn.notifies():
            print("Got message:", msg)


@asynccontextmanager
async def lifespan(app: FastAPI):
    task = create_task(listen_to_channel("mychannel"))
    yield
    task.cancel()


app = FastAPI(lifespan=lifespan)

So this will startup when FastAPI starts up and listen for any notification on the postgres channel, which presumably would come from the updater process.

But this got me wondering, why should I have an updater process at all when I can update the database as needed in another task just like this one? That way I don’t have to fiddle around with any inter-process communication. That sounds much simpler but I’m glad I got to play around with postgres notify/listen!