> 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" <t...@sss.pgh.pa.us> wrote:
David Wheeler <dwhee...@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 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
