CODEWITHSUNDEEP
c++postgresqlc++11libpqxx
PaolaJ.
PaolaJ.

Reputation: 11532

How to check if connection is still active couple hours later?

I have in working thread, which runs forever, connection to postgresql database ( in c++ using libpqxx) 

#include <pqxx/connection>

// later in code on starting thread only once executed
connection* conn= conn = new connection(createConnectionString(this->database, this->port, this->username, this->password));

How to check if connection is still active couple hours later, for example if I in meanwhile restrt postgre server ( worker thread still running and is not restarted) I should when I try to execute new query check if it is still valid and reconnect if it not. How to know if it is still alive ?

Upvotes: 2

Views: 1571

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324521

How to check if connection is still active couple hours later

Don't.

Just use it, as if it were alive. If an exception is thrown because there's something wrong, catch the exception and retry the transaction that failed from be beginning.

Attempts to "test" connections or "validate" them are doomed. There is an inherent race condition where the connection could go away between validation and actually being used. So you have to handle exceptions correctly anyway - at which point there's no point doing that connection validation in the first place.

Queries can fail and transactions can be aborted for many reasons. So your app must always execute transactions in a retry loop that detects possibly transient failure conditions. This is just one possibility - you could also have a query cancelled by the admin, a transaction aborted by the deadlock detector, a transaction cancelled by a serialization failure, etc.

To avoid unwanted low level TCP timeouts you can set a TCP keepalive on the connection, server-side or client-side.

If you really insist on doing this, knowing that it's wrong, just issue an empty query, i.e. "".

Upvotes: 5

Related Questions