Обсуждение: Problem with connection dropping

I'm seeing some intermittent issues with a client who is trying to connect to my Postgres server using the ODBC driver.  I have about 15 customers who are able to connect without issue, but one seems to be having problems. 

Here's what I'm seeing in the server's log file

xx.xxx.xxx.xxx(1351) 2009-04-10 21:08:20 EDT LOG:  connection authorized: user=xxx database=xxx
...
xx.xxx.xxx.xxx(1351) 2009-04-10 21:25:49 EDT LOG:  could not receive data from client: Connection timed out
xx.xxx.xxx.xxx(1351) 2009-04-10 21:25:49 EDT LOG:  unexpected EOF on client connection
xx.xxx.xxx.xxx(1089) 2009-04-10 21:25:49 EDT LOG:  process 24517 acquired ShareLock on transaction 2282399 after 609040.428 ms
xx.xxx.xxx.xxx(1351) 2009-04-10 21:25:49 EDT LOG:  disconnection: session time: 0:17:30.678 user=xxx database=xxx host=xx.xxx.xxx.xxx port=xxxx

My best guess is that they are connecting, something is getting hung up, and when they try to reconnect, they aren't able to do so. 

I'm somewhat stuck as to where to start looking for a solution.  Can anyone assist?

Thanks in advance--

Jeff Wigal

"Jeff Wigal (Referee Assistant)" <jeff@referee-assistant.com> writes:
> I'm seeing some intermittent issues with a client who is trying to connect
> to my Postgres server using the ODBC driver.  I have about 15 customers who
> are able to connect without issue, but one seems to be having problems.

> Here's what I'm seeing in the server's log file

> xx.xxx.xxx.xxx(1351) 2009-04-10 21:08:20 EDT LOG:  connection authorized:
> user=xxx database=xxx
> ...
> xx.xxx.xxx.xxx(1351) 2009-04-10 21:25:49 EDT LOG:  could not receive data
> from client: Connection timed out
> xx.xxx.xxx.xxx(1351) 2009-04-10 21:25:49 EDT LOG:  unexpected EOF on client
> connection

Is he allowing the connection to sit idle?  If so, this might represent
a router timeout.  What network hardware is between your machine and
this customer's?

            regards, tom lane

Not 100% sure what's going on with the network between here and there.  He's on a Time Warner or Comcast residential network between his PC in Colorado and my server down in Georgia. 

"Jeff Wigal (Referee Assistant)" <jeff@referee-assistant.com> writes:
> Not 100% sure what's going on with the network between here and there.  He's
> on a Time Warner or Comcast residential network between his PC in Colorado

Ick ...

> He's not intentionally leaving a connection open.  When things go as they
> should, the entire transaction lasts about 3-4 minutes from connection to
> disconnect.

Well, your log shows about 17 minutes between connection and timeout,
so apparently things didn't go well.  I'd like to think that Comcast
doesn't cut connections after just a minute or two of idle time, but
they might well drop 'em at ten or so minutes.

If your server platform supports it, setting tcp_keepalives_interval
to a few minutes might prevent the drop.

            regards, tom lane