Omnis PostgreSQL - Windows connection problem

[Bastiaan Olij]

Hey Bob,

We've encountered this before as well. We do not go through the
automatic reconnect logic as there are issues for us if the connection
is lost mid transaction or if since the disconnect things have changed
that require the user to go through a full logon. Those are things we
could easily solve if we had kept them in mind when we started building
SQL support into our system they are harder for us to deal with right now.

As to the cause, there are three things we've encountered regularly:
1) wireless networks, though I'm on wireless myself without issues, for
some it does seem to disconnect people. The most heard of complaint is
people who jank out their cable to take their laptop into a meeting and
expect the connection to magically be picked up by their wireless
connection.
2) computer going to sleep, does it every time, Postgres no longer
detects activity on the line and will assume the connection is dead. i
suspect we'll get into more issues with this on Mavericks with App Nap
and I can only imagine similar technology will find its way into Windows
if it hasn't already (I suspect it already has, we do often here from
users who have left our app running but go to work in
Word/Excel/whatever for an hour or so, and when they come back our app
has been disconnected.
3) routers that disconnect the connection due to inactivity. This is the
main culprit that gets us every time. The more intelligent routers
monitor connections that remain open to the outside world (as they often
work as transparent proxies and thus need to maintain these
connections). If they detect the connections aren't actively used,
they'll sever the connection.

Thought there is little to be done about the first, and I hope the
second will reveal an answer in due course, like the "turning app nap
off on Mac" setting, the third one I do have a solution for that seems
to work in a lot of situations.
Postgres has a set of settings that control a keep-alive mechanism. This
keep-alive mechanism will result in the client middle ware and the
server to send occasional packets to each other to verify either end is
still there. Setting these settings properly will ensure there is enough
activity over the line for routers to keep the connection open.

The last suggestion I'd offer, but this will likely only help people who
are starting from scratch, is to not keep the connection open at all.
One of the most heard complaints we have is why our application suffers
from these disconnects while "web based competitor X's" app doesn't. Off
course with a web based solution there is no connection, only the
illusion of a connection. Every time you communicate with the server it
is a new connection that asks the server something and gets a result back.
There is a lot of work server side to validate that you are still who
you say you are and to determine whether you are still there or whether
you've closed your browser but to the user, that is all hidden. While in
reality your server may have rebooted 10 times, for the user a
connection has never been dropped and they are blissfully unawares.
That same approach could be done in your application too, when you need
something from the server, make the connection, perform your
query/queries, and disconnect again.

You'll have to implement some form of checking that happens with every
connection made to make sure certain key things haven't changed that
require a "logoff and logon" to maintain certain state sensitive things
and there are certain concurrency issues to solve, but they are no
different to the issues you need to solve if you're going web based..

Cheers,

Bas


On 11/12/13 6:20 AM, Bob Fiering wrote:
> We have a customer who is starting with our product and is having a lot of difficulties with the connection.
> Several times a day the connection is lost, where the internet connection is not lost.
> Only the connection to the database is lost.
>
> When i connect with my mac to the same server everything is fine. No problems. 
>
> Their network manager looked at things like sleep mode, ethernet settings and so on, but on their clients it seems ok.
>
> As a last effort i switched of SSL and made changes to the pg_hba config file to see if tomorrow when they start working again the problem is solved.
>
> PostgreSQL is version 9.3 and is running as a cluster on an Ubuntu 12.04 LTS server.
> Omnis is 5.2.3.
>
> Any help what so ever would be very appreciated.
>
>
> Bob_____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
>
>


-- 
Kindest Regards,

Bastiaan Olij
e-mail: bastiaan at basenlily.me
web: http://www.basenlily.me
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij