Re: Timeout parameters

Hello Ryohei,

I took a look at your changes and I have some notes.
I faced the same issue as you faced. In my opinion hanging of a client is
quite critical case and it needs to be overcame.
TCP_USER_TIMEOUT option helps to overcome this problem and I agree with
you that it needs to be supported within PostgreSQL.

Nevertheless, it is necessary to take into account that the option
TCP_USER_TIMEOUT is supported by Linux kernel starting since 2.6.37. In a
lower kernel version these changes will not take affect.

I am not sure that suggested by you “socket_timeout” option should be
implemented.
I see that you have changed pqWait() function. In my opinion it
contradicts a bit with the comment to this function:
“We also stop waiting and return if the kernel flags an exception
condition on the socket.” It means that this function should wait for some
condition (ready to read/write) forever. On the other side, there is a
function pqWaitTimed() which does the same action but within a timeout.
So, in my opinion such changes of this function can lead to the problem
with backward compatibility: the caller process expects that it will wait
forever but terminates unexpectedly by timeout.

As far as I understand PostgreSQL versioning policy, the implementation of
new parameter requires modification of internal PostgreSQL structure. As
you know it is not posssible without stopping a service which can be done
during migration to the new major version of PostgreSQL which is expected
to be released in September 2019.

As a workaround I suggest using asynchronous command processing
https://www.postgresql.org/docs/10/static/libpq-async.html.

Best regards,
Andrei Yahorau

From: "Nagaura, Ryohei" <nagaura(dot)ryohei(at)jp(dot)fujitsu(dot)com>
To: "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org>,
Cc: "AYahorau(at)ibagroup(dot)eu" <AYahorau(at)ibagroup(dot)eu>
Date: 23/10/2018 07:37
Subject: Timeout parameters

Hi, all.

I'd like to suggest introducing two parameters to handle client-server
communication timeouts.
That is "tcp_user_timeout" and "socket_timeout" parameter.

I implemented "tcp_user_timeout" parameter
in both backend and frontend side.
This parameter enables us to
use TCP_USER_TIMEOUT option on linux.
If the parameter is specified, the process sets the value to
TCP_USER_TIMEOUT option.
In my opinion, this option is needed for the following situation:
If the server can't return an ack packet to the request from the client,
the client performs retransmission processing.
In this case TCP keepalive option can't work.
Therefore we need TCP USER TIMEOUT option.
Andrei Yahorau also refer to the necessity of this option in [1].

"socket_timeout" is the application layer timeout parameter
from when frontend issues SQL query
to when frontend receives the execution result from backend.
When this parameter is active and timeout occurs,
frontend close the socket.
It is a merit for client to set the maximum time
to wait for SQL.

I'm waiting for your opinions or reviews.

[1]
https://www.postgresql.org/message-id/flat/OF4C8A68CE.A350F319-ON432582D0.0028A5FF-432582D0.002FEE28%40iba.by

Bes regards,
---------------------
Ryohei Nagaura

[attachment "socket_timeout.patch" deleted by Andrei Yahorau/IBA]
[attachment "TCP_USER_TIMEOUT_in_backend.patch" deleted by Andrei
Yahorau/IBA] [attachment "TCP_USER_TIMEOUT_in_interface.patch" deleted by
Andrei Yahorau/IBA]