CODEWITHSUNDEEP
javapostgresqljdbcmemory-managementgarbage-collection
beyfort
beyfort

Reputation: 63

memory leak when executing sql query using postgres and java

I need to run some queries using java on postgres DB. The problem is when I execute a query using this code me memory increase and even I close the statement and the result set the memory still increasing. Off course the GC will be executed, but the problem is that sometimes the GC is executed in the middle of a query execution and it change the time execution of my query. How I can solve this problem to prevent GC executing in the middle of SQL query and it is possible to prevent memory leak in my case?

enter image description here

 public long getStatementExecutionTimeInMilliseconds( final String sqlQuery, 
final int fetchSize, final boolean fetchAttributesFlag,
            Integer numberOfSelectedFields) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        int numberOfResult = 0;
        if (numberOfSelectedFields == null) {
            numberOfSelectedFields = getNumberOfSelectedFields(sqlQuery);
        }
        long start = 0;
        try {
            stmt = createStatement (fetchSize);
            stmt.executeQuery (sqlQuery);
            start = System.currentTimeMillis ();
            rs = stmt.executeQuery (sqlQuery);
            while (rs.next ()) {
                if (fetchAttributesFlag) {
                    for (int i = 1; i <= numberOfSelectedFields; i++) {
                        rs.getInt (i);
                    }
                }
            }

            stmt.close ();
        }

        finally {
            DatabaseUtils.tryClose(rs);
            DatabaseUtils.tryClose(stmt);
        }
        //System.out.println (numberOfResult);
        final long executionTimeInMilliseconds = System.currentTimeMillis() - start;


        return executionTimeInMilliseconds;
    }

Upvotes: 0

Views: 2555

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247950

The PostgreSQL JDBC driver snarfs the whole result set into RAM by default.

To change that, set the fetch size to a value different from 0. Then cursors will be used, and memory consumption should be stable.

From the documentation:

// make sure autocommit is off
conn.setAutoCommit(false);
Statement st = conn.createStatement();

// Turn use of the cursor on.
st.setFetchSize(50);

Upvotes: 1

Related Questions