I have a long-running process (possibly months) that listens for notifications on a database connection using conn.notifies() (see psycopg3 documentation). The documentation doesn’t specify the expected behavior if the listening connection becomes unresponsive/corrupt, so I believe I need to perform health checks myself.

If conn.notifies() is guaranteed to raise an exception if the connection has issues, then the rest of this is moot. Does anyone know?

Assuming conn.notifies() does not necessarily raise an exception, this github comment says I can execute a statement on the listening connection without losing notifications.

If you need to issue a new LISTEN on that connection, you can stop the notifies generator (using generators.close(), see docs), execute the statement, and call notifies() again. You won’t lose messages doing so: they will be buffered in the libpq connection anyway.

def _listen_for_notifications():
    with psycopg.Connection.connect(CONN_STR, autocommit=True) as conn:
        listen_sql = sql.SQL("LISTEN {}").format(sql.Identifier("scheduler_test"))

        conn.execute(listen_sql)
        gen = conn.notifies(timeout=5)
        print('listening')
        # This prints notifications sent within the five second timeout
        for n in gen:
            print(n)

        gen.close()  # I see the same behavior with and without this line.

        print("Performing health check")
        # Any notifications sent over these three seconds are lost
        conn.execute("select pg_sleep(3)")  # Would be SELECT 1 in real code

        conn.execute(listen_sql)
        gen = conn.notifies(timeout=5)
        print('listening again on the same connection')
        # This prints notifications sent within the five second timeout
        for n in gen:
            print(n)

        print('done!')


_listen_for_notifications()

This does not capture notifications that occur while select pg_sleep(3) executes.
This makes sense to me since I have two different generators, but I don’t see a way around that since the first generator is closed whether I close it explicitly or not.