Re: ODBC cliens is hanging if network connection is broken - Mailing list pgsql-odbc

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" <h-inoue@dream.email.ne.jp>
To:        AYahorau@ibagroup.eu,
Cc:        pgsql-odbc@postgresql.org, MikalaiKeida@ibagroup.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@ibagroup.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