Re: ODBC cliens is hanging if network connection is broken

Hello,

On 2018/07/11 20:54, AYahorau(at)ibagroup(dot)eu wrote:
> Hello,
>
> Thank you for your previous suggestion.
>
> I proceeded with the investigation of this issue. I found out that it
> is ok from time to time.
> On the one hand  the configuration (*pqopt = **keepalives=1
> keepalives_idle=5 keepalives_count=1 keepalives_interval=1*) it
> terminates client connection.
> But it  can happen that sometimes it remains to be hanging about 15
> minutes. At the beginnig of the connection establishment netstat shows
> the following:
> *sles12-2:/etc/unixODBC # netstat -a -n -o |grep 192.168.1.11*
> *tcp        0      0 192.168.1.12:41279      192.168.1.11:5432      
> ESTABLISHED keepalive (2.34/0/0)*
>
> But then it becomes as follows:
> *sles12-2:/etc/unixODBC # netstat -a -n -o |grep 192.168.1.11*
> *tcp        0     38 192.168.1.12:41270      192.168.1.11:5432      
> ESTABLISHED on (8.60/10/0) *
> at it hangs about 15 minutes.

Hmm it seems keepalive stops while waiting for ack.
Therefore it's a matter of retransmission control
You can use TCP_USER_TIMEOUT on linux but the option is not used in libpq.

regards,
Hiroshi Inoue

