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);

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);
  }

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));
    }
@Override
public void abort() {
  try {
    pgStream.getSocket().close();
  } catch (IOException e) {
    // ignore
  }
  closed = true;
}

总结

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