Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » SQL statements timeout in Linux (bug?)
SQL statements timeout in Linux (bug?) [message #50414] Wed, 24 October 2018 11:40 Go to next message
Giorgio is currently offline  Giorgio
Messages: 170
Registered: August 2015
Experienced Member
Hi there,
I have an application that uses a Postgres DB as backend. The application has been developed using a wired connection to the LAN, but now it is deployed to some clients using a wifi connection. With the wifi an issue arose: the timeout when issuing SQL statements.

All my routines that connect to a database (for select/update/insert) have the following structure:

int SaveData::TotalDrums(String c)
{
	int retval;
   	SQL.ClearError();
	try {
		retval = SQL % Select(SqlSum(DRUMS))
		 .From(MYTABLE)
 		 .Where( MYFIELD == AsString(c) );
	} catch(SqlExc) {
		retval = -1;
		ErrorOK(t_("Error: ") + SQL.GetLastError());
	}
	return retval;
}


That worked well for a wired connection, but in a wifi environment sometimes the connection is unstable, so if the connection is lost, the application waits for it but if the timeout before the application "gives up" is too high, the application seems frozen and user thinks that it crashed. I made some test in Windows and in Linux Debian Stretch (the latest version of Debian). I launched the application (so it initially connects to the DB and "builds" the SQL global variable) and - before hitting a button that performs an insert into the DB - I detached the cable. In Windows the application stays idle for about 20 seconds, and after that period the SQL statement fails and the application prompts the dialog box explaining the error. In Debian the application stays idle for a very long time (during the test I sat in front of the monitor for ten minutes and the applications was still idle, so I got a coffe and when I came back, 20 minutes later, the error box was on the screen).

Is there a way to reduce the timeout, so the user does not feel like the application crashed?

Regards,
gio

Re: SQL statements timeout in Linux (bug?) [message #50415 is a reply to message #50414] Wed, 24 October 2018 12:27 Go to previous messageGo to next message
Oblivion is currently offline  Oblivion
Messages: 526
Registered: August 2007
Location: Turkey
Contributor
Hello Giorgio,

AFAIK, if you need to set the connection timeout value, you need to do it when opening a session.
E.g.
my_postgre_session.Open("host=localhost dbname=test user=test password=test connection_timeout=1000");


OTOH, if you need to set a timeout for queries (statemenst, etc.) there is a statement_timeout parameter which can only be called after a successful Open().

You should "SET" it manually.
E.g.
 SET statement_timeout = 5000


For more details about statement_timeout, see: https://www.postgresql.org/docs/9.4/static/runtime-config-cl ient.html


Best regards,
Oblivion
Re: SQL statements timeout in Linux (bug?) [message #50416 is a reply to message #50414] Wed, 24 October 2018 14:27 Go to previous messageGo to next message
Giorgio is currently offline  Giorgio
Messages: 170
Registered: August 2015
Experienced Member
Hi Oblivion,
I tried the connect_timeout option, but it did not work: it acts server side, and the problem is clearly client side (on my test I physically detached the LAN cable).
Regards,
gio
Re: SQL statements timeout in Linux (bug?) [message #50432 is a reply to message #50414] Tue, 30 October 2018 13:05 Go to previous messageGo to next message
Giorgio is currently offline  Giorgio
Messages: 170
Registered: August 2015
Experienced Member
Hi there,
I performed some further tests and I can confirm that there are some glitches in the Postgresql management.

As described before, I have abnormally long waiting time when I try to save or retrieve data from Postgresql. This usually happens with a wifi connection: if the connection is down for some reason, my application hangs for a very long time (in Windows about 20 seconds, in Linux several minutes) before getting back with an error. For my test, I physically unplugged the network cable.

So, I performed a test using libpqxx (official C++ client API for PostgreSQL) and the result is that the library notices immediately that the network is down issuing an error.

Someone does know a workaround for this problem?

[Updated on: Tue, 30 October 2018 13:06]

Report message to a moderator

Re: SQL statements timeout in Linux (bug?) [message #50433 is a reply to message #50432] Tue, 30 October 2018 13:54 Go to previous messageGo to next message
Oblivion is currently offline  Oblivion
Messages: 526
Registered: August 2007
Location: Turkey
Contributor
HEllo Giorgio,
Did you try to enable connection keepalive? (PostgreSQLSession::KeepAlive())
It is disabled by default. May be it will help.

Best regards,
Oblivion.

Re: SQL statements timeout in Linux (bug?) [message #50473 is a reply to message #50414] Mon, 05 November 2018 20:57 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 11561
Registered: November 2005
Ultimate Member
Giorgio wrote on Wed, 24 October 2018 11:40
Hi there,
I have an application that uses a Postgres DB as backend. The application has been developed using a wired connection to the LAN, but now it is deployed to some clients using a wifi connection. With the wifi an issue arose: the timeout when issuing SQL statements.

All my routines that connect to a database (for select/update/insert) have the following structure:

int SaveData::TotalDrums(String c)
{
	int retval;
   	SQL.ClearError();
	try {
		retval = SQL % Select(SqlSum(DRUMS))
		 .From(MYTABLE)
 		 .Where( MYFIELD == AsString(c) );
	} catch(SqlExc) {
		retval = -1;
		ErrorOK(t_("Error: ") + SQL.GetLastError());
	}
	return retval;
}


That worked well for a wired connection, but in a wifi environment sometimes the connection is unstable, so if the connection is lost, the application waits for it but if the timeout before the application "gives up" is too high, the application seems frozen and user thinks that it crashed. I made some test in Windows and in Linux Debian Stretch (the latest version of Debian). I launched the application (so it initially connects to the DB and "builds" the SQL global variable) and - before hitting a button that performs an insert into the DB - I detached the cable. In Windows the application stays idle for about 20 seconds, and after that period the SQL statement fails and the application prompts the dialog box explaining the error. In Debian the application stays idle for a very long time (during the test I sat in front of the monitor for ten minutes and the applications was still idle, so I got a coffe and when I came back, 20 minutes later, the error box was on the screen).

Is there a way to reduce the timeout, so the user does not feel like the application crashed?

Regards,
gio



"it is a feature"

Unfortunately, disconnecting of DB connection is normal and there is little we can do about that. Notice:

Gate1<int> WhenReconnect;

This is triggered on disconnect. In my apps, I try to reconnect. Of course, this works fine until you start doing transactions, then things get complicated...
Re: SQL statements timeout in Linux (bug?) [message #50476 is a reply to message #50473] Tue, 06 November 2018 13:36 Go to previous message
Giorgio is currently offline  Giorgio
Messages: 170
Registered: August 2015
Experienced Member
Hi Mirek,
Iagree that when the DB connection is unstable the only solution is dealing with that, but I tried to use in my application libpqxx and with that library the application recognizes that there is a connection problem and issues an error message in about 1-2 seconds, while the Postgres library of upp freezes for 20 seconds at least (in Linux several minutes). It seems that there is some problem in the timeout management.
Regards,
gio
Previous Topic: How to manage the access to a Db on an unstable connection
Goto Forum:
  


Current Time: Wed Dec 19 17:16:35 CET 2018

Total time taken to generate the page: 0.00714 seconds