Re: ODBC cliens is hanging if network connection is broken

Hello,

Thank you for detailed explanation.

Setting TCP_USER_TIMEOUT on linux brillianly solves my problem. But on
the other hand it influences all other opened sockets and that is not
quite good.
How do you think is it necessary to add support for this option to libpq?

Best regards,
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: 12/07/2018 14:36
Subject: 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 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>
To: AYahorau(at)ibagroup(dot)eu,
Cc: MikalaiKeida(at)ibagroup(dot)eu, 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 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>
To: AYahorau(at)ibagroup(dot)eu,
Cc: pgsql-odbc(at)postgresql(dot)org, 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 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>
To: AYahorau(at)ibagroup(dot)eu,
Cc: pgsql-odbc(at)postgresql(dot)org, 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 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