PostgreSQLjdbc錯誤程式碼對映(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.