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

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