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