Provide data source status in order to manage auto-reconnect

[niushapaks]

Hi,

We encountered a disconnection issue with our PostgreSQL database, after our application was running during several hours.

In order to re-open the connection in these cases, we used reflection to check the status of the closed private field of the C3P0DataSource class :

Field closedField = C3P0DataSource.class.getDeclaredField("closed");
closedField.setAccessible(true);
AtomicBoolean closed = (AtomicBoolean)closedField.get(graph.getSqlgDataSource());

if(closed.get()) {
    graph = SqlgGraph.open(graphConfiguration.getConfiguration());
}

When I close manually the connection, this field is correctly updated and I am able to re-open it, but as this is a private field I am not confident using it and don't know if it will be reliable in case of random connection loose.

Is that field reliable for our needs ? If so, is it possible to provide it with a public accessor ?

If no, is there any auto-reconnect mechanism available ? Or is it possible to provide some ?

Thank you very much

[pietermartin]

I'd be hesitant to mess with the pools internal state. I'll have to investigate the pool's properties/methods to see what the recommended way is. Do you know why the disconnection is happening? Its not because people are manually closing connections instead of just executing commit or rollback?

[niushapaks]

Thanks for your response.

Our database (and our app) are deployed as containers on a cloud environnement, and these containers can be unavailables if they are not used to optimize our resources.

This can leads to unwanted disconnections between the app and the database.

[niushapaks]

Hello,

Despite the hotfix I mentionned in my first message (closed attribute of C3P0), we have one more time encountered the database connection issue after one night up and running application, and this time I got the stacktrace in order to show you more details :

java.lang.RuntimeException: org.postgresql.util.PSQLException: This connection has been closed.] with root cause
org.postgresql.util.PSQLException: This connection has been closed.
    at org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:782)
    at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1675)
    at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:371)
    at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:567)
    at org.umlg.sqlg.strategy.SqlgSqlExecutor.executeQuery(SqlgSqlExecutor.java:133)
    at org.umlg.sqlg.strategy.SqlgSqlExecutor.executeRegularQuery(SqlgSqlExecutor.java:77)
    at org.umlg.sqlg.structure.SqlgCompiledResultIterator.executeRegularQuery(SqlgCompiledResultIterator.java:251)
    at org.umlg.sqlg.structure.SqlgCompiledResultIterator.hasNextLazy(SqlgCompiledResultIterator.java:121)
    at org.umlg.sqlg.structure.SqlgCompiledResultIterator.hasNext(SqlgCompiledResultIterator.java:78)
    at org.umlg.sqlg.step.SqlgGraphStep.processNextStart(SqlgGraphStep.java:80)
    at org.apache.tinkerpop.gremlin.process.traversal.step.util.AbstractStep.next(AbstractStep.java:128)
    at org.apache.tinkerpop.gremlin.process.traversal.step.util.AbstractStep.next(AbstractStep.java:38)
    at org.apache.tinkerpop.gremlin.process.traversal.Traversal.fill(Traversal.java:179)
    at org.apache.tinkerpop.gremlin.process.traversal.Traversal.toList(Traversal.java:117)

The only way to get back the connection is to restart the application.

[pietermartin]

Are you sure the application code is not calling connection.close() ?

[pietermartin]

Not sure if you are still interested in this. C3P0 pool has a jmx interface that one can check and call operations on. I tested resetting the pool from there and it works fine. I am adding some code to make the data source's name readable.

Sqlg also makes some stats available via json. sqlgGraph.getSqlgDataSource().getPoolStatsAsJson()

[niushapaks]

Hi, I'm not working on the project using sqlg anymore, but thank you for looking on it 👍