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)?
49 views (last 30 days)
Show older comments
MathWorks Support Team
on 26 Aug 2013
Edited: MathWorks Support Team
on 30 Apr 2023
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);
Accepted Answer
MathWorks Support Team
on 20 Feb 2023
Edited: MathWorks Support Team
on 30 Apr 2023
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:
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.
0 Comments
More Answers (0)
See Also
Categories
Find more on Database Toolbox in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!