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

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?

在 2016年6月7日，19:37，Vladimir Sitnikov <sitnikov.vladimir@gmail.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