I’m experiencing an issue with my Spring Boot web application when trying to handle exceptions thrown by a PostgreSQL BEFORE DELETE trigger during a DELETE request. Here is the situation:
When a DELETE request is made, a PostgreSQL trigger checks whether the item can be deleted. If it cannot be deleted, the trigger raises an exception. This exception should be caught by a GlobalExceptionHandler in Spring to display an appropriate error message on the front-end.

Problem: When I test the DELETE request using POSTMAN, it works correctly and I receive the custom error message from PostgreSQL. However, when the same request is made from the front-end, I get the original error message “Error while committing the transaction; nested exception is javax.persistence.RollbackException: Error while committing the transaction” instead of the detailed error message.

Trigger:

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$;

Global Exception Handler

package com.coswiniot.controller;

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. Why does the custom error message from PostgreSQL not get propagated correctly to the front-end but works fine in POSTMAN?
2. Is there a better way to handle PostgreSQL trigger exceptions in Spring Boot to ensure that the error messages are correctly shown on the front-end?