PostgreSQL jdbc 错误代码映射(SQLSTATE)

标签

PostgreSQL , SQLSTATE , 错误代码 , org.postgresql.util.PSQLState

背景

Does such a class enumerating the PostgreSQL error codes already exist?

Yes, it does: org.postgresql.util.PSQLState

However, there are 238 error codes listed on the page you referenced, and org.postgresql.util.PSQLState only enumerates 41 values. Of those 41 values, only 33 are from that list of PostgreSQL error codes (roughly 14% coverage).

If you require any of the other constants, you will have to enumerate those yourself.

PostgreSQL 包含250个左右的错误代码，如下

grep -c ERRCODE errcodes.txt  
250  

src/backend/utils/errcodes.txt

00000    S    ERRCODE_SUCCESSFUL_COMPLETION                                  successful_completion  
01000    W    ERRCODE_WARNING                                                warning  
0100C    W    ERRCODE_WARNING_DYNAMIC_RESULT_SETS_RETURNED                   dynamic_result_sets_returned  
01008    W    ERRCODE_WARNING_IMPLICIT_ZERO_BIT_PADDING                      implicit_zero_bit_padding  
01003    W    ERRCODE_WARNING_NULL_VALUE_ELIMINATED_IN_SET_FUNCTION          null_value_eliminated_in_set_function  
01007    W    ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED                          privilege_not_granted  
...........  
XX000    E    ERRCODE_INTERNAL_ERROR                                         internal_error  
XX001    E    ERRCODE_DATA_CORRUPTED                                         data_corrupted  
XX002    E    ERRCODE_INDEX_CORRUPTED                                        index_corrupted  

PostgreSQL jdbc 驱动封装的错误代码如下：

PSQLState

https://github.com/pgjdbc/pgjdbc/blob/8be516d47ece60b7aeba5a9474b5cac1d538a04a/pgjdbc/src/main/java/org/postgresql/util/PSQLState.java

https://jdbc.postgresql.org/documentation/publicapi/index.html

  // begin constant state codes  
  public final static PSQLState UNKNOWN_STATE = new PSQLState("");  
  
  public final static PSQLState TOO_MANY_RESULTS = new PSQLState("0100E");  
  
  public final static PSQLState NO_DATA = new PSQLState("02000");  
  
  public final static PSQLState INVALID_PARAMETER_TYPE = new PSQLState("07006");  
  
  /**  
   * We could establish a connection with the server for unknown reasons. Could be a network  
   * problem.  
   */  
  public final static PSQLState CONNECTION_UNABLE_TO_CONNECT = new PSQLState("08001");  
  
  public final static PSQLState CONNECTION_DOES_NOT_EXIST = new PSQLState("08003");  
  
  /**  
   * The server rejected our connection attempt. Usually an authentication failure, but could be a  
   * configuration error like asking for a SSL connection with a server that wasn't built with SSL  
   * support.  
   */  
  public final static PSQLState CONNECTION_REJECTED = new PSQLState("08004");  
  
  /**  
   * After a connection has been established, it went bad.  
   */  
  public final static PSQLState CONNECTION_FAILURE = new PSQLState("08006");  
  public final static PSQLState CONNECTION_FAILURE_DURING_TRANSACTION = new PSQLState("08007");  
  
  /**  
   * The server sent us a response the driver was not prepared for and is either bizarre datastream  
   * corruption, a driver bug, or a protocol violation on the server's part.  
   */  
  public final static PSQLState PROTOCOL_VIOLATION = new PSQLState("08P01");  
  
  public final static PSQLState COMMUNICATION_ERROR = new PSQLState("08S01");  
  
  public final static PSQLState NOT_IMPLEMENTED = new PSQLState("0A000");  
  
  public final static PSQLState DATA_ERROR = new PSQLState("22000");  
  public final static PSQLState NUMERIC_VALUE_OUT_OF_RANGE = new PSQLState("22003");  
  public final static PSQLState BAD_DATETIME_FORMAT = new PSQLState("22007");  
  public final static PSQLState DATETIME_OVERFLOW = new PSQLState("22008");  
  public final static PSQLState DIVISION_BY_ZERO = new PSQLState("22012");  
  public final static PSQLState MOST_SPECIFIC_TYPE_DOES_NOT_MATCH = new PSQLState("2200G");  
  public final static PSQLState INVALID_PARAMETER_VALUE = new PSQLState("22023");  
  
  public final static PSQLState INVALID_CURSOR_STATE = new PSQLState("24000");  
  
  public final static PSQLState TRANSACTION_STATE_INVALID = new PSQLState("25000");  
  public final static PSQLState ACTIVE_SQL_TRANSACTION = new PSQLState("25001");  
  public final static PSQLState NO_ACTIVE_SQL_TRANSACTION = new PSQLState("25P01");  
  public final static PSQLState IN_FAILED_SQL_TRANSACTION = new PSQLState("25P02");  
  
  public final static PSQLState INVALID_SQL_STATEMENT_NAME = new PSQLState("26000");  
  public final static PSQLState INVALID_AUTHORIZATION_SPECIFICATION = new PSQLState("28000");  
  
  public final static PSQLState STATEMENT_NOT_ALLOWED_IN_FUNCTION_CALL = new PSQLState("2F003");  
  
  public final static PSQLState INVALID_SAVEPOINT_SPECIFICATION = new PSQLState("3B000");  
  
  public final static PSQLState SYNTAX_ERROR = new PSQLState("42601");  
  public final static PSQLState UNDEFINED_COLUMN = new PSQLState("42703");  
  public final static PSQLState UNDEFINED_OBJECT = new PSQLState("42704");  
  public final static PSQLState WRONG_OBJECT_TYPE = new PSQLState("42809");  
  public final static PSQLState NUMERIC_CONSTANT_OUT_OF_RANGE = new PSQLState("42820");  
  public final static PSQLState DATA_TYPE_MISMATCH = new PSQLState("42821");  
  public final static PSQLState UNDEFINED_FUNCTION = new PSQLState("42883");  
  public final static PSQLState INVALID_NAME = new PSQLState("42602");  
  
  public final static PSQLState OUT_OF_MEMORY = new PSQLState("53200");  
  public final static PSQLState OBJECT_NOT_IN_STATE = new PSQLState("55000");  
  
  public final static PSQLState SYSTEM_ERROR = new PSQLState("60000");  
  public final static PSQLState IO_ERROR = new PSQLState("58030");  
  
  public final static PSQLState UNEXPECTED_ERROR = new PSQLState("99999");  

