PostgreSQL jdbc 错误代码映射(SQLSTATE)

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介:

标签

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.

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
12月前
|
Java 数据库连接
27SpringBoot之JDBC(完整代码)
27SpringBoot之JDBC(完整代码)
71 0
|
12月前
|
SQL Java 数据库连接
26SpringBoot之JDBC(关键代码)
26SpringBoot之JDBC(关键代码)
69 0
|
12月前
|
SQL Java 关系型数据库
Java的JDBC编程<-----用Java代码操作数据库(MySQL数据库)
Java的JDBC编程<-----用Java代码操作数据库(MySQL数据库)
53 0
|
4月前
|
SQL XML Java
后端数据库开发JDBC编程Mybatis之用基于XML文件的方式映射SQL语句实操
后端数据库开发JDBC编程Mybatis之用基于XML文件的方式映射SQL语句实操
63 3
|
5月前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之使用JDBC方式读取Oracle的number类型时,通过什么方式进行映射
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
133 0
实时计算 Flink版产品使用合集之使用JDBC方式读取Oracle的number类型时,通过什么方式进行映射
|
5月前
|
SQL 算法 Java
jdbc(ikun)实现事务的开启,提交,回滚代码示例
jdbc(ikun)实现事务的开启,提交,回滚代码示例
|
Java 数据库连接 数据库
JDBC快速入门(三) 代码再优化
JDBC快速入门(三) 代码再优化
|
SQL 存储 Java
如何模拟MyBatis对象映射赋值的过程,以及如何通过这种方式来简化我们的JDBC开发工作?
如何模拟MyBatis对象映射赋值的过程,以及如何通过这种方式来简化我们的JDBC开发工作?
93 0
|
Java 数据库连接 数据库
使用Java代码通过JDBC驱动创建数据库
使用Java代码通过JDBC驱动创建数据库
106 0
|
存储 网络协议 Java
PostgreSQL JDBC 开发指导
Java Database Connectivity (JDBC) 是一个应用程序编程接口 (API),用于 编程语言 Java,它定义了客户端如何访问数据库。 它是Java标准版平台的一部分,提供查询和 更新数据库中的数据,并面向关系数据库。 PostgreSQL JDBC驱动程序(简称pgJDBC)允许Java程序连接到PostgreSQL®® 数据库使用标准的、独立于数据库的 Java 代码。是一个开源的JDBC驱动程序 用纯Java(类型4)编写,并使用PostgreSQL®本机网络协议进行通信。 因此,驱动程序独立于平台;编译后,驱动程序 可以在任何系统上使用。
148 0

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版
  • 下一篇
    无影云桌面