Postgresql trigger is not triggered in sprint test

Question

In postgresql, I have a table Student(id, name), Guardian(id, type,name) and a relationship table StudentXGuardian, a Student table has a column Guardian.

And I have a constraint to constraint a Student must have a Guardian which has type primary. This is my constraint, which will be triggered after a transaction:

CREATE OR REPLACE FUNCTION ensure_primary_guardian_on_delete()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if the guardian being deleted is a primary guardian
    IF OLD.guardian_id IN (SELECT id FROM Guardian WHERE type = 'primary') THEN
        -- Check if deleting this guardian would leave the student without a primary guardian
        IF NOT EXISTS (
            SELECT 1
            FROM StudentXGuardian
            WHERE student_id = OLD.student_id
              AND guardian_id != OLD.guardian_id
              AND guardian_id IN (SELECT id FROM Guardian WHERE type = 'primary')
        ) THEN
            RAISE EXCEPTION 'Cannot delete the only primary guardian for the student';
        END IF;
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER check_primary_guardian_on_delete
AFTER DELETE ON StudentXGuardian
INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION ensure_primary_guardian_on_delete();

This constraint works fine when I try it with pure sql. However it doesn't work in integration test in spring.

And I have a repository which has methods addStudentXGuardian(...), and deleteStudentXGuardian(...) With a test method:

@Transactional
@Test
void myTest() {
  var primaryGuardian=...;
  addStudentXGuardian(primaryGuardian);
  assertThrows(
        QueryExecutionException.class,deleteStudentXGuardian(primaryGuardian));
}

And this method passes.

I know you should not use @Transactional, because the exception will only be thrown after the transaction, but the transaction happens after the assertThrows(...).

I have tried to use TransactionTemplate in the test, and it throws exception saying "Cannot delete the only primary guardian for the student".

I also have noticed that Spring treats @Transactional differently like described in the document. Does that mean that my the transaction is committed only after the test method returning when using @Transactional? I have debugged it, it seems like so.

However when I the debugger went to commit the transaction eventually after the test method, it still didn't throw this exception. What is wrong here?

Thank you for reading this!