Leaked connections caused by asyncio.CancelledError

[nevladov]

• asyncpg version: 0.18.2
• PostgreSQL version: 10
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?:
• Python version: 3.6.6
• Platform:
• Do you use pgbouncer?: yes
• Did you install asyncpg with pip?:
• If you built asyncpg locally, which version of Cython did you use?:
• Can the issue be reproduced under both asyncio and
  uvloop?:

asyncio.CancelledError occured in this place may be cause of connection leak.
The code below reproduces this problem:

import asyncpg
import traceback

async def test_connect():
    conn = None
    try:
        print('started')
        conn = await asyncpg.connect(
            host='your_host',
            port='6432',
            database='db',
            user='user',
            password='password',
            ssl=True,
        )
    except asyncio.CancelledError:
        print('CancelledError occured')
        traceback.print_exc()
    finally:
        if conn:
            await conn.close()
            print('connection closed')
            return
        print('connection was not closed, maybe leaked')


async def test_cancel():
    for i in range(50): # try 50 times
        future = asyncio.ensure_future(test_connect())
        await asyncio.sleep(0.09) # this sleep allows to call connect
        future.cancel()
    await asyncio.sleep(600)


import asyncio
loop = asyncio.get_event_loop()
loop.run_until_complete(test_cancel())

Printed traceback:

Traceback (most recent call last):
  File "<stdin>", line 11, in test_connect
  File "python3.6/site-packages/asyncpg/connection.py", line 1688, in connect
    max_cacheable_statement_size=max_cacheable_statement_size)
  File "python3.6/site-packages/asyncpg/connect_utils.py", line 543, in _connect
    connection_class=connection_class)
  File "python3.6/site-packages/asyncpg/connect_utils.py", line 519, in _connect_addr
    await asyncio.wait_for(connected, loop=loop, timeout=timeout)
  File "python3.6/asyncio/tasks.py", line 351, in wait_for
    yield from waiter
concurrent.futures._base.CancelledError

After running this script 3-6 iterations of 50 lead to connection leaks:

$ netstat -a | grep 6432 |  awk '{ print $6 }'
ESTABLISHED
ESTABLISHED
ESTABLISHED
ESTABLISHED

These connections remain establised while python process is running.
Our service is based on aiohttp application. Servise has about 5K request per second. When client request is finished by timeout aiohttp cancels handler task. This situation occurrs very often when we have network problem. And in this case we have thousands of lost connections.

[stachlewski]

With aiohttp you need to shield() your code. Connect to the database inside a shielded function.

[1st1]

Should be fixed in master. Thanks for reporting!

[elprans]

Fixed in #608