PostgreSQL之IOException

PostgreSQL之IOException

PostgreSQL 是一款开源的对象关系型数据库管理系统，在使用过程中可能会遇到 IOExcption 异常，这通常是由以下原因之一导致的：

1. Sends a 2-byte integer (short) to the back end

原因：

1. 使用SQL语言in时超过Short.MAX_VALUE最大数量时，抛出此异常，源代码如下：
2. 第1点产生的异常会有一个关闭数据库连接的操作，导致业务后续数据库操作拿不到连接，从而抛出另一个错误Cause: java.sql.SQLException: connection holder is null
   
   注：在PostgreSQL数据库驱动jar的org.postgresql.core.v3.QueryExecutorImpl类中，IOExcpetion捕获后，都有一个abort();执行，方法详细代码为：pgStream.getSocket().close();
   

解决方案：

// 分批处理，每次In的数量1000条
// 可以用Hutool工具类来处理
CollUtil.split(list, 2000);
1
2
3

org.postgresql.core.PGStream;

  public void sendInteger2(int val) throws IOException {
    if (val < Short.MIN_VALUE || val > Short.MAX_VALUE) {
      throw new IOException("Tried to send an out-of-range integer as a 2-byte value: " + val);
    }

    _int2buf[0] = (byte) (val >>> 8);
    _int2buf[1] = (byte) val;
    pg_output.write(_int2buf);
  }
1
2
3
4
5
6
7
8
9

org.postgresql.core.v3.QueryExecutorImpl

try {
      try {
        handler = sendQueryPreamble(handler, flags);
        autosave = sendAutomaticSavepoint(query, flags);
        sendQuery(query, (V3ParameterList) parameters, maxRows, fetchSize, flags,
            handler, null);
        if ((flags & QueryExecutor.QUERY_EXECUTE_AS_SIMPLE) != 0) {
          // Sync message is not required for 'Q' execution as 'Q' ends with ReadyForQuery message
          // on its own
        } else {
          sendSync();
        }
        processResults(handler, flags);
        estimatedReceiveBufferBytes = 0;
      } catch (PGBindException se) {
        // There are three causes of this error, an
        // invalid total Bind message length, a
        // BinaryStream that cannot provide the amount
        // of data claimed by the length arugment, and
        // a BinaryStream that throws an Exception
        // when reading.
        //
        // We simply do not send the Execute message
        // so we can just continue on as if nothing
        // has happened. Perhaps we need to
        // introduce an error here to force the
        // caller to rollback if there is a
        // transaction in progress?
        //
        sendSync();
        processResults(handler, flags);
        estimatedReceiveBufferBytes = 0;
        handler
            .handleError(new PSQLException(GT.tr("Unable to bind parameter values for statement."),
                PSQLState.INVALID_PARAMETER_VALUE, se.getIOException()));
      }
    } catch (IOException e) {
      // 关闭数据库连接
      abort();
      handler.handleError(
          new PSQLException(GT.tr("An I/O error occurred while sending to the backend."),
              PSQLState.CONNECTION_FAILURE, e));
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

@Override
public void abort() {
  try {
    pgStream.getSocket().close();
  } catch (IOException e) {
    // ignore
  }
  closed = true;
}
1
2
3
4
5
6
7
8
9

总结

遇到代码异常后不要慌（其时大多数错误都是第一次碰见），首先仔细观查日志，并根据关键字查找源代码，排查错误产生的原因