Description
- asyncpg version: 0.18.3
- PostgreSQL version: PostgreSQL 10.10 (Ubuntu 10.10-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
- Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
the issue with a local PostgreSQL install?: N/A - Python version: 3.7.4
- Platform: Linux, MacOS
- Do you use pgbouncer?: YES
- Did you install asyncpg with pip?: Yes
- If you built asyncpg locally, which version of Cython did you use?: N/A
- Can the issue be reproduced under both asyncio and
uvloop?: IDK
My application uses the library in the following manner:
db = await asyncpg.create_pool(
postgresql://user:password@host:6432/MyDB?' +
'sslmode=require&application_name=My-application-name'
)
# ...
async with db.acquire() as conn:
val = await conn.fetchval('select ...', params)
use(val)
# ...
await db.close()
The problem is that I cannot not see the application_name in pg_stat_activity when connecting throug pgbouncer, though with direct connection to RDMBS it is being shown fine. At the same time other applications show application name successfully using connection through pgbouncer. I also noticed that if I pause program execution at some point I am able to see the application_name.
To reproduce the issue I created a script with delays to see in what moments it gets set and cleared:
import asyncio
import asyncpg
pgb = 'postgresql://user:password@host:6432/NLP?sslmode=require&application_name=My-application-name'
pgloc = 'postgresql://postgres@localhost:5432/postgres?application_name=My-application-name'
async def main(loop):
db = await asyncpg.create_pool(pgb, min_size=1, max_size=5, loop=loop,
max_inactive_connection_lifetime=5.0)
# application_name
print('Pool') # No
await asyncio.sleep(5) # No
async with db.acquire() as conn: # No
print('Connected') # No
await asyncio.sleep(5) # No
async with conn.transaction(): # Yes
print('Started tran') # Yes
await asyncio.sleep(5) # Yes
val = await conn.fetchval('select $1::int', 5) # Yes
print('Fetched', val) # Yes
await asyncio.sleep(5) # Yes
print('Finished tran') # Yes
await asyncio.sleep(5) # Yes
print('Released conn') # No
await asyncio.sleep(5) # No
print('Timeout') # No
await asyncio.sleep(5) # No
await db.close() # No
print('Closed') # No
loop = asyncio.get_event_loop()
loop.run_until_complete(main(loop))
As you can see, it is get set at the point of starting transaction (either explicit or when fetching data), and gets cleared at time of releasing connection to asyncpg pool.
With direct connection to Postgres, the picture is different (and more expectable): the application name is being shown through the entire lifetime of the session, starting with 'Pool' and finishing at 'Timeout' expiration.
Having looked at the source code I found this: https://github.com/MagicStack/asyncpg/blob/master/asyncpg/connection.py#L1281
It looks like based on detected server capabilities asyncpg issues some reset statements. The problem is in RESET ALL. I tested it separately through online session with sync psycopg2 and it shows the difference between direct connection and pgbouncer: in the first case RESET ALL does not perform anything malicious, but in the second case it indeed clears application_name.
I am not sure if it is a piece misbehavior of the library or pgbouncer. Can we avoid issuing RESET ALL? What are the downsides of it? Or should I file a bug for pgbouncer?