Async connection close method doesn't terminate the ongoing transaction

[khushboovashi]

If the async query is running in the thread and the connection is explicitly closed, the ongoing execution doesn't get terminated (we can still see the process in the terminal). Also, the execute function ( await _c.execute("SELECT pg_sleep(20);")) runs indefinitely.

Here is the test code:

async def test_connect():
    conn = await AsyncConnection.connect(
        host='localhost',
        port=5432,
        dbname='db1',
        user='postgres',
        password='xxxxxx'
    )

    return conn

conn = asyncio.run(test_connect())
_cur = _cursor(conn)

def asyn_exec_query(c):
    try:
        async def _execute(_c):
            print("Execution Start.")
            await _c.execute("SELECT pg_sleep(20);")
            print("Execution Complete.")
        asyncio.run(_execute(c))
    except Exception as e:
        print(str(e))


async def main():
    _thread = threading.Thread(target=asyn_exec_query, args=(_cur,))
    _thread.start()
    print("Before Sleep")
    time.sleep(3)
    print("After sleep")
    await conn.close()
    print("Finish")

asyncio.run(main())

[dvarrazzo]

Uhmm... so the suggestion would be to send a cancel before closing, if the connection is closed in ACTIVE state?

[khushboovashi]

Yes, and the cursor.execute function should not go into an infinite loop.

[dvarrazzo]

Actually, by the sound of it, it seems more like we just don't close the connection correctly, there should be no need to issue a cancel.

[dvarrazzo]

Hello,

I might have found the problem. The attempted solution is being tested, and, if it works correctly, I will release a fixed version soon.

The only fix introduced is about hanging forever on an async connection that was closed while querying.

Unlike suggested here, we will not run a cancel on close too. The reason is that this is a change in behaviour that should be analyzed better, and because currently cancelling is a blocking operation (an async cancel is being introduced in a future version of the libpq).

So, with this fix, there should be no hanging anymore. However, if you want to have your pg_sleep(20) interrupted early on close, you still need to run conn.cancel() from your code.

[dvarrazzo]

This should be fixed in 3.1.12, just released.