PostgreSQL之IOException
PostgreSQL 是一款开源的对象关系型数据库管理系统,在使用过程中可能会遇到 IOExcption 异常,这通常是由以下原因之一导致的:
1. Sends a 2-byte integer (short) to the back end
原因:
- 使用SQL语言in时超过Short.MAX_VALUE最大数量时,抛出此异常,源代码如下:
- 第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;
}
总结
遇到代码异常后不要慌(其时大多数错误都是第一次碰见),首先仔细观查日志,并根据关键字查找源代码,排查错误产生的原因