asyncpg.exceptions.ReadOnlySQLTransactionError: cannot execute UNLISTEN during recovery #707

• asyncpg version: 0.22.0
• PostgreSQL version: 9.4
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?: Local PostgreSQL install
• Python version: 3.8
• Platform: Linux
• Do you use pgbouncer?: Yes
• Did you install asyncpg with pip?: Yes
• If you built asyncpg locally, which version of Cython did you use?: 3.8?
• Can the issue be reproduced under both asyncio and
  uvloop?:

When connected to a read-only replica (possibly reproducible even if not with connection server_settings={"options": "-c default_transaction_read_only=on"}), when asyncpg attempts to return the connection to the pool, its reset_query issues "UNLISTEN *" which results in the below:

  ...
  File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1311, in reset
    await self.execute(reset_query, timeout=timeout)
  File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 297, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 336, in query
asyncpg.exceptions.ReadOnlySQLTransactionError: cannot execute UNLISTEN during recovery

UNLISTEN is a no-op in this context, and PostgreSQL 10.7+ ignore it with their patch "Allow UNLISTEN during recovery".
https://www.postgresql.org/docs/10/release-10-7.html
https://www.postgresql.org/message-id/15766.1548469030%40sss.pgh.pa.us

Ideally asyncpg could skip sending UNLISTEN when returning the connection to the pool when the connection is in recovery (read only), at least when connection server version is < 10.7.

A hacky workaround (so long as you are not using [UN]LISTEN) is to alter the connection's notifications capability as below after acquiring the connection from the pool:
conn._con._server_caps = conn._con._server_caps._replace(notifications=False)