"lost connection"-Handling not possible!?

[PW]

Hello,

Im using the ZConnection and a view ZQuerys to connect and work with the PostgreSQL 8.3.
For security reasons I need to know when the connection got lost. But this is probably not possible.
At program start I connect to the DataBase. Everything is fine and works. Now I shut down the DataBase to simulate a failure. But the ZConnection.Connected is still "true". In fact the socket-connection is closed, I checked this.
So far so good. Now a Query makes a request, which has to go wrong. "SQL Error: no connection to the server" At that point I can't get the whole thing working again wthout restarting the application. The application ought to work again, if I restart the DataBase. But there is nothing I can do. I tried ZConnection.Connect, Disconnect, Reconnect; ZQuery.close, and so on... Everytime I get a Access-Violation Class C0000005 either in TZPostgreSQL8PlainDriver.Finish or in TZPostgreSQL8PlainDriver.ExecuteQuery.

Why doesn't the ZConnection detect a connection failure? It only does if I Reconnect, but I cannot do this before every request. And why is the application lost, if the ZQuery detects the connection lost?

[mdaems]

Hi,

Can you tell if there's a ping API call for Postgres as we use for mysql? Best would be if this ping also tries to reconnect on fail. Then we can implement it allowing you to check the connection before doing query actions on a bad connection.

Mark

[seawolf]

Taking a look here function PQStatus can be used to determine if a connection is good or bad

So, like mySQL you can add this function to src\dbc\ZdbcPostgresSql.pas

{**
Ping Current Connection's server, if client was disconnected,
the connection is resumed.
@return 0 if succesfull or error code if any error occurs
}
function TZPostgreSQLConnection.PingServer: Integer;
const
PING_ERROR_ZEOSCONNCLOSED = -1;
var
Closing: boolean;
begin
Closing := FHandle = nil;
if Closed or Closing then Result := PING_ERROR_ZEOSCONNCLOSED
else
begin
if FPlainDriver.GetStatus(FHandle) = CONNECTION_OK then
Result := 0
else
Result := 1;
end;
end;

declaring, on TZPostgreSQLConnection component,

function PingServer: Integer; override;

[mdaems]

Thanks Seawolf,

I did add a little code to do a reset when the connection fell away so reconnect should happen automatically if possible. (meaning the server is available again)

As this is an isolated function that wasn't implemented for this driver yet, I want to add it to the 6.6-patches branch, provided somebody tests it!
For the exact patch look here (SVN Rev. 547).

Mark

[PW]

Thanks for your replys!

But it doesn't work. I downloaded the new "ZDbcPostgreSql.pas"-File replaced the old one and recompiled the Zeos components.
The new code seems to work, I debugged it. But when I shut down the Server, the ZConnection does not change its behaviour. Neither "Closed" is true nor FPlainDriver.GetStatus(FHandle) is not CONNECTION_OK. So ZConnection.PingServer is always true.

Did I something wrong?

[mdaems]

No, you didn't. Seems like the getstatus function does NOT make a trip to the server to check the connection status.
Does anybody know what the 'cheapest' call to the server is you can make using the API? 'select 1;' looks like a good candidate, but some function getting information from the server without doing an actual query would be better.

Mark

[PW]

Maybe "version()" is good enough? But I think this is a SQL-function...

[mdaems]

Just checked the postgres IRC channel. Their advise : add a call to pqExec with an empty query string before pqGetstatus...
I did so and now it works like a charm...

Full patch here (SVN Rev. 550)

Mark

[PW]

It really works like a charm! Thanks!

[Claudiu]

I have been searching for this solution for some time now. Thank you!

[isnotgood]

Hello,

I have the same problem of disconnection I downloaded the rev 734 for ZDbcPostgreSql (and rebuild all). See bug report http://zeosbugs.firmos.at/view.php?id=187
I use postgresql 8.3.8 and Zeoslib 6.6.5 stable.

To test I made a simple project with a TZConnection, TZQuery, TDBGrid and a TTimer. Every second the timer refresh the zquery

When I stop postgresql to simulate a connection failure or unplug the lan wire. The Connected property is always true.
And I have error messages like:
Exception in EAccessViolation libpq.dll 0000A23C. Access violation at address 1000A23C in module libpq.dll ...

The function PingServer is it used automatically ?

[mdaems]

No, Pingserver isn't called automatically. That would be too costly for most applications. So before requerying you should call pingserver yourself.

Mark

[isnotgood]

Ok thank you. I will do this.