>
>
> I googled on the internet and found that there is one more
> tcp_keepalive parameter /tcp_retries2/*(
> **https://linux.die.net/man/7/tcp**) *which influences deeply on
> keep-alive behaviour.
> When I changed this parameter on my system to 1 (15 is the default
> value),  I began constantly get the expected behaviour for my ODBC client.
>
> Could you please tell if there is any analogue in psqlODBC/ libpq for
> this parameter?
>
> Thank you so much,
> Andrei Yahorau
>
>
>
> From: "Inoue, Hiroshi" <h-inoue(at)dream(dot)email(dot)ne(dot)jp>
> To: AYahorau(at)ibagroup(dot)eu,
> Cc: MikalaiKeida(at)ibagroup(dot)eu, pgsql-odbc(at)postgresql(dot)org
> Date: 11/07/2018 05:41
> Subject: Re: ODBC cliens is hanging if network connection is broken
> ------------------------------------------------------------------------
>
>
>
>
>
> On 2018/07/10 21:21, _AYahorau(at)ibagroup(dot)eu_
> <mailto:AYahorau(at)ibagroup(dot)eu>wrote:
> Great! Now it works!
>
> Please set appropriate values for keepalives, keepalives_idle,
> keepalives_interval or keepalives_count according to your
> environment.
>
> Please look at_
> __https://www.postgresql.org/docs/10/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS_
> about the keywords.
>
> regards,
>
> Hiroshi Inoue
>
>
> Thank you so much for your explanation.
>
> Best regards,
> Andrei Yahorau
>
>
>
> From: "Inoue, Hiroshi" _<h-inoue(at)dream(dot)email(dot)ne(dot)jp>_
> <mailto:h-inoue(at)dream(dot)email(dot)ne(dot)jp>
> To: _AYahorau(at)ibagroup(dot)eu_ <mailto:AYahorau(at)ibagroup(dot)eu>,
> Cc: _MikalaiKeida(at)ibagroup(dot)eu_ <mailto:MikalaiKeida(at)ibagroup(dot)eu>,
> _pgsql-odbc(at)postgresql(dot)org_ <mailto:pgsql-odbc(at)postgresql(dot)org>
> Date: 10/07/2018 14:28
> Subject: Re: ODBC cliens is hanging if network connection is broken
> ------------------------------------------------------------------------
>
>
>
> Hi,
>
> On 2018/07/10 19:40, _AYahorau(at)ibagroup(dot)eu_
> <mailto:AYahorau(at)ibagroup(dot)eu>wrote:
> Hello,
>
> I added pqopt option into the odbc.ini  file as you suggested:
> [sles12-1]
> Description = PostgreSQL connection to rtpdb
> Driver = PostgreSQL
> Database = rtpdb*
> pqopt = **keepalives=1 keepalives_interval=5 keepalives_count=1*
>
> Oops I mistook keepalive_idle for keepalive_interval.
> Please try*
>
> pqopt = **keepalives=1 keepalives_idle=5 keepalives_count=1
> keepalives_interval=1
>
> regards,
> Hiroshi Inoue*
>
> Servername = sles12-1
> Username =
> Password =
> Port = 5432
> Protocol = 7.4+
> ReadOnly = No
> RowVersioning = No
> ShowSystemTables = No
> ConnSettings =
>
> But unfortunately if did not take a desired effect. These conditions
> lead to client hanging again.
> I checked an opened socket on client side with the command*
> netstat -a -n -o*
> and found out that it contains system defaut parameters:*
> tcp        0      0 192.168.1.12:40961      192.168.1.11:5432      
> ESTABLISHED keepalive (7216.12/0/0)*
>
> After I had changed system keepalive parameters by manual modification
> the files *
> echo 1 > /proc/sys/net/ipv4/tcp_keepalive_time**
> echo 1 > /proc/sys/net/ipv4/tcp_keepalive_intvl
> echo 1 > /proc/sys/net/ipv4/tcp_keepalive_probes*
>
> I saw that it affected on keepalive parameters and the ODBC client
> behaviour and SQLExecute failed when keepalive timeout expired:*
> tcp        0      0 192.168.1.12:40972      192.168.1.11:5432      
> ESTABLISHED keepalive (3.18/0/0)*
>
> Nevertheless this is not a good way of configuration because it
> affects on whole the system.
>
> Did I do anything wrong with pqopt option?
>
> Best regards,
> Andrei Yahorau
>
>
>
> From: "Inoue, Hiroshi" _<h-inoue(at)dream(dot)email(dot)ne(dot)jp>_
> <mailto:h-inoue(at)dream(dot)email(dot)ne(dot)jp>
> To: _AYahorau(at)ibagroup(dot)eu_ <mailto:AYahorau(at)ibagroup(dot)eu>,
> Cc: _pgsql-odbc(at)postgresql(dot)org_ <mailto:pgsql-odbc(at)postgresql(dot)org>,
> _MikalaiKeida(at)ibagroup(dot)eu_ <mailto:MikalaiKeida(at)ibagroup(dot)eu>
> Date: 10/07/2018 08:50
> Subject: Re: ODBC cliens is hanging if network connection is broken
> ------------------------------------------------------------------------
>
>
> On 2018/07/09 23:06, _AYahorau(at)ibagroup(dot)eu_
> <mailto:AYahorau(at)ibagroup(dot)eu>wrote:
> Hello!
>
> Thank You for quick response.
> I set *SQL_ATTR_QUERY_TIMEOUT*to 5 seconds as follows:
>
> SQLRETURN rc = SQLSetStmtAttr(hstmt, SQL_ATTR_QUERY_TIMEOUT,
> (SQLPOINTER) 5, SQL_IS_UINTEGER);
> after SQLAllocHandle for hstmt and before SQLExecDirect call.
>
> To take some time in SQLExecDirect I call the command "*SELECT
> pg_sleep(10)*".
> If an ODBC aplication does not loose network connection with the
> remote server  this command is terminated in 5 seconds after its
> invocation.
> I get the following error *57014 *(*Cancelling statement due to
> statement timeout*).
>
> In case if network connection is lost(network interface is down) my
> ODBC application is indefinitely hanging.
> Here is a gstack output of this process:*
> gstack 13359*
> #0  0x00007fdd045703b0 in __poll_nocancel () from /lib64/libc.so.6
> #1  0x00007fdd037c8626 in ?? () from /usr/lib64/libpq.so.5
> #2  0x00007fdd037c86b0 in ?? () from /usr/lib64/libpq.so.5
> #3  0x00007fdd037c6889 in PQgetResult () from /usr/lib64/libpq.so.5
> #4  0x00007fdd039fbe55 in CC_send_query_append
> (self=self(at)entry=0x10ccc60, query=query(at)entry=0x7fff2364d470 "SET
> statement_timeout = 5000", qi=qi(at)entry=0x0, flag=flag(at)entry=0,
> stmt=stmt(at)entry=0x0, appendq=appendq(at)entry=0x0) at connection.c:1921
> #5  0x00007fdd03a2e3ed in SC_execute (self=self(at)entry=0x10df840) at
> statement.c:1912
> #6  0x00007fdd03a0c596 in Exec_with_parameters_resolved
> (stmt=stmt(at)entry=0x10df840, exec_end=exec_end(at)entry=0x7fff2364d60c) at
> execute.c:450
> #7  0x00007fdd03a0d4b6 in PGAPI_Execute (hstmt=hstmt(at)entry=0x10df840,
> flag=flag(at)entry=1) at execute.c:1045
> #8  0x00007fdd03a0daac in PGAPI_ExecDirect
> (hstmt=hstmt(at)entry=0x10df840, szSqlStr=szSqlStr(at)entry=0x4014ed "SELECT
> pg_sleep(10)", cbSqlStr=cbSqlStr(at)entry=-3, flag=flag(at)entry=1) at
> execute.c:191
> #9  0x00007fdd03a3511b in SQLExecDirect (StatementHandle=0x10df840,
> StatementText=0x4014ed "SELECT pg_sleep(10)", TextLength=-3) at
> odbcapi.c:322
> #10 0x00007fdd04851432 in SQLExecDirect () from /usr/lib64/libodbc.so.2
> #11 0x0000000000400fcf in main (argc=1, argv=0x7fff2364d928) at
> odbcExample.c:163
>
> Do you have any idea what can be wrong?
>
> Thank you in advance,
> Andrei Yahorau
>
>
>
> From: "Inoue, Hiroshi" _<h-inoue(at)dream(dot)email(dot)ne(dot)jp>_
> <mailto:h-inoue(at)dream(dot)email(dot)ne(dot)jp>
> To: _AYahorau(at)ibagroup(dot)eu_ <mailto:AYahorau(at)ibagroup(dot)eu>,
> Cc: _pgsql-odbc(at)postgresql(dot)org_ <mailto:pgsql-odbc(at)postgresql(dot)org>,
> _MikalaiKeida(at)ibagroup(dot)eu_ <mailto:MikalaiKeida(at)ibagroup(dot)eu>
> Date: 08/07/2018 13:33
> Subject: Re: ODBC cliens is hanging if network connection is broken
>
> ------------------------------------------------------------------------
>
>
>
> Hi,
>
> On 2018/07/07 21:37, _AYahorau(at)ibagroup(dot)eu_
> <mailto:AYahorau(at)ibagroup(dot)eu>wrote:
> Hello PostgreSQL Community!
>
> I downloaded and installed the latest version of PostgreSQL from
> _https://www.postgresql.org/ftp/source/v10.4/_on my SLES 12 system.
> Here is my configuration of unixODBC:
>
> odbcinst,ini
> [PostgreSQL]
> Description=PostgreSQL ODBC driver
> Driver=/usr/local/lib/psqlodbcw.so
> FileUsage=1
>
> odbc.ini
> [sles12-1]
> Description = PostgreSQL connection to rtpdb
> Driver = PostgreSQL
> Database = rtpdb
> Servername = sles12-1
> Username =
> Password =
> Port = 5432
> Protocol = 7.4+
> ReadOnly = No
> RowVersioning = No
> ShowSystemTables = No
> ConnSettings =
>
> As a Driver in odbcinst.ini I specified the path to *psqlodbcw.so
> *which was downloaded from
> _https://git.postgresql.org/gitweb/?p=psqlodbc.git_and built as follows:
> ./configure
> make
> make install
>
> Could you please provide a comment concerning the following situation?
>
> I  created a simple ODBC program which calls "*SELECT
> pg_is_in_recovery()*" on the remote PostgreSQL server using ODBC API.
> It works good with this ODBC configuration. But as soon as I turn off
> network interface after *SQLConnect *call and before *SQLExecDirect*,
> it leads to *SQLExecDirect *hanging.
>
> I tried to use *statement_timeout* and
> *idle_in_transaction_session_timeout* postgresql configuration
> parameters but they were not suitable for network issue case. These
> parameters define server-side behaviour but a client considers that
> the connection is ok and it is hanging.
>
>
>
> As far as I know *SQL_ATTR_CONNECTION_TIMEOUT *ODBC attribute could
> handle this situation. Another ODBC driver for another database in
> case of similar situation returns *HYT01 *ODBC error (Connection
> timeout expired) . But I guess *SQL_ATTR_CONNECTION_TIMEOUT * is not
> supported by psqlodbc.  Am I right?
>
> How about calling SQLSetStmtAttr() with the attribute parameter
> SQL_ATTR_QUERY_TIMEOUT?
>
>
>
> PostgreSQL JDBC driver provides *socketTimeout*property which helps a
> client to be informed about network problems and to avoid an infinite
> hanging.
> In my tests it returned *08006 *sqlstate (*connection_failure*).
>
> So I have a question. Is there any psqlodbc or native PostgreSQL
> mechanism or configuration parameter which helps a client to be
> informed about network problem?
> Is there any mechanism which helps to avoid this infinite hanging for
> SQL query in this case?
>
> Could you try options about TCP keepalive using pqopt in odbc.ini
>
> pqopt = keepalives=1 keepalives_interval=5 keepalives_count=1
>
> ?
>
> regards,
> Hiroshi Inoue
>
>
> Thank You in advance,
> Andrei Yahorau