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 » Patch: Reconnecting PostgreSQL Automatically on Unstable Networks
Patch: Reconnecting PostgreSQL Automatically on Unstable Networks [message #26366] Tue, 27 April 2010 19:21 Go to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
After a network connection problem or coming back from suspend, it is convenient for your users to reconnect your app to the server automatically.

I did not want to write a lot of code dealing with this problem, so I patched PostgreSQL classes.

Using this patch PostgreSQL classes reconnect to the server on connection problems. They try to reconnect only once and not within transactions.

In void PostgreSQLSession::ExecTrans(const char * statement) change the row
result = PQexec(conn, statement);


to
	for(int i=0; i<2; i++){
		result = PQexec(conn, statement);
		if(level==0 && !ConnectionOK())
			if(!ReOpen()) return;
			else continue;
		break;
	}


and in bool PostgreSQLConnection::Execute() change the row
result = PQexecParams(conn, query, 0, NULL, NULL, NULL, NULL, 0);

to
	for(int i=0; i<2; i++){
		result = PQexecParams(conn, query, 0, NULL, NULL, NULL, NULL, 0);
		if(session.level==0 && !session.ConnectionOK())
			if(!session.ReOpen()) return false;
			else continue;
		break;
	}
Re: Patch: Reconnecting PostgreSQL Automatically on Unstable Networks [message #26367 is a reply to message #26366] Tue, 27 April 2010 20:21 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
zsolt wrote on Tue, 27 April 2010 13:21

After a network connection problem or coming back from suspend, it is convenient for your users to reconnect your app to the server automatically.

I did not want to write a lot of code dealing with this problem, so I patched PostgreSQL classes.

Using this patch PostgreSQL classes reconnect to the server on connection problems. They try to reconnect only once and not within transactions.

In void PostgreSQLSession::ExecTrans(const char * statement) change the row
result = PQexec(conn, statement);


to
	for(int i=0; i<2; i++){
		result = PQexec(conn, statement);
		if(level==0 && !ConnectionOK())
			if(!ReOpen()) return;
			else continue;
		break;
	}


and in bool PostgreSQLConnection::Execute() change the row
result = PQexecParams(conn, query, 0, NULL, NULL, NULL, NULL, 0);

to
	for(int i=0; i<2; i++){
		result = PQexecParams(conn, query, 0, NULL, NULL, NULL, NULL, 0);
		if(session.level==0 && !session.ConnectionOK())
			if(!session.ReOpen()) return false;
			else continue;
		break;
	}



I am not sure this is a good solution - what if you are in the middle of the transaction? Or in the Fetch loop?

Interestingly, I had to deal with this issue quite recently (in PGSQL). In the end I have ended with solution that:

- periodically (via 1s timer; I may make it more frequent in the future) issues "select 0" as sort of ping and automatically reconnects if this fails

- in other cases throws exception and restarts the application (because that is the only solution I consider safe in all cases).
Re: Patch: Reconnecting PostgreSQL Automatically on Unstable Networks [message #26370 is a reply to message #26367] Tue, 27 April 2010 21:19 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
Quote:

what if you are in the middle of the transaction?


This is why I don't reconnect in transaction (session.level==0).

I don't use fetch loop, but I think it can be an other exeption.
Re: Patch: Reconnecting PostgreSQL Automatically on Unstable Networks [message #26376 is a reply to message #26370] Wed, 28 April 2010 14:31 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
OK, Mirek. I can see, that this level management is absolutely useless. We have to find out some better way.
Re: Patch: Reconnecting PostgreSQL Automatically on Unstable Networks [message #26399 is a reply to message #26370] Thu, 29 April 2010 23:55 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
zsolt wrote on Tue, 27 April 2010 15:19

Quote:

what if you are in the middle of the transaction?


This is why I don't reconnect in transaction (session.level==0).

I don't use fetch loop, but I think it can be an other exeption.


Hm, just curious: How do you read anything from DB then? Smile

Mirek
Re: Patch: Reconnecting PostgreSQL Automatically on Unstable Networks [message #26402 is a reply to message #26399] Fri, 30 April 2010 01:41 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
I don't use DECLARE CURSOR and FECTH SQL statements, only simple SELECTS.

If you iterate on the result of a SELECT, it iterates on a table in RAM, AFAIK, so reconnection is not an issue here.

BTW, I changed PostgreSQLSession::Rollback() to:
void PostgreSQLSession::Rollback()
{
	ExecTrans("rollback");
	if(level>0) level--;
}


and my app is quite usable now on unstable networks.
Re: Patch: Reconnecting PostgreSQL Automatically on Unstable Networks [message #26411 is a reply to message #26402] Fri, 30 April 2010 10:53 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
zsolt wrote on Thu, 29 April 2010 19:41

I don't use DECLARE CURSOR and FECTH SQL statements, only simple SELECTS.

If you iterate on the result of a SELECT, it iterates on a table in RAM, AFAIK, so reconnection is not an issue here.



Ah, I mean U++ Fetch. Am not using cursors and FETCH either.

Well, you are right that in this PGSQL version, the result set is in RAM. It can change in future though...

(Frankly, I was quite surprised by this issue - sometimes I am loading quite big result sets a pgsql allocating 0.5G is no fun... Smile

Quote:


BTW, I changed PostgreSQLSession::Rollback() to:
void PostgreSQLSession::Rollback()
{
	ExecTrans("rollback");
	if(level>0) level--;
}


and my app is quite usable now on unstable networks.


I guess this patch cannot cause any harm -> applied.

Mirek
Re: Patch: Reconnecting PostgreSQL Automatically on Unstable Networks [message #26456 is a reply to message #26411] Tue, 04 May 2010 12:11 Go to previous messageGo to next message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
Thanks, BTW, my Scrum Tool uses this reconnecting code since last week and seems to be very useful and stable this way.

Would it be possible to include this feature if it would be optional? The default mode would be the normal behaviour and auto reconnecting mode would be an option?
Re: Patch: Reconnecting PostgreSQL Automatically on Unstable Networks [message #26630 is a reply to message #26456] Mon, 17 May 2010 14:47 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
After some thinking, I have added WhenReconnect callback that basically extends your proposal.

Mirek
Re: Patch: Reconnecting PostgreSQL Automatically on Unstable Networks [message #26633 is a reply to message #26630] Mon, 17 May 2010 15:44 Go to previous message
zsolt is currently offline  zsolt
Messages: 693
Registered: December 2005
Location: Budapest, Hungary
Contributor
Thanks, I will check and try it soon.
Previous Topic: Any way to generate sch from database?
Next Topic: SQLITE3: Bind parameter by name
Goto Forum:
  


Current Time: Thu Mar 28 21:34:38 CET 2024

Total time taken to generate the page: 0.01223 seconds