Greetings,

PostgreSQL 7.1.3, FreeBSD-4.3-RELEASE, gcc 2.95.3

I'm trying to attempt to detect a failed backend connection, but a call to
PQstatus() always returns the state of the backend when the call was
made. For example, take this test code:

PGconn *pgConn;
PGresult *pgRes;
int fdPGconn;

int i = 0;
int iNewState = 0;
int iOldState = 60;

pgConn = PQconnectdb("dbname=pglogd user=postgres");

while ( i == 0 )
{
iNewState = PQstatus(pgConn);

if ( iNewState != iOldState )
{
iOldState = iNewState;
printf("Connection State [%d]\n", iNewState);

fdPGconn = PQsocket(pgConn);
printf("Connection Socket [%d]\n", fdPGconn);
}

sleep(1);
}

PQfinish(pgConn);

If you start this with the backend running, the status is CONNECTION_OK,
then pull the plug on the backend, the call to PQstatus() will still return
CONNECTION_OK, even though the backend is not running. Start this program
with the backend not running, then start the backend, PQstatus() never sees
the backend come to life...

Am I reading PQstatus() wrong? Is there any way to detect when the backend
goes down or comes back up?

Thanks,
Matthew

Search Discussions

  • Mark Pritchard at Oct 18, 2001 at 1:51 am
    I presume you are trying to re-establish a connection automatically...if
    that doesn't apply, ignore the rest of this email :)

    The way I interpreted the docs was that you can use the return codes from
    PQexec() to establish whether the command was sent to the backend correctly.
    PQresultStatus() returns whether the command was syntactically
    correct/executed OK.

    I've attached a chunk of code from a back-end independent DB driver
    (supports Oracle, PgSQL, MySQL through the same front end API), which
    implements this auto-reconnect. Take a look at the sqlExec() method.

    This code successfully recovers when used in a client connection pool in the
    following sequence:

    1) start postmaster
    2) connect through pool/driver
    3) issue SQL statements
    4) kill postmaster
    5) start postmaster
    6) issue SQL statements
    7) driver detects connection invalid, reconnects and re-issues
    automatically.

    Perhaps those infinitely more knowledgeable on the list have a better/more
    correct way of doing things?

    Cheers,

    Mark Pritchard
    -----Original Message-----
    From: pgsql-hackers-owner@postgresql.org
    On Behalf Of Matthew Hagerty
    Sent: Thursday, 18 October 2001 10:47 AM
    To: pgsql-hackers@postgresql.org
    Subject: [HACKERS] PQstatus() detect change in connection...


    Greetings,

    PostgreSQL 7.1.3, FreeBSD-4.3-RELEASE, gcc 2.95.3

    I'm trying to attempt to detect a failed backend connection, but
    a call to
    PQstatus() always returns the state of the backend when the call was
    made. For example, take this test code:

    PGconn *pgConn;
    PGresult *pgRes;
    int fdPGconn;

    int i = 0;
    int iNewState = 0;
    int iOldState = 60;

    pgConn = PQconnectdb("dbname=pglogd user=postgres");

    while ( i == 0 )
    {
    iNewState = PQstatus(pgConn);

    if ( iNewState != iOldState )
    {
    iOldState = iNewState;
    printf("Connection State [%d]\n", iNewState);

    fdPGconn = PQsocket(pgConn);
    printf("Connection Socket [%d]\n", fdPGconn);
    }

    sleep(1);
    }

    PQfinish(pgConn);

    If you start this with the backend running, the status is CONNECTION_OK,
    then pull the plug on the backend, the call to PQstatus() will
    still return
    CONNECTION_OK, even though the backend is not running. Start
    this program
    with the backend not running, then start the backend, PQstatus()
    never sees
    the backend come to life...

    Am I reading PQstatus() wrong? Is there any way to detect when
    the backend
    goes down or comes back up?

    Thanks,
    Matthew


    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
  • Matthew Hagerty at Oct 18, 2001 at 4:18 am
    I am trying to re-establish a connection, however, I cannot afford to issue
    a query to determine if the connection still exists. I'm writing a server
    that uses the asynchronous query processing functions and speed is an
    issue. Queries are slow compared to what the server does and it cannot
    wait around for a query to finish just to see if another query *should* be
    attempted based on the connection status.

    I've been digging into the libpq code to see what is going on, maybe I can
    gleam a little hint or two there... Anyone know a good *fast* way to test
    if a socket is still valid?

    Thanks,
    Matthew
    At 11:51 AM 10/18/2001 +1000, Mark Pritchard wrote:
    I presume you are trying to re-establish a connection automatically...if
    that doesn't apply, ignore the rest of this email :)

    The way I interpreted the docs was that you can use the return codes from
    PQexec() to establish whether the command was sent to the backend correctly.
    PQresultStatus() returns whether the command was syntactically
    correct/executed OK.

    I've attached a chunk of code from a back-end independent DB driver
    (supports Oracle, PgSQL, MySQL through the same front end API), which
    implements this auto-reconnect. Take a look at the sqlExec() method.

    This code successfully recovers when used in a client connection pool in the
    following sequence:

    1) start postmaster
    2) connect through pool/driver
    3) issue SQL statements
    4) kill postmaster
    5) start postmaster
    6) issue SQL statements
    7) driver detects connection invalid, reconnects and re-issues
    automatically.

    Perhaps those infinitely more knowledgeable on the list have a better/more
    correct way of doing things?

    Cheers,

    Mark Pritchard
    -----Original Message-----
    From: pgsql-hackers-owner@postgresql.org
    On Behalf Of Matthew Hagerty
    Sent: Thursday, 18 October 2001 10:47 AM
    To: pgsql-hackers@postgresql.org
    Subject: [HACKERS] PQstatus() detect change in connection...


    Greetings,

    PostgreSQL 7.1.3, FreeBSD-4.3-RELEASE, gcc 2.95.3

    I'm trying to attempt to detect a failed backend connection, but
    a call to
    PQstatus() always returns the state of the backend when the call was
    made. For example, take this test code:

    PGconn *pgConn;
    PGresult *pgRes;
    int fdPGconn;

    int i = 0;
    int iNewState = 0;
    int iOldState = 60;

    pgConn = PQconnectdb("dbname=pglogd user=postgres");

    while ( i == 0 )
    {
    iNewState = PQstatus(pgConn);

    if ( iNewState != iOldState )
    {
    iOldState = iNewState;
    printf("Connection State [%d]\n", iNewState);

    fdPGconn = PQsocket(pgConn);
    printf("Connection Socket [%d]\n", fdPGconn);
    }

    sleep(1);
    }

    PQfinish(pgConn);

    If you start this with the backend running, the status is CONNECTION_OK,
    then pull the plug on the backend, the call to PQstatus() will
    still return
    CONNECTION_OK, even though the backend is not running. Start
    this program
    with the backend not running, then start the backend, PQstatus()
    never sees
    the backend come to life...

    Am I reading PQstatus() wrong? Is there any way to detect when
    the backend
    goes down or comes back up?

    Thanks,
    Matthew


    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
  • Tom Lane at Oct 18, 2001 at 6:11 pm

    Matthew Hagerty writes:
    Anyone know a good *fast* way to test
    if a socket is still valid?
    What exactly are you trying to defend against?

    In general, I don't believe that there is any way of discovering whether
    the server is still up, other than to send it a query. (FWIW, an empty
    query string bounces back very quickly, with little processing.)

    For particular scenarios it's possible that some notification has been
    delivered to the client, but if you have had (say) a loss of network
    connectivity then there just is no other alternative. Your end isn't
    going to discover the connectivity loss until it tries to send a
    message.

    regards, tom lane
  • Matthew Hagerty at Oct 18, 2001 at 8:54 pm

    At 02:10 PM 10/18/2001 -0400, Tom Lane wrote:
    Matthew Hagerty <mhagerty@voyager.net> writes:
    Anyone know a good *fast* way to test
    if a socket is still valid?
    What exactly are you trying to defend against?

    In general, I don't believe that there is any way of discovering whether
    the server is still up, other than to send it a query. (FWIW, an empty
    query string bounces back very quickly, with little processing.)

    For particular scenarios it's possible that some notification has been
    delivered to the client, but if you have had (say) a loss of network
    connectivity then there just is no other alternative. Your end isn't
    going to discover the connectivity loss until it tries to send a
    message.

    regards, tom lane

    I was using PQstatus() under the assumption that it actually *checked* the
    connection, however I have since discovered that is simply returns the
    value in a structure, and that value only gets updated in pqReadData() or
    pqReadReady() (both of which are internal function calls.)

    What I'm doing is using the asynchronous processing to write a server that
    does not have to wait around for a query to finish (which is a slow process
    compared to what the rest of the server does.) So, using a query to test
    if the connection is up seems rather redundant and slow... I was hoping to
    come up with a faster more simple solution. If the connection is down I
    need to write - what would have been a query - to a temporary place and
    attempt a reconnect, all while going off and doing other things.

    This all came about when my main select() bailed because the backend went
    down and the socket's file-descriptor became invalid. I could probably
    catch the error in that loop, but I also want to check the connection
    *before* submitting a query... Basically, I hope to avoid a huge rewrite
    based on my assumption of how PQstatus() was actually working. ;-)

    Currently I'm looking at fnctl() or a dedicated select() call (similar to
    what pgReadReady() does), but I'm not sure of the OS overhead of these
    solutions compared to each other or an empty query. Any insight would be
    greatly appreciated.

    Thanks,
    Matthew
  • Tom Lane at Oct 19, 2001 at 12:51 am

    Matthew Hagerty writes:
    but I also want to check the connection
    *before* submitting a query...
    This strikes me as utterly pointless. You'll need to be able to recover
    from query failure anyway, so what's the value of testing beforehand?
    Send the query and see if it works or not.

    regards, tom lane
  • Peter Eisentraut at Oct 18, 2001 at 8:58 pm

    Matthew Hagerty writes:

    I am trying to re-establish a connection, however, I cannot afford to issue
    a query to determine if the connection still exists.
    But requesting that the server do something *is* the only way to know
    whether it's still alive. Another question to ask, of course, would be,
    why is your server always going down?
    I've been digging into the libpq code to see what is going on, maybe I can
    gleam a little hint or two there... Anyone know a good *fast* way to test
    if a socket is still valid?
    Try to send or receive something.

    --
    Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
  • Andreas Zeugswetter at Oct 19, 2001 at 10:04 am

    Matthew Hagerty writes:
    but I also want to check the connection *before* submitting a
    query...

    If you mean directly before the query, then forget it, as Tom already
    said :-)
    This strikes me as utterly pointless. You'll need to be able to recover
    from query failure anyway, so what's the value of testing beforehand?
    Send the query and see if it works or not.
    I see a value in checking connection status before you start doing
    loads of local work after a long idle time, that results in a query.
    In this situation I guess it is good enough to send an empty query
    even if it takes a little.

    In our projects we recv 0 bytes from the socket every x seconds
    during long idle periods to detect connection problems early.
    While it is not 100% reliable (since it does not transfer
    anything over the network) it does detect some common error situations.

    I am not 100% sure, but I think PQstatus could be patched to do that.

    Andreas

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedOct 18, '01 at 12:46a
activeOct 19, '01 at 10:04a
posts8
users5
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase