Why do I receive a Java out of memory error when trying to query a large table held in a PostgreSQL database with the EXEC function in the Database Toolbox 4.0 (R2012b)?

I am trying to access a large table held in a PostgreSQL database using a JDBC driver. When I execute the following command:

curs = exec(conn, 'select * from tableName')

the following error message is received:

ERROR: Error using cursor (line 180)
Java exception occurred:
java.lang.OutOfMemoryError: Java heap space

Error in database/exec (line 34)
curs=cursor(connect,sqlQuery);

Answer 1

[MathWorks Support Team]

This behavior occurs because the PostgreSQL JDBC driver caches all the results of the query in memory when the query is executed.

To work around this issue the following may be tried:

1. Increase the Java heap memory (consider trying the maximum allowable). To do this please refer to the following solution:

https://www.mathworks.com/matlabcentral/answers/92813-how-do-i-increase-the-heap-space-for-the-java-vm-in-matlab

2. Try the ODBC driver as this driver appears not to cache all the results. Note that this may require a switch to 32-bit MATLAB if a 64-bit PostgreSQL driver is not available.

3. Write a custom Java solution in MATLAB that sets the 'FetchSize' and also the autocommit property to off. For example:

conn = database(instance, username, password, 'Vendor', 'PostGreSQL', 'Server', 'localhost', 'PortNumber', XXXX')
set(conn, 'AutoCommit', 'off')
h = conn.Handle
stmt = h.createStatement()
stmt.setFetchSize(50)
rs = stmt.executeQuery(java.lang.String('select * from tableName where productnumber <= 3000000'))

Then the resultset object (rs) may be processed (ideally in batches). Please see the attached example code (fetchInBatches.m) which shows how to achieve this. Note that this is just an example and may require appropriate customizations, error checks etc.