Spring Boot中PostgreSQL DELETE触发器异常前端未正确接收问题

在Spring Boot Web应用中处理PostgreSQL BEFORE DELETE触发器抛出的异常时遇到问题：发起DELETE请求时，PostgreSQL触发器会检查数据是否可删除，若不可删除则抛出自定义异常，期望通过Spring全局异常处理器捕获并将该自定义错误信息返回给前端。使用Postman测试该DELETE请求时，能正常获取PostgreSQL返回的自定义错误信息；但通过前端发起相同请求时，收到的却是事务提交失败的嵌套异常信息，而非触发器抛出的详细错误内容。

触发器代码 #

CREATE OR REPLACE FUNCTION iot.f_bd_reset_mepo_references()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    haveRule integer := 0;
    haveEchart integer := 0;
    haveAlarm boolean := false;
    ruleName character varying (20);
    echartName character varying (20);

BEGIN
    SELECT COUNT(*), cond_is_active INTO haveRule, haveAlarm 
    FROM condition 
    WHERE cond_mepo_code = OLD.mepo_code AND client_code = OLD.client_code 
    GROUP BY cond_is_active;
    
    SELECT r.rule_code INTO ruleName
    FROM condition c
    JOIN event e ON c.cond_event_code = e.pk_event
    JOIN rule r ON e.pk_event = r.rule_event
    WHERE c.cond_mepo_code = OLD.mepo_code AND r.client_code = OLD.client_code; 

    SELECT COUNT(*), echart_code INTO haveEchart, echartName 
    FROM echartconfig 
    WHERE echart_pk_mepo = OLD.pk_measure_point AND client_code = OLD.client_code 
    GROUP BY echart_code;

    IF (haveAlarm = TRUE) THEN
        RAISE EXCEPTION 'Unable to delete measurement point because the alarm "%" is in progress.', ruleName;
    ELSIF (haveRule > 0) THEN
        RAISE EXCEPTION 'Unable to delete measurement point because it is referenced to the rule "%"', ruleName;
    ELSIF (haveEchart > 0) THEN
        RAISE EXCEPTION 'Unable to delete measurement point because it is referenced to the graph "%"', echartName;
    ELSE
        DELETE FROM alarm WHERE alarm_mepo_code = OLD.pk_measure_point AND client_code = OLD.client_code;
        DELETE FROM echartconfig WHERE echart_pk_mepo = OLD.pk_measure_point AND client_code = OLD.client_code;    
        DELETE FROM feedback WHERE sens_code = OLD.mepo_code AND client_code = OLD.client_code;    
    END IF;
    
    RETURN OLD;
END; 
$BODY$;

全局异常处理器代码 #

import org.postgresql.util.PSQLException;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.context.request.WebRequest;

@ControllerAdvice
public class GlobalExceptionHandler {

    @ExceptionHandler(PSQLException.class)
    public ResponseEntity<?> handlePSQLException(PSQLException ex, WebRequest request) {
        String message = ex.getServerErrorMessage().getMessage();
        return new ResponseEntity<>(message, HttpStatus.BAD_REQUEST);
    }

}

疑问 #

1. 为何PostgreSQL返回的自定义错误信息能在Postman中正常接收，却无法正确传递到前端？
2. 在Spring Boot中处理PostgreSQL触发器异常，有什么更优的方式确保前端能正确显示错误信息？

解答 #

问题1原因 #

Postman能正常接收是因为异常直接由JDBC层抛出并被全局处理器捕获；而前端请求时，通常是因为Spring事务管理（如@Transactional注解）将PSQLException包装成了TransactionSystemException或其他嵌套异常，导致只捕获PSQLException的全局处理器逻辑未触发，返回的是外层异常的通用信息。此外，前端请求可能触发了额外的拦截器、过滤器，或者内容协商配置不同，导致异常信息被二次处理。

问题2优化方案 #

1. 捕获嵌套异常中的PSQLException

修改全局异常处理器，递归查找嵌套异常中的PSQLException，确保无论异常被多少层包装都能提取到原始错误信息：

@ControllerAdvice
public class GlobalExceptionHandler {

    @ExceptionHandler(PSQLException.class)
    public ResponseEntity<?> handlePSQLException(PSQLException ex, WebRequest request) {
        String message = ex.getServerErrorMessage().getMessage();
        return new ResponseEntity<>(message, HttpStatus.BAD_REQUEST);
    }

    // 兜底处理所有异常，提取嵌套的PSQLException
    @ExceptionHandler(Exception.class)
    public ResponseEntity<?> handleGlobalException(Exception ex, WebRequest request) {
        Throwable rootCause = ex;
        // 递归找到最底层的异常
        while (rootCause.getCause() != null && !(rootCause instanceof PSQLException)) {
            rootCause = rootCause.getCause();
        }
        if (rootCause instanceof PSQLException) {
            String message = ((PSQLException) rootCause).getServerErrorMessage().getMessage();
            return new ResponseEntity<>(message, HttpStatus.BAD_REQUEST);
        }
        // 其他异常返回通用提示
        return new ResponseEntity<>("服务器内部错误", HttpStatus.INTERNAL_SERVER_ERROR);
    }
}

2. 业务层转换异常

在Service层手动捕获PSQLException，转换为自定义业务异常，再由全局处理器处理，避免事务包装异常：

// 自定义业务异常
public class BusinessException extends RuntimeException {
    public BusinessException(String message) {
        super(message);
    }
}

// Service层代码
@Transactional(rollbackFor = Exception.class)
public void deleteMeasurePoint(String mepoCode, String clientCode) {
    try {
        measurePointRepository.deleteByMepoCodeAndClientCode(mepoCode, clientCode);
    } catch (PSQLException ex) {
        // 提取触发器的错误信息，抛出自定义异常
        throw new BusinessException(ex.getServerErrorMessage().getMessage());
    }
}

// 全局处理器新增自定义异常处理
@ExceptionHandler(BusinessException.class)
public ResponseEntity<?> handleBusinessException(BusinessException ex, WebRequest request) {
    return new ResponseEntity<>(ex.getMessage(), HttpStatus.BAD_REQUEST);
}

3. 统一响应格式

返回结构化的响应体，确保前端能稳定解析错误信息，同时适配跨场景需求：

// 统一响应体
public class ApiResponse<T> {
    private int code;
    private String message;
    private T data;

    // 构造方法、getter、setter省略
}

// 修改全局处理器返回格式
@ExceptionHandler(BusinessException.class)
public ResponseEntity<ApiResponse<Void>> handleBusinessException(BusinessException ex, WebRequest request) {
    ApiResponse<Void> response = new ApiResponse<>();
    response.setCode(HttpStatus.BAD_REQUEST.value());
    response.setMessage(ex.getMessage());
    return new ResponseEntity<>(response, HttpStatus.BAD_REQUEST);
}

4. 检查事务配置

确保@Transactional注解的rollbackFor属性包含Exception.class，避免Spring默认只回滚RuntimeException而吞掉部分异常：

@Transactional(rollbackFor = Exception.class)
public void deleteMeasurePoint(String mepoCode, String clientCode) {
    // 业务逻辑
}

内容的提问来源于stack exchange，提问作者Salvatore Montagna