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