Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » postgres and CursoredStream(obtaining the cursor can run of memory if data set too large)
postgres and CursoredStream [message #654968] Thu, 17 February 2011 19:27 Go to next message
No real name is currently offline No real nameFriend
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!
icon11.gif  Re: postgres and CursoredStream [message #656255 is a reply to message #654968] Thu, 24 February 2011 18:17 Go to previous messageGo to next message
brad Mising name is currently offline brad Mising nameFriend
Messages: 17
Registered: February 2010
Junior Member
I have run into this issue as well. Has anyone had any luck finding a solution?
Thanks
Re: postgres and CursoredStream [message #656875 is a reply to message #654968] Mon, 28 February 2011 20:52 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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 Go to previous messageGo to next message
No real name is currently offline No real nameFriend
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 Go to previous message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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
Previous Topic:MySQL character encoding
Next Topic:IndirectSet triggers raiseAddChangeEvent even when no element was added
Goto Forum:
  


Current Time: Fri Dec 19 07:26:30 GMT 2014

Powered by FUDForum. Page generated in 0.21151 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software