by Razvan Popian

The result set fetch size is an important setting for processing result sets in a memory efficient way in JDBC. setFetchSize() method’s javadoc of java.sql.Statement class specifies the behavior of the fetch size:

“Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects generated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero.”

PostgreSQL does not implement the above contract exactly as a value of 0 means to use the default fetch size configured in the driver properties (the name of the property is “defaultRowFetchSize”). Setting a non zero fetch size, works as one would expect for Statements and PreparedStatements, but unfortunately any fetch size set for CallableStatements is ignored. Moreover, the default value from the driver properties is used. By default, this is 0, which for PostgreSQL it means loading the entire result set in memory. This is obviously a bug that may cause memory problems when large result sets are processed.

Below, there is a piece of code that runs a PreparedStatement on a PostgreSQL database and demonstrates that the fetch size is applied properly. An Apache DBCP data source was used, thus we needed to unwrap the PostgreSQL result set and then used the Spring Framework ReflectionTestUtils class to peek into the rows member of the org.postgresql.jdbc.PgResultSet instance in order to count the number of rows loaded initially in the result set. Nothing is closed for brevity, it is assumed that the CostCenter table contains more than 10 rows:

@Test public void testPreparedStatement() throws SQLException { Connection conn = ; // get a Postgres connection PreparedStatement ps = conn.prepareStatement("select CostCenterName, CostCenterNo from CostCenter"); ps.setFetchSize(10); ps.execute(); ResultSet rs = ps.getResultSet(); System.out.println("RS fetch size: " + rs.getFetchSize() + ", should be " + ps.getFetchSize()); System.out.println("Checking what is already loaded in the RS ..."); @SuppressWarnings("unchecked") List<byte[][]> rows = (List<byte[][]>) ReflectionTestUtils.getField( ((DelegatingResultSet) ((DelegatingResultSet) rs).getDelegate()).getDelegate(), "rows"); System.out.println(rows.size() + " records already loaded."); } 

The output of the above code is:

RS fetch size: 10, should be 10 Checking what is already loaded in the RS … 10 records already loaded. 

Assuming we have the following stored function on the PostgreSQL database:

"CREATE OR REPLACE FUNCTION GetCostCenters() RETURNS REFCURSOR AS $$" + "DECLARE " + " cl1 REFCURSOR;" + "BEGIN\r\n" + " OPEN cl1 FOR " + " select CostCenterName, CostCenterNo from CostCenter;" + " RETURN cl1;" + "END;" + "$$ LANGUAGE plpgsql;" 

and that the CostCenter table has 20 records, here is a second piece of code that runs a CallableStatement on a PostgreSQL database and demonstrates that the fetch size is ignored and the entire result set is loaded in memory. The rows member is peeked at similarly to the PreparedStatement example above:

@Test public void testCallableStatement() throws SQLException { Connection conn = ; // get a Postgres connection conn.setAutoCommit(false); CallableStatement cs = conn.prepareCall("{? = call getCostCenters()}"); cs.registerOutParameter(1, Types.REF_CURSOR); cs.setFetchSize(10); cs.execute(); ResultSet rs = (ResultSet) cs.getObject(1); System.out.println("RS fetch size: " + rs.getFetchSize() + ", should be " + cs.getFetchSize()); System.out.println("Checking what is already loaded in the RS ..."); List<byte[][]> rows = (List<byte[][]>) ReflectionTestUtils.getField(rs, "rows"); System.out.println(rows.size() + " records already loaded."); } 

Assuming the CostCenter table has 20 records, the output of the above code is:

RS fetch size: 0, should be 10 Checking what is already loaded in the RS … 20 records already loaded. 

Note the entire result set was loaded in memory.

In conclusion, do not rely on the fetch size set on the CallableStatement. The only reliable way to set the fetch size for CallableStatements is the driver property. The downside is that this is a global setting so you cannot run a certain CallableStatement with fetch size x and another one with fetch size y. Unfortunately, this is still the case for driver version 42.2.17.