Re: PrepareStatement.execute() blocked because of long time 'create index' operation, connection leak

I learnt a lot. Many thanks.

Questions:
1. How much time later a connection (jdbc) will change from ‘alive’ to ‘idle’ state if no data exchange between client and server?
And, How to configure at OS level? (PG using OS level connection state to determine the jdbc connection state?)
2. Is there any solutions for jdbc client side, such as setting socket timeout?

> 在 2016年6月7日，20:31，Sehrope Sarkuni <sehrope(at)jackdb(dot)com> 写道：
>
> On Tue, Jun 7, 2016 at 8:17 AM, Dave Cramer <pg(at)fastcrypt(dot)com <mailto:pg(at)fastcrypt(dot)com>> wrote:
> On 7 June 2016 at 08:01, jingzhi(dot)zhang(at)outlook(dot)com <mailto:jingzhi(dot)zhang(at)outlook(dot)com> <jingzhi(dot)zhang(at)outlook(dot)com <mailto:jingzhi(dot)zhang(at)outlook(dot)com>> wrote:
> Vladimir,
>
> Thanks :)
>
> I think there’s no firewall in our test environments. However, there’s network control software at client machine.
> The network control software occasionally lost the connection.
>
> My question is, if the network connection lost, then jdbc client should return an IOException immediately?
> OR blocked forever until TCP connection killed by operating system?
>
>
> Well the problem is we don't know that the connection has failed until the TCP connection has been killed by the O/S.
>
> Vladimir is proposing keep alive messages so that either it won't fail or we will know about it sooner if it does.
>
> I don't think it's a good idea to flood the server with dummy messages for the purposes of keeping the connection alive. Besides the usual problems of filling up the send buffer, you'd also have to have it done in a separate thread as the usual sender is already blocked waiting for the command response.
>
> This an OS issue, not a driver issue. The correct thing to do is to enable TCP keep alives at the OS level. Here's some info for doing so on Linux: http://tldp.org/HOWTO/TCP-Keepalive-HOWTO/usingkeepalive.html <http://tldp.org/HOWTO/TCP-Keepalive-HOWTO/usingkeepalive.html>
>
> The defaults for TCP keep alives are very high (I think two-hours before the first attempt) so it's a good idea to reduce them if you're dealing with long running WAN connections. Also, from personal experience it's usually residential networking devices (i.e. your WiFi router) that drop inactive NATed connections. This is less of an issue in a server-to-server environment in a data center. Either way, bumping up the TCP keep alives works in both situations.
>
> Regards,
> -- Sehrope Sarkuni
> Founder & CEO | JackDB, Inc. | https://www.jackdb.com/ <https://www.jackdb.com/>