How to remove log listeners for a pool:ed connections.

[Gyllsdorff]

According to the documentation the setup callback is where you should do stuff like add log listeners to the connection. It works fine but when the connection is release back to the pool I get a warning that it still have a active log listener, which is correct but misleading.

• How/where should I remove the log listener?
• Is there a release callback somewhere that I have missed?
• I see that the warning is made using the warnings python api. I can just silence it but that might hide actual errors.

Example code:

pool = await asyncpg.create_pool(
    user='postgres',
    setup=lambda conn: conn.add_log_listener(_log_test),
)

After I have checked out a connection and released it back this gets printed to stdout/err: /opt/python3.7/lib/python3.7/site-packages/asyncpg/connection.py:1295: InterfaceWarning: <wild.database.LeaderConnection object at 0x7f006d4e1208> is being released to the pool but has 1 active log listener

[elprans]

Good point! We need to allow specifying the setup counterpart in the pool configuration.

[elprans]

As a note, it is often better to allocate a dedicated persistent connection for the purpose of listening to notifications so that you don't suffer the overhead of LISTEN/UNLISTEN on every acquire()/release() cycle.

[Gyllsdorff]

@elprans

As a note, it is often better to allocate a dedicated persistent connection for the purpose of listening to notifications [SNIP]

I am only interested in the RAISE NOTICE events/messages generated by the queries, is the only way to do that by opening a notification channel?

[elprans]

RAISE NOTICE is different from NOTIFY. For the former you need add_log_listener(), and for the latter its add_listener().

[rmedaer]

Good point! We need to allow specifying the setup counterpart in the pool configuration.

@elprans do you already have an ETA for this improvement ?

Instead of passing init and setup callbacks in pool factory (or in connection initialization). Why could we not setup these hooks directly into the Connection(Proxy) class which is already inheritable/configurable through the connection_class argument.

[frafra]

Some problems here with add_listener: https://github.com/frafra/postgresql2websocket/blob/v0.1/postgresql2websocket.py#L22

Is there any workaround that can be used in the meanwhile?

[Soheab]

Any fix for this yet?

[neumond]

In my case there's one long-living, but restartable thread with LISTEN, with several side-threads that react on received events and use pg connections as well. I still find using the pool beneficial since it's easy to recreate a connection when listener thread restarts.

async def _remove_listeners(conn):
    is_closed = conn._con is None
    if is_closed:
        return
    ...
    await conn.remove_listener(channel, callback)

async with AsyncExitStack() as stack:
    conn = await stack.enter_async_context(pool.acquire())
    stack.push_async_callback(_remove_listeners, conn)
    ...

I use little hack here, _con property of proxy object, to detect whether underlying connection is dead. .is_closed() fires InterfaceError as any other Connection method. This allows to remove listeners on graceful exit and to avoid exceptions caused by lost connection (pg_terminate_backend).