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

Hi.

But do we already use SO_KEEPALIVE on the socket layer?
Also I am not really sure socket was closed on the server end. TS said he
could see server backend in the idle state. Also backed server did not
fail, so unless there were some network glitch we should have received tcp
reset.
BTW: Are there any stateful firewalls or NATs between server in client?

Best regards, Vitalii Tymchyshyn

Вт, 7 черв. 2016 08:17 користувач Dave Cramer <pg(at)fastcrypt(dot)com> пише:

>
>
>
> 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
>>
>>
>>