Cursor.execute() freezes + Pool.wait_closed() blocks after Pool.close()

[samthesuperhero]

Dear contributors,

do you have any ideas why execute can at all freeze??

At the time it blocks, pgAdmin says that connection is idle and Wait event is Client: ClientRead

        wapp["state"]["db"] = await aiopg.create_pool(host=wapp["config"]["db-ip"],
                                                      database=wapp["config"]["db-name"],
                                                      user=wapp["config"]["db-usr"],
                                                      password=wapp["config"]["db-pwd"],
                                                      timeout=wapp["config"]["db-connect-timeout"])

        async with wapp["state"]["db"] as _dbconn:
            # Get cursor
            _c = await _dbconn.cursor()
            # Get all table contents
            await _c.execute("SELECT * FROM fa_user_timestamps;")
            # Fetch all results
            _rc = _c.rowcount
            _sm = _c.statusmessage
            if _rc != -1:
                _qr = await _c.fetchall()

In the end, after handling SIGTERM via loop.add_signal_handler() and calling Pool.close() - the await Pool.wait_closed() as well blocks.

Am I doing something wrong??

[Pliner]

Hi @samthesuperhero,

Could you please attach a complete sample which reproduces mentioned issue? It's a bit unclear for me how to reproduce it.

[samthesuperhero]

Hi @Pliner,

sure, please, full example:

# coding=utf-8
"""
Reproduce freezing aiopg
"""

import asyncio

import aiopg


async def fetchmydb() -> None:
    """
    Fetch from DB

    :return: nothing
    """
    _db_pool = await aiopg.create_pool(host="your db host",
                                       database="your db name",
                                       user="login",
                                       password="password",
                                       timeout=2.000)
    print(f"Connected: {_db_pool}")
    async with _db_pool as _dbconn:
        # Get cursor
        _c = await _dbconn.cursor()
        print(f"Got cursor: {_c}")
        # Get all table contents
        await _c.execute("SELECT * FROM fa_user_timestamps;")
        # Fetch all results
        _rc = _c.rowcount
        _sm = _c.statusmessage
        if _rc != -1:
            _qr = await _c.fetchall()
        else:
            _qr = None
    print(f"Row count: {_rc}, message: {_sm}, query result: {_qr}")


loop = asyncio.get_event_loop()

loop.run_until_complete(fetchmydb())

Python:

Python 3.8.0 (default, Oct 25 2019, 15:42:23)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux

System:

[pfn@ya2 ~]$ sudo yum info kernel -q
Installed Packages
Name        : kernel
Arch        : x86_64
Version     : 3.10.0
Release     : 1062.4.1.el7
Size        : 64 M
Repo        : installed
From repo   : updates
Summary     : The Linux kernel
URL         : http://www.kernel.org/
License     : GPLv2
Description : The kernel package contains the Linux kernel (vmlinuz), the core of any
            : Linux operating system.  The kernel handles the basic functions
            : of the operating system: memory allocation, process allocation, device
            : input and output, etc.

Name        : kernel
Arch        : x86_64
Version     : 3.10.0
Release     : 1062.9.1.el7
Size        : 64 M
Repo        : installed
From repo   : updates
Summary     : The Linux kernel
URL         : http://www.kernel.org/
License     : GPLv2
Description : The kernel package contains the Linux kernel (vmlinuz), the core of any
            : Linux operating system.  The kernel handles the basic functions
            : of the operating system: memory allocation, process allocation, device
            : input and output, etc.

Name        : kernel
Arch        : x86_64
Version     : 3.10.0
Release     : 1062.18.1.el7
Size        : 64 M
Repo        : installed
From repo   : updates
Summary     : The Linux kernel
URL         : http://www.kernel.org/
License     : GPLv2
Description : The kernel package contains the Linux kernel (vmlinuz), the core of any
            : Linux operating system.  The kernel handles the basic functions
            : of the operating system: memory allocation, process allocation, device
            : input and output, etc.

Name        : kernel
Arch        : x86_64
Version     : 3.10.0
Release     : 1127.19.1.el7
Size        : 64 M
Repo        : installed
From repo   : updates
Summary     : The Linux kernel
URL         : http://www.kernel.org/
License     : GPLv2
Description : The kernel package contains the Linux kernel (vmlinuz), the core of any
            : Linux operating system.  The kernel handles the basic functions
            : of the operating system: memory allocation, process allocation, device
            : input and output, etc.

Name        : kernel
Arch        : x86_64
Version     : 3.10.0
Release     : 1160.11.1.el7
Size        : 64 M
Repo        : installed
From repo   : updates
Summary     : The Linux kernel
URL         : http://www.kernel.org/
License     : GPLv2
Description : The kernel package contains the Linux kernel (vmlinuz), the core of any
            : Linux operating system.  The kernel handles the basic functions
            : of the operating system: memory allocation, process allocation, device
            : input and output, etc.

Available Packages
Name        : kernel
Arch        : x86_64
Version     : 3.10.0
Release     : 1160.21.1.el7
Size        : 50 M
Repo        : updates/7/x86_64
Summary     : The Linux kernel
URL         : http://www.kernel.org/
License     : GPLv2
Description : The kernel package contains the Linux kernel (vmlinuz), the core of any
            : Linux operating system.  The kernel handles the basic functions
            : of the operating system: memory allocation, process allocation, device
            : input and output, etc.

[pfn@ya2 ~]$ hostnamectl
   Static hostname: ya2.ru-central1.internal
         Icon name: computer-vm
           Chassis: vm
        Machine ID: b58f9d8f31e043e8943c07dbc07d39b4
           Boot ID: ee4315a4db9b49cbaff0e0714d97f44c
    Virtualization: kvm
  Operating System: CentOS Linux 7 (Core)
       CPE OS Name: cpe:/o:centos:centos:7
            Kernel: Linux 3.10.0-1160.11.1.el7.x86_64
      Architecture: x86-64

Output looks like this,

[pfn@ya2 pyscratch]$ python3.8 ./aiopg-reproduce.py
Connected: <aiopg.pool.Pool object at 0x7faebbb85c10>
Got cursor: <aiopg.utils._PoolCursorContextManager object at 0x7faebbb13b40>

and then freezes.

While it is frozen, pgAdmin4 shows this:

where pub is my username which I use to connect.