Thread: ODBC cliens is hanging if network connection is broken
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?
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
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
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?
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
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
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@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: pgsql-odbc@postgresql.org, MikalaiKeida@ibagroup.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@ibagroup.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@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?
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
AYahorau@ibagroup.eu wrote: > [sles12-1] > Description = PostgreSQL connection to rtpdb > Driver = PostgreSQL > Database = rtpdb > pqopt = keepalives=1 keepalives_interval=5 keepalives_count=1 > > But unfortunately if did not take a desired effect. <https://odbc.postgresql.org/docs/config-opt.html> says that the pqopt value must be {enclosed with braces}. Alternatively, use the top-level KeepaliveTime and KeepaliveInterval options. Regars, Clemens
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=1regards,
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@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: pgsql-odbc@postgresql.org, MikalaiKeida@ibagroup.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@ibagroup.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@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?
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
Thank you so much for your explanation.
Best regards,
Andrei Yahorau
From: "Inoue, Hiroshi" <h-inoue@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: MikalaiKeida@ibagroup.eu, pgsql-odbc@postgresql.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@ibagroup.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@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: pgsql-odbc@postgresql.org, MikalaiKeida@ibagroup.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@ibagroup.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@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?
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
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@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: MikalaiKeida@ibagroup.eu, pgsql-odbc@postgresql.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@ibagroup.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@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: pgsql-odbc@postgresql.org, MikalaiKeida@ibagroup.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@ibagroup.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@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?
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
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.
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@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: MikalaiKeida@ibagroup.eu, pgsql-odbc@postgresql.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@ibagroup.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@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: MikalaiKeida@ibagroup.eu, pgsql-odbc@postgresql.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@ibagroup.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@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: pgsql-odbc@postgresql.org, MikalaiKeida@ibagroup.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@ibagroup.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@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?
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
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@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: MikalaiKeida@ibagroup.eu, pgsql-odbc@postgresql.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@ibagroup.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@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: MikalaiKeida@ibagroup.eu, pgsql-odbc@postgresql.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@ibagroup.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@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: pgsql-odbc@postgresql.org, MikalaiKeida@ibagroup.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@ibagroup.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@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?
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
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@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: MikalaiKeida@ibagroup.eu, pgsql-odbc@postgresql.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@ibagroup.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@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: MikalaiKeida@ibagroup.eu, pgsql-odbc@postgresql.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@ibagroup.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@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: MikalaiKeida@ibagroup.eu, pgsql-odbc@postgresql.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@ibagroup.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@dream.email.ne.jp>
To: AYahorau@ibagroup.eu,
Cc: pgsql-odbc@postgresql.org, MikalaiKeida@ibagroup.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@ibagroup.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@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?
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