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.
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
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);
}
}
- Why does the custom error message from PostgreSQL not get propagated correctly to the front-end but works fine in Postman?
- 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?
5
Thanks to comments above from Andrei Lisa, I changed the approach and now it is working.
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;
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';
ELSIF haveRule > 0 THEN
RAISE EXCEPTION SQLSTATE 'P0002';
ELSIF haveEchart > 0 THEN
RAISE EXCEPTION SQLSTATE 'P0003';
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$;
Global Exception Handler
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;
import java.sql.SQLException;
@ControllerAdvice
public class GlobalExceptionHandler {
@ExceptionHandler(SQLException.class)
public ResponseEntity<?> handleSQLException(SQLException ex, WebRequest request) {
String sqlState = ex.getSQLState();
String customMessage = mapErrorMessage(sqlState);
return new ResponseEntity<>(customMessage, HttpStatus.BAD_REQUEST);
}
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 "Unable to delete this measurement point because it is referenced to a rule.";
case "P0003":
return "Unable to delete this measurement point because it is referenced to a graph.";
case "P0004":
return "Unable to delete this rule because there is an alarm in progress.";
default:
return "An unknown error occurred.";
}
}
}