postgres and CursoredStream [message #654968] |
Thu, 17 February 2011 19:27 |
No real name Messages: 2 Registered: February 2011 |
Junior Member |
|
|
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 #656875 is a reply to message #654968] |
Mon, 28 February 2011 20:52 |
|
Not sure why this would work with Oracle and not Postgres, obviously something with the driver, it might fetch the entire result-set when you execute the query.
What JDBC code are you using that you say works?
Also note your fetchSize is 1000, so this will fetch 1000 rows and build 1000 objects.
You could also try pagination, or a ScrollableCursor.
James : Wiki : Book : Blog : Twitter
|
|
|
Re: postgres and CursoredStream [message #666287 is a reply to message #656875] |
Tue, 19 April 2011 18:36 |
No real name Messages: 2 Registered: February 2011 |
Junior Member |
|
|
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 13:32 |
|
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.
James : Wiki : Book : Blog : Twitter
|
|
|
Powered by
FUDForum. Page generated in 0.03591 seconds