Postgresql JDBC的 OOM问题

Abstract

最近在用亚马逊的postgre rds做数据处理的过程中 发现同步某些数据时竟然会OOM.

代码

出问题的代码：(使用的是springboot的jdbctemplate)

    @Override
    public <T> List<T> getList(String sql, RowMapper<T> rowMapper, Object... args) {
        return this.jdbcTemplate.query(sql, args, rowMapper);
    }

然后pg版本是：12.5.

分析

怀疑是jdbcdriver会一次性返回所有数据 即使是如下的处理模式：

while (rs.next()) {
   rowMapper.map(rs)
}

测试

创建一个表：

create table test (a int, b text)

然后往里面插入5k条数据 每条数据~25k。

static void write(JdbcBaseRepo jdbc, int count) {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < 1000; i ++) {
            sb.append("01234567890012345678900123456789001234567890012345678900123456789001234567890");
            if (sb.length() > 1024 * 25) {
                break;
            }
        }
        String target = sb.toString();
        while (count -- >0) {
            jdbc.insert("insert into test values(?, ?)", count, target);
        }
    }

然后在执行getList方法时做一个heapdump 我们发现PG一次性返回了所有记录(~5000条记录)
这样就不奇怪了。

如何解决？

1. 试试设置fetchSize

statement.setFetchSize(100);

不工作。
2. 然后看到了这个文章：https://jdbc.postgresql.org/documentation/head/query.html
pg官网关于sql查询的解释：

By default the driver collects all the results for the query at once. This can be inconvenient for large data sets so the JDBC driver provides a means of basing a ResultSet on a database cursor and only fetching a small number of rows.

默认情况一次性返回所有的结果ResultSet。
要想使用不一次性返回所有结果的REsultSet：以下都不能出现：

1. 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.
2. 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.
3. 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.
4. The query given must be a single statement, not multiple statements strung together with semicolons.
   最关键的是第二点： 必须不是autocommit。

所有最终这个问题的解决办法：

1. 关闭autocommit。 因为我用的是dbcp 那么就是setDefaultAutoCommit(false);
2. 设置每个statement的fetchSize为100-1000. 我是通过Proxy实现的。

    public class DynamicInvocationHandler implements InvocationHandler {

        private final Connection ori;
        private final DbType dbType;

        public DynamicInvocationHandler(Connection ori, DbType dbType) {
            this.ori = ori;
            this.dbType = dbType;
        }

        @Override
        public Object invoke(Object proxy, Method method, Object[] args)
                throws Throwable {
            if (method.getName().equals("prepareStatement")) {
                Object result = method.invoke(ori, args);
                PreparedStatement s = (PreparedStatement) result;
                s.setFetchSize(100);
                SQL_QUERY.set((String)args[0]);
                PreparedStatement wrapped = (PreparedStatement) Proxy.newProxyInstance(WrappedDataSource.class.getClassLoader(),
                        new Class[]{PreparedStatement.class}, new WrappedExecuteStatementInvocationHandler(s, dbType));
                return wrapped;
            }
            else if (method.getName().equals("createStatement")) {
                Object result = method.invoke(ori, args);
                Statement s = (Statement) result;
                s.setFetchSize(100);
                Statement wrapped = (Statement) Proxy.newProxyInstance(WrappedDataSource.class.getClassLoader(),
                        new Class[]{Statement.class}, new WrappedExecuteStatementInvocationHandler(s, dbType));
                return wrapped;
                // CallableStatement prepareCall
            }
            else if (method.getName().equals("prepareCall")) {
                Object result = method.invoke(ori, args);
                CallableStatement s = (CallableStatement) result;
                CallableStatement wrapped = (CallableStatement) Proxy.newProxyInstance(WrappedDataSource.class.getClassLoader(),
                        new Class[]{CallableStatement.class}, new WrappedExecuteStatementInvocationHandler(s, dbType));
                return wrapped;
            }
            else {
                Object result = method.invoke(ori, args);
                return result;
            }
        }

    }

3. 因为默认的autocommit是false了。 所以对于jdbctemplate的update/insert性都需要在事务中运行。 我使用的是TransactionTemplate。 如下：

    transactionTemplate = new TransactionTemplate( new DataSourceTransactionManager(datasource))
    
    @Override
    public int delete(String sql, Object... args) {
        return transactionTemplate.execute(t -> jdbcTemplate.update(sql, args));
    }