Memory problems with ScrollableCursor in Payara/EclipseLink with Postgres

Question

I'm using Payara 4.1 (Eclipselink 2.6), Postgres 9.6.

I'm trying to use a ScrollableCursor to access data results that can be quite large. In testing we had gotten an java.lang.OutOfMemoryError trying to run the query so I did some heapdump troubleshooting with a smaller amount of data and found a lot of memory taken up by org.postgresql.jdbc.PgResultSet in the rows ArrayList.

Based on EclipseLink documentation you just provide one hint to get the ScrollableCursor, but after attaching the Postgres JDBC driver source code and debugging I found that it was going to load the whole data result if we didn't set the fetch size, so I added QueryHints.JDBC_FETCH_SIZE. What I'm finding though is that it is still fetching the whole query results and saving them to memory initially.

In org.postgresql.v3.QueryExecutorImpl sendOneQuery it only sets the initial rows to the fetch size if the QUERY_FORWARD_CURSOR flag is set.

In org.postgresql.jdbc.PgStatement executeInternal it only sets the QUERY_FORWARD_CURSOR flage if there is a fetch size, the result set isn't scrollable, the connection isn't autoCommit, and the result set isn't holdable.

When debugging my connection is set to autocommit even though I'm calling from a @TransactionAttribute(TransactionAttributeType.REQUIRED) in a @Stateless ejb called from another @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW) method in another @Stateless ejb.

I don't see why my query seems to be using a new connection defaulted to autocommit. Before I was using QueryHints.READ_ONLY which is non-transactional, so it probably uses a new connection, but I removed that. Is one of my other query hints causing a new connection? Is there an additional hint I should be using? Is there a bug with Payara/Eclipselink and Postgres so that it never actually scrolls when using ScrollableCursor?

private static <T> void executeScrollableQuery(@NotNull final Query query, final Consumer<T> recordConsumer) {
    query.setHint(QueryHints.RESULT_SET_TYPE, ResultSetType.ForwardOnly)
            .setHint(QueryHints.SCROLLABLE_CURSOR, HintValues.TRUE)
            .setHint(QueryHints.MAINTAIN_CACHE, HintValues.FALSE).setHint(QueryHints.JDBC_FETCH_SIZE, 500);

    ScrollableCursor cursor = null;
    try {
        cursor = (ScrollableCursor) query.getSingleResult();
        while (cursor.hasNext()) {
            recordConsumer.accept((T) cursor.next());
        }
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
}

Tim · Answer 1 · 2017-11-17T22:18:17.717

As far as I can tell it is a bug with the Postgres JDBC driver. As a work around I was able to pass the entity manager in too and unwrap the connection on the entity manager before getting the query result and this made the connection not autocommit so that it used the supplied fetch size and didn't get the whole query results in memory.

I think the reason this works is that unwrapping the connection makes the transaction dirty, and then the cursor reads using the write connection instead of a new autocommit read connection.

    private static <T> void executeScrollableQuery(@NotNull final EntityManager em, @NotNull final Query query,
        final Consumer<T> recordConsumer) {
    query.setHint(QueryHints.RESULT_SET_TYPE, ResultSetType.ForwardOnly)
            .setHint(QueryHints.SCROLLABLE_CURSOR, HintValues.TRUE)
            .setHint(QueryHints.MAINTAIN_CACHE, HintValues.FALSE).setHint(QueryHints.JDBC_FETCH_SIZE, 500);

    em.unwrap(Connection.class);

    ScrollableCursor cursor = null;
    try {
        cursor = (ScrollableCursor) query.getSingleResult();
        while (cursor.hasNext()) {
            recordConsumer.accept((T) cursor.next());
        }
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
}

Ok, based on this documentation I guess the postgres driver may be working as intended and it's really an issue with payara/eclipselink that it's allowing an autocommit connection to be used with the eclipselink.cursor.scrollable hint.

Memory problems with ScrollableCursor in Payara/EclipseLink with Postgres

1 Answers1