由于未完全对应PG的SQLSTATE，所以，建议可以使用getSQLState获取一下错误代码并输出，或者扩展PSQLState.java，映射完整的PostgreSQL SQLSTATE。

https://docs.oracle.com/javase/8/docs/api/java/sql/SQLException.html#getSQLState--

输出elog日志打印的相关源码位置

拿到了SQLSTATE代码，还不够，因为可能有多处代码报同一个错误，如果要定位更加详细的原因，可以配置数据库的log_error_verbosity参数，在报错误代码的同时，可输出源代码的位置。

例如1，在psql中，将错误代码以及源码输出到客户端：

postgres=# \set VERBOSITY verbose  
postgres=# select t.oid,(select string_agg(relname, s',') from pg_class) from pg_class t;  
ERROR:  42704: type "s" does not exist  
LINE 1: select t.oid,(select string_agg(relname, s',') from pg_class...  
                                                 ^  
LOCATION:  typenameType, parse_type.c:546  

例如2，在postgresql的错误日志中拿到错误代码以及源码.

postgres=# set log_error_verbosity ='verbose';  
SET  
postgres=# select t.oid,(select string_agg(relname, s',') from pg_class) from pg_class t;  
ERROR:  type "s" does not exist  
LINE 1: select t.oid,(select string_agg(relname, s',') from pg_class...  
                                                 ^  
  
  
错误日志:  
  
2018-07-11 13:22:40.505 CST,"enterprisedb","postgres",28166,"[local]",5b45743c.6e06,5,"SELECT",2018-07-11 11:06:36 CST,4/80,0,ERROR,42704,"type ""s"" does not exist",,,,,,"select t.oid,(select string_agg(relname, s',') from pg_class) from pg_class t;",42,"typenameType, parse_type.c:546","psql.bin"  

参考

https://stackoverflow.com/questions/40311792/postgresql-jdbc-error-code-enumeration

https://jdbc.postgresql.org/documentation/publicapi/index.html

https://github.com/pgjdbc/pgjdbc/blob/8be516d47ece60b7aeba5a9474b5cac1d538a04a/pgjdbc/src/main/java/org/postgresql/util/PSQLState.java

https://stackoverflow.com/questions/28416445/postgres-jdbc-especific-error-code-of-psqlexception

https://docs.oracle.com/javase/8/docs/api/java/sql/SQLException.html#getSQLState--

https://www.postgresql.org/docs/devel/static/errcodes-appendix.html

https://www.postgresql.org/docs/11/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

log_error_verbosity (enum)

Controls the amount of detail written in the server log for each message that is logged. Valid values are TERSE, DEFAULT, and VERBOSE, each adding more fields to displayed messages. TERSE excludes the logging of DETAIL, HINT, QUERY, and CONTEXT error information. VERBOSE output includes the SQLSTATE error code (see also Appendix A) and the source code file name, function name, and line number that generated the error. Only superusers can change this setting.