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@entry=0x10ccc60, 
query=query@entry=0x7fff2364d470 "SET statement_timeout = 5000", 
qi=qi@entry=0x0, flag=flag@entry=0, stmt=stmt@entry=0x0, 
appendq=appendq@entry=0x0) at connection.c:1921
#5  0x00007fdd03a2e3ed in SC_execute (self=self@entry=0x10df840) at 
statement.c:1912
#6  0x00007fdd03a0c596 in Exec_with_parameters_resolved 
(stmt=stmt@entry=0x10df840, exec_end=exec_end@entry=0x7fff2364d60c) at 
execute.c:450
#7  0x00007fdd03a0d4b6 in PGAPI_Execute (hstmt=hstmt@entry=0x10df840, 
flag=flag@entry=1) at execute.c:1045
#8  0x00007fdd03a0daac in PGAPI_ExecDirect (hstmt=hstmt@entry=0x10df840, 
szSqlStr=szSqlStr@entry=0x4014ed "SELECT pg_sleep(10)", 
cbSqlStr=cbSqlStr@entry=-3, flag=flag@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" <[email protected]>
To:     [email protected], 
Cc:     [email protected], [email protected]
Date:   08/07/2018 13:33
Subject:        Re: ODBC cliens is hanging if network connection is broken



Hi,

On 2018/07/07 21:37, [email protected] 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