Deferred Constraint Trigger with Savepoint

Question

We make use of some deferred constraint triggers in our application and I was hoping to write some tests for them using pgTap. For database tests, we like to wrap things inside of a transaction so that everything is isolated and cleaned up at the end, but this poses a problem for deferred constraint triggers as they don't seem to fire unless the transaction is committed (by design).

Is there any way to get them to fire using savepoints? I attempted something simple like below but it still seems like the deferred trigger won't fire unless I commit the outer transaction.

BEGIN TRANSACTION;

CREATE TABLE test_table_with_trigger
(
    comment text
);

CREATE FUNCTION t_fn_test() RETURNS TRIGGER AS
$$
BEGIN
    RAISE INFO '%', tg_argv;
    RETURN NEW;
END;
$$
    LANGUAGE plpgsql VOLATILE;

CREATE CONSTRAINT TRIGGER t_test_constraint
    AFTER INSERT
    ON test_table_with_trigger DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
EXECUTE PROCEDURE t_fn_test('constraint trigger');

CREATE TRIGGER t_test
    AFTER INSERT
    ON test_table_with_trigger
    FOR EACH ROW
EXECUTE PROCEDURE t_fn_test('row trigger');

SAVEPOINT nested;

INSERT INTO test_table_with_trigger (comment) VALUES ('will it trigger?');

RELEASE SAVEPOINT nested;

ROLLBACK;

Answer 1

You can issue SET CONSTRAINTS ALL IMMEDIATE right before the end of the transaction, which will lead to the immediate execution of the deferred constraints.