Feature request: Support client side keepalives

[keithks]

I have been using version 0.20.1. I was able to configure server side TCP keepalives via the server_settings dict object, but I can't seem to find the place where I can set client side keepalives from either a connection pool or a connection object. Does it make sense for an async library like asyncpg to support something like this?

Thanks and Regards, Keith

[elprans]

Yeah, this should be possible via ioctl on the transport socket, which you can get in _connect_addr: tr.get_extra_info('socket').

[teplinsky-maxim]

Hello! Since 2 years I didn't find any variant, how to pass keepalive options to an engine\connection\etc, as it is possible in psycopg2.

engine = create_async_engine(
    SQLALCHEMY_DATABASE_URL,
    connect_args={
        "keepalives": 1,
        "keepalives_idle": 10,
        "keepalives_interval": 5,
        "keepalives_count": 5
    },
    pool_size=50,
    max_overflow=100
)

the variant above does not work. Can you guys tell me, how should I implement the same behavior as it says here? https://www.postgresql.org/docs/14/libpq-connect.html

[robinlandstrom]

This worked for me to enable client side TCP Keepalives on a connection.

python -m asyncio
import asyncpg
import socket
def set_keepalive_linux(sock, after_idle_sec=1, interval_sec=3, max_fails=5):
    """Set TCP keepalive on an open socket.

    It activates after 1 second (after_idle_sec) of idleness,
    then sends a keepalive ping once every 3 seconds (interval_sec),
    and closes the connection after 5 failed ping (max_fails), or 15 seconds
    """
    sock.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1)
    sock.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPIDLE, after_idle_sec)
    sock.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPINTVL, interval_sec)
    sock.setsockopt(socket.IPPROTO_TCP, socket.TCP_KEEPCNT, max_fails)

c = await asyncpg.connect("postgresql://...")
set_keepalive_linux(c._transport.get_extra_info("socket"))
await c.execute("select pg_sleep(10)")

With set_keepalive_linux in place i get a connection timeout after ~15s if I pull out the network cable during the select pg_sleep(10) call.

Without it pg_sleep(10) hangs indefinitely.