Spring Boot doesn't catch SQLState custom exception from PostgreSQL trigger – Angular gets 500 but Postman gets 400

Question

I'm working on a Spring Boot + PostgreSQL project where I have a trigger function that raises custom exceptions using RAISE EXCEPTION with SQLSTATE codes.

Here is the PostgreSQL trigger function:

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;
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 COUNT(*) INTO haveEchart
    FROM echartconfig
    WHERE echart_pk_mepo = OLD.pk_measure_point AND client_code = OLD.client_code
    GROUP BY echart_code;

    IF haveAlarm THEN
        RAISE EXCEPTION SQLSTATE 'P0001'; -- Measure point has an alarm in progress
    ELSIF haveRule > 0 THEN
        RAISE EXCEPTION SQLSTATE 'P0002'; -- Referenced to a rule
    ELSIF haveEchart > 0 THEN
        RAISE EXCEPTION SQLSTATE 'P0003'; -- Referenced to a graphic
    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;
        RETURN OLD;
    END IF;
END;
$BODY$;

In my Spring service, I added:

@Transactional(rollbackFor = {SQLException.class})
public void deleteById(Integer id) {
    repository.deleteById(id); // Triggers the DB function
}

I added rollbackFor = {SQLException.class} to ensure Spring rolls back the transaction if the trigger throws a SQL error.

My global exception handler looks like this:

@ControllerAdvice
public class GlobalExceptionHandler {

    @ExceptionHandler(Exception.class)
    public ResponseEntity<?> handleAnyException(Exception ex, WebRequest request) {
        Throwable root = findSQLException(ex);
        if (root instanceof SQLException) {
            String sqlState = ((SQLException) root).getSQLState();
            String customMessage = mapErrorMessage(sqlState);
            return new ResponseEntity<>(customMessage, HttpStatus.BAD_REQUEST);
        }

        return new ResponseEntity<>("Internal Server Error: " + ex.getMessage(), HttpStatus.INTERNAL_SERVER_ERROR);
    }

    private Throwable findSQLException(Throwable ex) {
        while (ex != null) {
            if (ex instanceof SQLException) return ex;
            ex = ex.getCause();
        }
        return null;
    }

    private String mapErrorMessage(String sqlState) {
        switch (sqlState) {
            case "P0001":
                return "Unable to delete this measurement point because there is an alarm in progress.";
            case "P0002":
                return "Referenced to a rule.";
            case "P0003":
                return "Referenced to a graph.";
            case "P0004":
                return "Alarm in progress for this rule.";
            default:
                return "Unknown SQL error.";
        }
    }
}

The issue:

• When I test the DELETE call in Postman, it works as expected — I get a 400 Bad Request with the correct message
• But when I call the same endpoint from my Angular frontend, I receive a 500 Internal Server Error, and the error message is: "Error while committing the transaction; nested exception is javax.persistence.RollbackException: Error while committing the transaction "

Logs:

[2025-04-24 15:09:02.693] - 22900 WARN [http-nio-127.0.0.1-8090-exec-2] --- org.hibernate.engine.jdbc.spi.SqlExceptionHelper: SQL Error: 0, SQLState: P0003
[2025-04-24 15:09:02.694] - 22900 ERROR [http-nio-127.0.0.1-8090-exec-2] --- org.hibernate.engine.jdbc.spi.SqlExceptionHelper: ERRORE: P0003
  Dove: funzione PL/pgSQL f_bd_reset_mepo_references() riga 22 a RAISE
[2025-04-24 15:09:02.698] - 22900 INFO [http-nio-127.0.0.1-8090-exec-2] --- org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl: HHH000010: On release of batch it still contained JDBC statements
[http-nio-127.0.0.1-8090-exec-2] WARN org.springframework.web.servlet.mvc.method.annotation.ExceptionHandlerExceptionResolver - Resolved [org.springframework.orm.jpa.JpaSystemException: Error while committing the transaction; nested exception is javax.persistence.RollbackException: Error while committing the transaction]

Any suggestions on how to ensure Angular receives the 400 and correct message just like Postman does?