Exception in PostgreSQL function not catched in Java Hibernate

Question

I am using the following PostgreSQL function to delete a record from a table in the DB.

CREATE OR REPLACE FUNCTION deletesegment(segid bigint) RETURNS integer AS
    $BODY$
    DECLARE
        rowsAffected integer := 0;
    BEGIN
        DELETE FROM segment
        WHERE
            id = segid;
        GET DIAGNOSTICS rowsAffected = ROW_COUNT;
        RETURN rowsAffected;
    EXCEPTION
        WHEN foreign_key_violation THEN
            RAISE EXCEPTION foreign_key_violation;
    END;
$BODY$
    LANGUAGE plpgsql;

The primary key column of the above table (i.e. id column of segment table) is referenced as a foreign key by another table and when I execute the above function to delete a record from the segment table which is referenced by the other table, the function raises the error as expected (shown below).

ERROR:  foreign_key_violation

********** Error **********

ERROR: foreign_key_violation
SQL state: 23503

Now I am calling this PostgreSQL function from my Java code which is using Hibernate as below,

public int deleteSegment(Long segmentId) {

        SQLQuery query = (SQLQuery)sessionFactory.getCurrentSession().createSQLQuery("SELECT deletesegment(:segmentId)")
            .setParameter("segmentId", segmentId);

        int rows = (Integer)(query.list().get(0));

        return rows;
    }

When I pass in the same id to the above function (Integer)(query.list().get(0)) executes without raising any exceptions and returns 0. I expect it to throw an exception given that the underlying PostgreSQL function is raising an exception. But it doesn't.

What am I doing wrong. How can I make the exception to be raised in the Java code?

Thanks in advance..!!

The two links are how to do it in PL/SQL in Oracle.. As I've indicated I am using plpgsql in PostgreSQL.. — Bathiya Priyadarshana, Aug 09 '13 at 16:33

score 0 · Answer 1 · edited May 23 '17 at 12:13

Your problem is almost certainly that Hibernate is caching the query for you. You will have to investigate how to disable the query cache for this.

Hibernate has a number of different cache types, and one is a second level cache which caches database query results and hands them back up. This is probably what you have to disable.

For some starting ideas, check out this other question:

How to disable hibernate caching

Exception in PostgreSQL function not catched in Java Hibernate

1 Answers1