Thread: How to check for server availability? [v9.3.1, Linux]
Hi all. I'd need to check from a C language program whether the server is running or not. I was planning to use either "PGPing PQping(const char *conninfo)" or "ConnStatusType PQstatus(const PGconn *conn)". I have my program running and checking from time to time whether the connection is kicking and alive. In order to test the things I've done the following: while the program is waiting for input I simply restart the postgresql server. The issue is thayt both functions return PQPING_OK and CONNECTION_OK respectively. When I forward the request, I get an error: "FATAL: terminating connection due to administrator command". How can I reliably check the server status? TIA:
Vincenzo Romano <vincenzo.romano@notorand.it> writes:
> I'd need to check from a C language program whether the server is
> running or not.
> I was planning to use either "PGPing PQping(const char *conninfo)" or
> "ConnStatusType PQstatus(const PGconn *conn)".
> I have my program running and checking from time to time whether the
> connection is kicking and alive.
> In order to test the things I've done the following: while the program
> is waiting for input I simply restart the postgresql server.
> The issue is thayt both functions return PQPING_OK and CONNECTION_OK
> respectively.
> When I forward the request, I get an error: "FATAL: terminating
> connection due to administrator command".
> How can I reliably check the server status?
You are not actually interested in checking the server status; you
evidently want to know about the health of an existing connection.
PQping will not help that because it is meant to see if the server
would accept a new connection. PQstatus is not tremendously helpful
either since it doesn't issue any new server traffic; it's just for
seeing if the operations so far were OK. I think issuing a new
dummy query (an empty string would do) is your best bet. But why
bother? Just send your next command, whatever it is. If you don't
have another useful command to issue, you're just wasting bandwidth
and server cycles.
regards, tom lane
2012/5/18 Tom Lane <tgl@sss.pgh.pa.us>: > Vincenzo Romano <vincenzo.romano@notorand.it> writes: >> I'd need to check from a C language program whether the server is >> running or not. >> I was planning to use either "PGPing PQping(const char *conninfo)" or >> "ConnStatusType PQstatus(const PGconn *conn)". >> I have my program running and checking from time to time whether the >> connection is kicking and alive. >> In order to test the things I've done the following: while the program >> is waiting for input I simply restart the postgresql server. >> The issue is thayt both functions return PQPING_OK and CONNECTION_OK >> respectively. >> When I forward the request, I get an error: "FATAL: terminating >> connection due to administrator command". >> How can I reliably check the server status? > > You are not actually interested in checking the server status; you > evidently want to know about the health of an existing connection. > PQping will not help that because it is meant to see if the server > would accept a new connection. PQstatus is not tremendously helpful > either since it doesn't issue any new server traffic; it's just for > seeing if the operations so far were OK. I think issuing a new > dummy query (an empty string would do) is your best bet. But why > bother? Just send your next command, whatever it is. If you don't > have another useful command to issue, you're just wasting bandwidth > and server cycles. Well, a reason to check the connection health is to understand whether it neeeds a reconnection or not. PQping is actually useless: if the database restarted, PQping says it's OK, but the connection is gone. You said enough about PQstatus. So issuing a "blank" command seems to be a good idea. Possibly the only one. Well, a "ping" function trying to use the current "PGconn*" to ping the server would make a lot of sense, though. Thanks a lot, Tom, for your hint.
On Sat, May 19, 2012 at 12:09 AM, Vincenzo Romano <vincenzo.romano@notorand.it> wrote: > You said enough about PQstatus. So issuing a "blank" command seems to > be a good idea. Possibly the only one. > > Well, a "ping" function trying to use the current "PGconn*" to ping > the server would make a lot of sense, though. If it helps make your code clearer, you could send the query "/* ping */", which would be parsed by the server as a comment. I've not verified, but a recent thread here mentioned that /**/ comments (unlike double-hyphen ones) get sent to the server, so you'll see it in your logs. ChrisA
On Fri, May 18, 2012 at 8:18 AM, Chris Angelico <rosuav@gmail.com> wrote: > On Sat, May 19, 2012 at 12:09 AM, Vincenzo Romano > <vincenzo.romano@notorand.it> wrote: >> You said enough about PQstatus. So issuing a "blank" command seems to >> be a good idea. Possibly the only one. >> >> Well, a "ping" function trying to use the current "PGconn*" to ping >> the server would make a lot of sense, though. > > If it helps make your code clearer, you could send the query "/* ping > */", which would be parsed by the server as a comment. I've not > verified, but a recent thread here mentioned that /**/ comments > (unlike double-hyphen ones) get sent to the server, so you'll see it > in your logs. Or send the command: select 1;