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);
采纳的回答
MathWorks Support Team
2023-2-20
编辑：MathWorks Support Team
2023-4-30
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 个）
类别
在 帮助中心 和 File Exchange 中查找有关 Database Toolbox 的更多信息
2013-8-26
2023-4-30
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
