On `connect_timeout`, its documentation, and unreachable backends

[midnya]

As of writing this issue (4.7.2), the latest documentation states the following for the connect_timeout configuration parameter:

connect_timeout (integer)
Specifies the amount of time in milliseconds before giving up connecting to backend using connect() system call. Default is 10000 ms (10 second).
The flaky network user may want to increase the value. 0 means no timeout.
Note: connect_timeout value is not only used for a health check, but also for creating ordinary connection pools.
This parameter can be changed by reloading the Pgpool-II configurations.

pgpool2/doc/src/sgml/healthcheck.sgml

Line 263 in 3c97bbd

<varlistentry id="guc-connect-timeout" xreflabel="connect_timeout">

This wording lead me to assume something like the following:

• Pgpool tries to connect to backend X
• If the backend does not respond in connect_timeout milliseconds, the connection to the backend is deemed a failure.
• If the reason Pgpool tried to connect to backend X was because a regular PostgreSQL client wished to connect to Pgpool via the backend, a failure is then reported to that application.

This interpretation was motivated by the name of this parameter, and the specific use of the words "giving up connecting to backend".

However, I encountered a specific failure in my cluster which makes me think this interpretation is incorrect, the documentation is not up to date to the behavior, or the behavior is bugged.

---

Assuming the following typology:

• Linux 6.x
• 3 "modern" PostgrSQL nodes
• 3 Pgpool in quorum
• streaming replication
• connect_timeout is a low value (a couple of seconds)
• everything is healthy

My understanding of what happens when an application wishes to connect to Pgpool is roughly the following:

• The client authenticates to Pgpool
• Pgpool attemps to create connections to all 3 backend nodes
  • During this process, this function is called:

    pgpool2/src/protocol/pool_connection_pool.c

    Line 590 in 3c97bbd

    connect_with_timeout(int fd, struct addrinfo *walk, char *host, int port, bool retry)

• Once those connections are established, the client may discuss PostgreSQL protocol and Pgpool business logic kicks in

If one of the standby node becomes unreachable (hardware failure, or for testing purposes, iptables-d away), because of the documentation wording, this behavior might be expected:

• The client authenticates to Pgpool
• Pgpool attemps to create connections to all 3 backend nodes
  • connect_with_timeout fails after approximately connect_timeout milliseconds, as one of the node is unreachable (no ECONNREFUSED can kick in)
• Pgpool respond with an error to the client

However, the actual behaviour is:

• The client authenticates to Pgpool
• Pgpool attemps to create connections to all 3 backend nodes
• The following loop happens:
  • connect_timeout milliseconds elapses
  • Pgpool logs those specific lines:

    pgpool2/src/protocol/pool_connection_pool.c

    Lines 672 to 674 in 3c97bbd

    	ereport(LOG,
    	(errmsg("trying connecting to PostgreSQL server on \"%s:%d\" by INET socket", host, port),
    	errdetail("timed out. retrying...")));

• After an amount of time dictated by the kernel's sysctl configuration, the socket is closed
• Pgpool respond with an error to the client

Given that the default on many Linux systems can be a couple of minutes for the kernel to abort a connection because of an unreachable peer, this is far cry, and completely independent of, connect_timeout.

This becomes worse if load_balance_mode = off. To serve a client, my understanding is that Pgpool still tries to establish a connection to every backend regardless of that setting, which means that in that failure mode, an end user might be left wondering why an unreachable replica means that the connection to the primary is stalled. However, I'm not an expert in Pgpool and there might be a good reason as to why this behavior exists, even if it surprising.

In any case, I believe there is clearly a miss somewhere.

---

I tried exploring the history behind the changes to connect_with_timeout. I came to the conclusion that this code might be broken:

pgpool2/src/protocol/pool_connection_pool.c

Lines 667 to 683 in 3c97bbd

	if (sts == 0)
	{
	/* select timeout */
	if (retry)
	{
	ereport(LOG,
	(errmsg("trying connecting to PostgreSQL server on \"%s:%d\" by INET socket", host, port),
	errdetail("timed out. retrying...")));
	continue;
	}
	else
	{
	ereport(LOG,
	(errmsg("failed to connect to PostgreSQL server on \"%s:%d\", timed out", host, port)));
	return false;
	}
	}

Specifically, I do not understand the point of this branch:

if (retry)
{
	ereport(LOG,
			(errmsg("trying connecting to PostgreSQL server on \"%s:%d\" by INET socket", host, port),
			 errdetail("timed out. retrying...")));
	continue;
}

The way it is set up with this continue;, and without some kind of "max_connect_retries" parameter, turns connect_timeout into a "polling" interval rather than an actual "after x milliseconds, abort" parameter.

Furthermore, my understanding of the retry function parameter is that it was meant to fix a bug (ae8924f) that existed before non-blocking socket and connect_timeout were implemented. I don't think it was meant to mean "retry on timeout" like the log implies, but rather, "retry if something weird and/or unexpected happened" (as retry is however always used like so: (errno == EINTR && retry) in this function).

The existence of connect_timeout and the log implies that a timeout is something that's expected.

---

Short of patching Pgpool, the only remediation I can easily see is to configure the system's tcp_syn_retries to a very low value. This does fix the problem, as the kernel kicks in early and aborts the connection, but this is obviously less than ideal.

I have applied a patch removing the above branch to some of my clusters. After reproducing the failure scenario described above, the bug disappeared. I did not observe unintended side effects.

In any case, I'm not sure what the correct fix is, or what specifically is broken (code or documentation), but I believe something is.

Thank you very much for your time, and your work on Pgpool! I am available if you have any questions or need more information.

[tatsuo-ishii]

Thank you for the report. I have to admit our documentation regarding connect_timeout has a room for improvement.
In the mean time I wonder why you don't let the health check feature handle it. Maybe you don't want to trigger a failover by this?

[midnya]

I wrote this report on the assumption the config was not to be changed, because as far as I can tell, no guard exist in the code to prevent such a configuration. That should have been clearer, my apologies.

And indeed, we configured Pgpool trying to minimize the time an end client would be stuck in a timeout, but not so low that a relatively simple network issue could cause a failover; connect_timeout seemed to be well suited for the task, combined with a higher count of healthcheck retries.

Thank you for your time!