keepalive_* connection args being ignored?

[danielfernandez]

Summary

I'm using psycopg2-binary==2.9.9 to connect to a PostgreSQL-compatible database. In between, a load balancer is configured to close any connection idle for more than 30 secs, and some of my queries may take more than that to return the first result, which makes the load balancer close the connection. I configure the keepalives_* connection arguments, but using Wireshark I don't see any TCP Keep-Alive messages — except in macOS.

Scenario

In order to reproduce this, I'm using a regular PostgreSQL 12 server behind the load balancer, and emulating the delay in returning the first result with pg_sleep(...). But I'm also checking simply having the connection open with the PostgreSQL session idle for some time between (shorter) queries:

Sample 1:

import psycopg2

conn = psycopg2.connect(host="thegateway", 
                        port="55432",
                        dbname="bookstore",
                        user="postgres",
                        password="****",
                        keepalives=1,
                        keepalives_idle=5,
                        keepalives_interval=5,
                        keepalives_count=2,
                        tcp_user_timeout=1000)
conn.set_session(autocommit=True)

with conn.cursor() as cur:
  cur.execute("WITH start_time AS (SELECT pg_sleep(45) AS start_time)"
              "   SELECT * FROM books CROSS JOIN start_time")
  records = cur.fetchall()

conn.close()

print(records)

Sample 2:

import psycopg2
import time

conn = psycopg2.connect(host="thegateway", 
                        port="55432",
                        dbname="bookstore",
                        user="postgres",
                        password="****",
                        keepalives=1,
                        keepalives_idle=5,
                        keepalives_interval=5,
                        keepalives_count=2,
                        tcp_user_timeout=1000)
conn.set_session(autocommit=True)

with conn.cursor() as cur:
  cur.execute("SELECT * FROM books")
  records = cur.fetchall()

print(records)

time.sleep(45)

with conn.cursor() as cur:
  cur.execute("SELECT * FROM authors")
  records = cur.fetchall()

conn.close()

print(records)

I've tested both code samples above as:

• Jupyter notebook running on an Ubuntu-based Docker image (jupyter/datascience-notebook:x86_64-ubuntu-22.04)
• Python scripts running on Windows 10, Ubuntu 18 and macOS Monterey clients.

In all cases the client was in a separate machine to the gateway and the PostgreSQL server (i.e. no UNIX domain sockets should be involved). I've tried both with and without setting the tcp_user_timeout argument.

Checking with Wireshark, only when executing from macOS Montery can I see the TCP Keep-Alives being sent to thegateway.

Is this expected? Am I missing any bits of configuration? My understanding is that this should have worked at least on Linux (including the Ubuntu Docker image)…

[dvarrazzo]

I would have expected the same, but I don't know precisely how this feature work.

This feature is part of the libpq, and I expect the libpq to mostly configure some configuration on the TCP socket.

So my suggestion would be to grep for keepalive in the src/interfaces/libpq directory of the Postgres repository or to ask to a PostgreSQL ML.

If you have any useful information please let us know!