U++ forum: Welcome to the forum

Re: Patch: Reconnecting PostgreSQL Automatically on Unstable Networks [message #26367 is a reply to message #26366]

Tue, 27 April 2010 20:21

mirek
Messages: 13976
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);

	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);

	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).

Report message to a moderator