Re: ODBC cliens is hanging if network connection is broken
| От | AYahorau@ibagroup.eu |
|---|---|
| Тема | Re: ODBC cliens is hanging if network connection is broken |
| Дата | |
| Msg-id | OFE08AF242.72702C7F-ON432582C5.004B96E0-432582C5.004D829F@iba.by обсуждение исходный текст |
| Ответ на | Re: ODBC cliens is hanging if network connection is broken ("Inoue, Hiroshi" <h-inoue@dream.email.ne.jp>) |
| Список | 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
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
В списке pgsql-odbc по дате отправления:
Следующее
От: "Inoue, Hiroshi"Дата:
Сообщение: Re: ODBC cliens is hanging if network connection is broken