asyncpg.exceptions.ReadOnlySQLTransactionError: cannot execute UNLISTEN during recovery

[dfuhry]

• 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)

[elprans]

We used to have a guard for UNLISTEN, which was expensive and it was deemed that supporting old unpatched versions of Postgres is not worth the perf hit. See #648 for details. If you can, upgrade to at least 9.4.21, otherwise your capabilities hack is the way to go.

[mrkbbk]

From my experience, the issue still exist with pg v9.6.2, and from brief skimming through Postgres repo commits, it looks like "UNLISTEN on hot stand by replica" tolerance appeared in v9.6.12 [1]

[1] (https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0d5b2735122557417e24a73f92a518638d2a5a32)

[elprans]

The UNLISTEN fix was backported to PostgreSQL 9.4.21, 9.5.16, 9.6.12, 10.7, and 11.2. The best course of action is to update to the latest Postgres point release.

[mrkbbk]

Thanks for the clarification.