Why is my Postgres cursor running out of memory?

Question

I have a huge table that I'm trying to stream out to a file. However it seems that no matter what I try, Postgres is trying to pull all the data at once and running out of memory. I've read many posts here and elsewhere and I think I'm doing this "right", so why do I keep running out of memory?

Here's my code:

        Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword);

        conn.setAutoCommit(false);
        conn.setReadOnly(true);

        Statement ps = conn.createStatement(
                ResultSet.TYPE_FORWARD_ONLY, 
                ResultSet.CONCUR_READ_ONLY, 
                ResultSet.HOLD_CURSORS_OVER_COMMIT);

        ps.setFetchSize(10);

        String sql = "SELECT * FROM BIGTABLE "
             + "WHERE '20150401' BETWEEN startdate AND enddate";

        ResultSet rs = ps.executeQuery(sql);

        writeResultSet(os, rs);

The code never gets past the query execution before running out of memory.

In case it matters, this is happening in a separate thread while streaming to an open ZipOutputStream. At this point, one item has already been streamed out and I never get to the point where I can stream out this table.

I'm working with Postgres 9.3.5, and currently limiting the VM to 128MB for testing. While I could increase the heap size, I still think I shouldn't be running into this issue.

[jsyk, I've stripped out try/catch blocks and the like for clarity.]

128MB is a very small amount of memory and a large query could definitely exceed Java's memory limit. Have you tried allocating more memory? Additionally, can you please post the full error message. — Adam
– Adam, Commented Jul 24, 2015 at 17:37
@adam OP is using a small memory limit to have the test fail quicker. His intention is to achieve O(1) space requirement and a smaller heap limit will be more sensitive to detect O(n) space. — Marko Topolnik
– Marko Topolnik, Commented Jul 24, 2015 at 17:57

Marko Topolnik · Accepted Answer · 2015-07-24 17:55:59Z

2

ResultSet.HOLD_CURSORS_OVER_COMMIT

This setting asks for a holdable cursor, which is implemented client-side for Postgresql. Postgresql itself does not support native holdable cursors over the protocol used by JDBC (the Frontend/Backend protocol). Related post:

http://postgresql.nabble.com/New-significance-of-holdable-result-sets-in-Java-8-td5826656.html

Note also that even the native holdable cursor has O(n) space requirement, but it will allocate in on disk.

However, by looking at your code I see no reason why you would need a holdable cursor.

edited Jul 24, 2015 at 17:55

answered Jul 24, 2015 at 17:50

Marko Topolnik

201k31 gold badges336 silver badges455 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Didjit Over a year ago

Thanks for the info on the holdable cursor. I had been getting an error where it sounded like my connection was closing, so I turned that on. But I just removed it and I am no longer running out of memory! It's actually looking very memory-healthy. I'm wondering whether the problem I was having before was due to using a PreparedStatement instead of a simple Statement. Anyone know? Regardless, I'm over this hurdle. Thanks for the insight, Marko!

Collectives™ on Stack Overflow

Why is my Postgres cursor running out of memory?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related