Application name with pgbouncer

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

Inizia da asyncpg/connection.py intorno alla riga 1281 ed esegui lo script del pool fornito con PgBouncer e direttamente con PostgreSQL. Confronta application_name prima e dopo il rilascio della connessione e RESET ALL; il lavoro è completo quando il comportamento del reset e la relativa responsabilità sono stati stabiliti e quando è stata identificata una correzione appropriata o una limitazione documentata.