> Is the application remote from the database server? My gut reaction to this type of report is "something timed out the network connection", but there would have to be a router or firewall or the like in between to make that a tenable explanation.
> If that is the issue, you should be able to fix it by making the server's TCP keepalive settings more aggressive.
Yes the application server is separate from the database server, and the application is running within docker which I suspect adds some complexity too. I had suspicions about something in the middle closing the connection too, but your email has clarified my thinking a bit.
TCP Keepalive appears to be enabled on the application server and within docker, and the client holds the allegedly dead connection for much longer (24h) than the keepalive should take to kill it (<3h), so I think the next step is to try to identify the connection at the OS level with netstat to see what state it's in.
Thanks for your help.
Regards,
David
On 2/12/19, 11:17 pm, "Tom Lane" <tgl@xxxxxxxxxxxxx> wrote:
David Wheeler <dwheeler@xxxxxxxxxxxxxxx> writes:
> We have a query that our system runs nightly to refresh materialised views. This takes some time to execute (~25 minutes) and then it will usually return the results to the application and everything is golden. However occasionally we see something like the below, where the query finishes, but the connection gets unexpectedly closed from Postgres’ perspective. From the application’s perspective the connection is still alive, and it sits there forever waiting for the result.
Is the application remote from the database server? My gut reaction
to this type of report is "something timed out the network connection",
but there would have to be a router or firewall or the like in between
to make that a tenable explanation.
If that is the issue, you should be able to fix it by making the
server's TCP keepalive settings more aggressive.
regards, tom lane
