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

On 7 June 2016 at 08:01, jingzhi(dot)zhang(at)outlook(dot)com <
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.

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

>
>
> 在 2016年6月7日，19:37，Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com> 写道：
>
> Jingzhi>I use jdbc to create index on a table, the sql of 'create index'
> cost about 30 minutes.
> Jingzhi>Finally I found, the jdbc client thread blocked at
> PrepareStatement.execute(), not finished.
> Jingzhi>At server side, query pg_stat_activity, the connection state
> already be ‘idle’ (index creation finished).
>
> Jingzhi, Do you have a firewall in between app and the database?
> Can it terminate the connection while index is being built?
>
> Dave>You may have to execute the execute() in a background thread to not
> block the main thread
>
> Dave, I'm afraid it looks like we need keep-alive messages for such
> long-running transactions.
> There's tcpKeepAlive, however it has no way to set specific timeout value.
> It could help to detect "broken connection" at java side, though.
>
> It boils down to plug&pray kind of solution, so everybody should ensure
> there's no firewall that could kill long-lasting TCP connections.
>
> What if we could teach pgjdbc to send a dummy command once a while?
> The drawback is it could fill up buffer at the backend side, however
> sending a sync message once every 10-15 minutes sounds quite innocent.
>
> Vladimir
>
>
>