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 |
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 rowresult = 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 |
|
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 rowresult = 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 #26411 is a reply to message #26402] |
Fri, 30 April 2010 10:53 |
|
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...
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
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Mar 28 21:34:38 CET 2024
Total time taken to generate the page: 0.01223 seconds
|