Re: ODBC cliens is hanging if network connection is broken

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?

Thank You in advance,
Andrei Yahorau