RE: Timeout parameters

Hello,

> To wrap up, the relevant parameters work like this:

> * TCP keepalive and TCP user (retransmission) timeout: for network
problems
> * statement_timeout: for long-running queries
> * socket_timeout (or send/recv_timeout): for saturated servers

Takayuki Tsunakawa, could you provide wider explanation of socket_timeout?
I'm little bit misunderstanding in which cases this parameter is/can be
used.

I would like to add some more information about TCP keepalive and
TCP_USER_TIMEOUT mechanisms:
1) Both these mechanisms are used for termination socket connection in
case of network problems.
2) This termination of tcp connection is done on operation system level
(kernel) and not by application.
3) TCP keepalive and TCP_USER_TIMEOUT work differently and complement each
other :
* TCP keepalive mechanism works when a socket is in idle state
(there is no any transaction in this case)
* TCP_USER_TIMEOUT mechanism works when a socket is in active state
(sending/receiving data).

So, TCP keepalive and TCP_USER_TIMEOUT provide full control under network
state directly after creating a TCP socket and applying these parameters
to it. Moreover, this control is delegate to the operation system (kernel)
in case it supports such mechanisms.
If TCP_USER_TIMEOUT is not supported by PostgreSQL, it means that TCP
connection are partly controlled by the operation system (kernel). In this
case pqWaitTimed() should be used on the application layer for connection
control in data transmission phase.

In my opinion, there is no any difference between server and client
connection sides. To avoid mess in the configuration it seems reasonable
to give the same name of this option for the client and server sides.

To my mind, this description of tcp_user_timeout option is not correct
(See my comment about TCP_USER_TIMEOUT mechanism above).

> + <listitem>
> + <para>
> + Specify in milliseconds the time to disconnect to the client
> + when there is no ack packet from the client to the server's data
transmission.
> + This parameter is supported on linux version 2.6.37 or later.
> + </para>
> + <note>
> + <para>
> + This parameter is not supported on Windows.
> + </para>
> + </note>
> + </listitem>

As for me It better to specify the description as follows:

<listitem>
<para>
Define a wrapper for TCP_USER_TIMEOUT socket option of libpq connection.
</para>
<para>
Specifies the number of milliseconds after which a TCP connection can be
aborted by the operation system due to network problems when the data is
transmitting through this connection (sending/receiving). A value of 0
uses the system default. This parameter is supported only on systems that
support TCP_USER_TIMEOUT or an equivalent socket option, and on Windows;
on other systems, it must be zero. In sessions connected via a Unix-domain
socket, this parameter is ignored and always reads as zero.
</para>
<note>
<para>
This parameter is not supported on Windows, and must be zero.
</para>
<para>
To enable full control under TCP connection use this option together with
keepalive.
</para>
</note>
</listitem>

Best regards,
Andrei Yahorau

From: "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>
To: 'Fabien COELHO' <coelho(at)cri(dot)ensmp(dot)fr>, "Nagaura, Ryohei"
<nagaura(dot)ryohei(at)jp(dot)fujitsu(dot)com>,
Cc: "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org>,
"AYahorau(at)ibagroup(dot)eu" <AYahorau(at)ibagroup(dot)eu>
Date: 27/12/2018 11:26
Subject: RE: Timeout parameters

From: Fabien COELHO [mailto:coelho(at)cri(dot)ensmp(dot)fr]
> I still do not understand the use-case specifics: for me, aborting the
> connection, or a softer cancelling the statement, will result in the
> server stopping the statement, so the server does NOT "continue the
job",
> so I still do not see how it really differs from the server-side
> statement_timeout setting.

How about when the server is so saturated that statement_timeout cannot
work? See SQLNET.SEND_TIMEOUT and SQLNET.RECV_TIMEOUT here:

https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF228

As these parameter names suggest, maybe we could use SEND_TIMEO and
RECV_TIMEO socket options for setsockopt() instead of using pqWaitTimed().

To wrap up, the relevant parameters work like this:

* TCP keepalive and TCP user (retransmission) timeout: for network
problems
* statement_timeout: for long-running queries
* socket_timeout (or send/recv_timeout): for saturated servers

FYI, PgJDBC has a parameter named socketTimeout:

https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters

Regards
Takayuki Tsunakawa