从大表中读取时发生java.lang.OutOfMemoryError [英] java.lang.OutOfMemoryError when reading from a large table

问题描述

我正在尝试从PostgreSQL中的一个很大的表(300GB)中预先生成报告.我做这样的事情:

I'm trying to pre generate reports from a very large table(300GB) in PostgreSQL. I do something like this:

rs = stmt.executeQuery("SELECT * FROM tbl");
System.out.println("select all finished");
while (rs.next()) {
    /* generate report and save it in report table */
    /* generated reports are not in memory, 
     * They are saved in a summary table in each iteration */
}

启动应用程序时，它会显示Exception in thread "main" java.lang.OutOfMemoryError: Java heap space.我尝试使用stmt.setFetchSize(1000)，但不能解决问题.

When I start the application it gives Exception in thread "main" java.lang.OutOfMemoryError: Java heap space. I tried to use stmt.setFetchSize(1000) But it doesn't solve the problem.

解决方案是什么?我在Debian 6.0.5和openJDK 6上使用PostgreSQL 8.4.11.

What is the solution? I'm using PostgreSQL 8.4.11 on Debian 6.0.5 and openJDK 6.

[UPDATE]

打印的堆栈跟踪显示OutOfMemoryError异常已在rs = stmt.executeQuery("SELECT * FROM tbl");行中生成.同样，System.out.println("select all finished");永远不会显示.

The printed stack trace shows that the OutOfMemoryError exception has been generated in rs = stmt.executeQuery("SELECT * FROM tbl"); line. Also System.out.println("select all finished"); never shows.

1. 我正在以autocommit模式运行.
2. stmt.getResultSetConcurrency()返回1007.
3. stmt.getResultSetHoldability()返回2.
4. rs.getType()返回1003.

1. I'm running in autocommit mode.
2. stmt.getResultSetConcurrency() returns 1007.
3. stmt.getResultSetHoldability() return 2.
4. rs.getType() returns 1003.

推荐答案

问题可能是PostgreSQL仅在少数情况下使用fetchSize.请参阅: http://jdbc.postgresql.org/documentation/91/query .html#fetchsize-example

The problem is probably that PostgreSQL only uses the fetchSize in a narrow set of circumstances. See: http://jdbc.postgresql.org/documentation/91/query.html#fetchsize-example

• 与服务器的连接必须使用V3协议.这是服务器7.4版及更高版本的默认设置(并且仅受其支持).
• 连接不得处于自动提交模式.后端在事务结束时关闭游标，因此在自动提交模式下，后端将先关闭游标，然后才能从中获取任何内容.
• 必须使用ResultSet.TYPE_FORWARD_ONLY的ResultSet类型创建该语句.这是默认设置，因此无需重写任何代码即可利用此功能，但这也意味着您无法向后滚动或以其他方式在ResultSet中跳来跳去.
• 给定的查询必须是单个语句，而不是用分号串在一起的多个语句.

• The connection to the server must be using the V3 protocol. This is the default for (and is only supported by) server versions 7.4 and later.
• The Connection must not be in autocommit mode. The backend closes cursors at the end of transactions, so in autocommit mode the backend will have closed the cursor before anything can be fetched from it.
• The Statement must be created with a ResultSet type of ResultSet.TYPE_FORWARD_ONLY. This is the default, so no code will need to be rewritten to take advantage of this, but it also means that you cannot scroll backwards or otherwise jump around in the ResultSet.
• The query given must be a single statement, not multiple statements strung together with semicolons.

因此，如果要在自动提交中执行此操作，或者使用非TYPE_FORWARD_ONLY的结果集类型执行此操作，则PostgreSQL将获取所有行.还要查看PostgreSQL JDBC 9.0-801驱动程序的源代码，看起来使用可保留的结果集也会使其获取所有行.

So if you are executing this in auto-commit, or with a resultset type other than TYPE_FORWARD_ONLY PostgreSQL will fetch all rows. Also looking at the sources of the PostgreSQL JDBC 9.0-801 driver it looks like using a holdable resultset will also make it fetch all rows.

这篇关于从大表中读取时发生java.lang.OutOfMemoryError的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！