| postgres and CursoredStream [message #654968] |
Thu, 17 February 2011 14:27  |
Eclipse User |
|
|
|
Hello, I'm trying to use CursoredStream to obtain a large amount of data from postgres. But I find that the CursoredStream uses an amount of memory proportional to the size of the underlying cursor (when it should be constant). Therefore, if the cursor is too large I run out of memory.
Here's my code (in a JTA transaction).
JpaQuery<T> query = (JpaQuery<T>) ((JpaEntityManager) em.getDelegate()).createQuery(expression, entityClass);
ReadAllQuery raq = (ReadAllQuery) query.getDatabaseQuery();
raq.setFetchSize(1000);
raq.doNotCacheQueryResults();
raq.dontCascadeParts();
raq.dontMaintainCache();
raq.dontCacheStatement();
raq.useCursoredStream();
cursor = (CursoredStream)query.getSingleResult();
I've tested the exact same code connecting to oracle instead and it works perfectly (i.e., query.getSingleResult() returns in < 1s) . I've also test the same query in straight postgres jdbc, and that works perfectly as well. But the code above for postgres takes 10s of seconds depending on the number of rows, or will run out of memory if too many rows are selected.
I don't think it's an autocommit issue as it seems this is turned off within the transaction.
I've tried this with the latest eclipselink2, JPA2, postgres9 jdbc driver, etc.
Any help would be appreciated. Thanks!
|
|
|
|
|
| Re: postgres and CursoredStream [message #666287 is a reply to message #656875] |
Tue, 19 April 2011 14:36  |
| Eclipse User |
|
|
|
I figured out the problem. Autocommit needs to be set to false for postgres. I had known this, but I could not find a way to set it. I even tried:
raq.getAccessor().getConnection().setAutoCommit(false);
but this gives an NPE.
What I had to do was to alter eclipselink code itself in the class
org.eclipse.persistence.internal.databaseaccess.DatabaseAcce ssor and it finally worked:
public ResultSet executeSelect(DatabaseCall call, Statement statement, AbstractSession session) throws SQLException {
ResultSet resultSet;
session.startOperationProfile(SessionProfiler.StatementExecute, call.getQuery(), SessionProfiler.ALL);
try {
getConnection().setAutoCommit(false); // I added this line
if (call.isDynamicCall(session)) {
resultSet = statement.executeQuery(call.getSQLString());
} else {
resultSet = ((PreparedStatement)statement).executeQuery();
}
} finally {
session.endOperationProfile(SessionProfiler.StatementExecute, call.getQuery(), SessionProfiler.ALL);
}
// Allow for procs with outputs to be raised as events for error handling.
if (call.shouldBuildOutputRow()) {
AbstractRecord outputRow = buildOutputRow((CallableStatement)statement, call, session);
call.getQuery().setProperty("output", outputRow);
if (session.hasEventManager()) {
session.getEventManager().outputParametersDetected(outputRow, call);
}
}
return resultSet;
}
Please let me know if there is a better way.
|
|
|
| Re: postgres and CursoredStream [message #666658 is a reply to message #666287] |
Thu, 21 April 2011 09:32 |
| Eclipse User |
|
|
|
Auto-commit is determined by your transaction state.
To start a transaction call getTransaction().begin() on the EntityManager, and then unwrap the EntityManager to JpaEntityManager and call,
getUnitOfWork().beginEarlyTransaction()
This will have all queries use a transactional connection.
|
|
|
Powered by
FUDForum. Page generated in 0.07789 seconds
Home