Обсуждение: Connecting to a postgreSQL database with windows CE over wi-fi; failing gracefully

Hello,

I'm developing a PostgreSQL application for Windows CE 5 on a PDT/ PDA
in C++/Qt, using Hiroshi Saito's libpq port for that platform. Since
the connection is established over wi-fi, and wi-fi connectivity is
often flaky, I feel that I have to "fail gracefully" to as great an
extent as possible. The following utility function,
VerifyDbConnection(), is called before every piece of database work:

void MainInterface::VerifyDbConnection()
{
    if (PQstatus(conn) != CONNECTION_OK)
    {
        for(;;)
        {
            PQreset(conn);
            if(PQstatus(conn) == CONNECTION_OK)
                return;

            QMessageBox msgbox_connection_lost;
            msgbox_connection_lost.setText("Connection to the database is lost,
and cannot be re-established. "
                "This may be due to the fact that you're too far away from the
Wi-fi access point, or because "
                "the backoffice computer is turned off, or it may be a low level
connectivity problem." );
            QPushButton* retry_button =
msgbox_connection_lost.addButton(tr("Reconnect"),
QMessageBox::ActionRole);
            QPushButton* exit_button =
msgbox_connection_lost.addButton(tr("Exit"), QMessageBox::ActionRole);
            msgbox_connection_lost.setWindowTitle("Connection lost");
            msgbox_connection_lost.exec();

            if(msgbox_connection_lost.clickedButton() == retry_button)
            {
                continue;
            }
            else if(msgbox_connection_lost.clickedButton() == exit_button)
            {
                exit(0);
                return;
            }
        }
    }

}

This seemed to work fine initially; I'd abruptly stop the database
server, and I would see a messagebox informing me of a connectivity
problem. Then, I'd start the server, click the retry button, and have
connectivity restored. However, when I walk out of range of the wi-fi
access point, which is the probable cause of losing connectivity in
the real world, my program crashes without displaying an error message
(I would expect to see a visual C++ runtime error message).

Can someone suggest a reason for this, or a workaround?

Regards,
Peter Geoghegan

On 2009-04-29, Peter Geoghegan <peter.geoghegan86@gmail.com> wrote:
> Hello,
>
> I'm developing a PostgreSQL application for Windows CE 5 on a PDT/ PDA
> in C++/Qt, using Hiroshi Saito's libpq port for that platform. Since
> the connection is established over wi-fi, and wi-fi connectivity is
> often flaky, I feel that I have to "fail gracefully" to as great an
> extent as possible. The following utility function,
> VerifyDbConnection(), is called before every piece of database work:

such an approach is doomed to failure, you have just implemented a
race condition. what happens if the server becomes unavailable between
the request and the response, or during the response.

Also, test by pulling out the ethernet from the server to the switch.

when you shut the server down it announces this to the clients, it may
even ask their permission before proceeding.

> This seemed to work fine initially; I'd abruptly stop the database
> server, and I would see a messagebox informing me of a connectivity
> problem. Then, I'd start the server, click the retry button, and have
> connectivity restored. However, when I walk out of range of the wi-fi
> access point, which is the probable cause of losing connectivity in
> the real world, my program crashes without displaying an error message
> (I would expect to see a visual C++ runtime error message).

> Can someone suggest a reason for this, or a workaround?

you are not checking the return values from some of your libpq calls.

I apologise for the duplicate posts - my initial post wasn't appearing
on the mailing list archives hours later, and someone in #postgresql
said that there was a problem with the service provider, so I thought
I'd resend.

> such an approach is doomed to failure, you have just implemented a
> race condition. what happens if the server becomes unavailable between
> the request and the response, or during the response.

It had occurred to me that this was the case. However, even though the
suggested approach is sub-optimal,  it still significantly ameliorates
the problem - the server may be shut down intentionally by someone
that is not aware that the handset is being used much of the time, and
that is unlikely to occur the instant between checking the connection
is all right and using the connection. If you can suggest an
alternative approach that doesn't have a race condition, I'm all ears.
Also, maybe you should give the hyperbole a rest.

> when you shut the server down it announces this to the clients, it may
> even ask their permission before proceeding.

Yes, that too had occurred to me. I recall that Slony-I really hates
it when you cut the connection, but is fine when you shut down
PostgreSQL correctly. From the Slony-I docs : "Any problems with that
connection can kill the connection whilst leaving "zombied" database
connections on the node that (typically) will not die off for around
two hours." It doesn't necessarily follow that my program should die
though, but it does. My guess is that libpq is calling abort() or
something similar, directly or indirectly. I know that would cause an
error message with windows XP, but windows CE is funny.

> you are not checking the return values from some of your libpq calls.

The only thing that I don't check the return value of is PQreset,
which returns void. What do you mean?

Regards,
Peter Geoghegan

Peter Geoghegan wrote:

> though, but it does. My guess is that libpq is calling abort() or
> something similar, directly or indirectly. I know that would cause an
> error message with windows XP, but windows CE is funny.

Maybe this is a stupid question (I don't really do WinCE) but ... can't
you just run your app in a debugger, with breakpoints set in the  major
runtime library exit points, so you can get a backtrace at exit time?

Since you're dropping the network, you can't do remote debugging over
wifi, but WinCE must surely offer serial or USB remote debugging...

--
Craig Ringer

It now appears that I was presumptuous in blaming libpq for my
application's shutdown. It probably was down to a problem with the way
I was remote debugging the application - a fluke.

I can now get the message to appear, and then re-establish a
connection by either disconnecting and reconnecting the ethernet cable
that connects the postgreSQL server to its switch, or by walking out
of and into range of the wi-fi access point with the handheld. Both
work consistently. I was able to consistently lose and regain a
connection as desired.

I guess I'm happy so. Can my approach be improved upon?

Regards,
Peter Geoghegan

Peter Geoghegan <peter.geoghegan86@gmail.com> writes:
> I'm developing a PostgreSQL application for Windows CE 5 on a PDT/ PDA
> in C++/Qt, using Hiroshi Saito's libpq port for that platform. Since
> the connection is established over wi-fi, and wi-fi connectivity is
> often flaky, I feel that I have to "fail gracefully" to as great an
> extent as possible. The following utility function,
> VerifyDbConnection(), is called before every piece of database work:

> void MainInterface::VerifyDbConnection()
> {
>     if (PQstatus(conn) != CONNECTION_OK)
>     {
>    ... try to recover ...
>     }
> }

PQstatus does *not* probe to see if there's a live connection to the
database; it just returns the state left behind by the last operation.
So this code will just fall through and not do anything useful until
after you've already had a failure.  The forcible PQreset is probably
not the most graceful way of recovering, either.

What I'd try is a "ping" to the database server, and not initiate any
libpq operation unless the server is answering pings.  If you get
a failure due to connectivity loss midway through an operation,
PQreset is appropriate then --- but don't do it to recover from
a momentary network outage.

            regards, tom lane

Wow, a response from the famous Tom Lane to my lame problem :-) .

> What I'd try is a "ping" to the database server, and not initiate any
> libpq operation unless the server is answering pings.  If you get
> a failure due to connectivity loss midway through an operation,
> PQreset is appropriate then --- but don't do it to recover from
> a momentary network outage.

That's interesting. In general, it's hard to ping from a windows
program, unless you want to repeatedly invoke ping.exe. I'm not sure
that I can even retrieve the result of that. That strikes me as fairly
kludgey - acceptable for some utility shellscript, but probably not
for what is supposed to be a responsive program.

Perhaps it would be preferable to call the function after the
operation, but before the application reports success. I'll look into
it.

Regards,
Peter Geoghegan