> Is the application remote from the database server? My gut reaction to this type of report is "something timed out
thenetwork 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
suspectadds some complexity too. I had suspicions about something in the middle closing the connection too, but your
emailhas clarified my thinking a bit.
TCP Keepalive appears to be enabled on the application server and within docker, and the client holds the allegedly
deadconnection for much longer (24h) than the keepalive should take to kill it (<3h), so I think the next step is to
tryto 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@sss.pgh.pa.us> wrote:
David Wheeler <dwheeler@dgitsystems.com> 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
wesee 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