Is there any difference between a “not deferrable constraint trigger” and a normal “after event row level trigger”?

Question

I get that for a normal constraint [NOT] DEFERRABLE {INITIALLY IMMEDIATE/INITIALLY DEFERRED} change when the constraint is checked: after each operation/ after each statement/ after each transaction. Also for deferrable trigger we can use "SET CONSTRAINTS".

But it seems that a "not deferrable constraint trigger" fires at the end of the statement for each row exactly as a normal "after event row level trigger" would do. Also for such trigger we can not use SET CONSTRAINTS because we have declared it as NOT DEFERRABLE.

For instance in this code the trigger copies everything that is visible into a table called "copy_example"

CREATE TABLE example (
    ex INT);

CREATE TABLE copy_example (
    ex_copy INT);
    
CREATE FUNCTION save_copy()
RETURNS TRIGGER
LANGUAGE plpgsql
AS 
$$
BEGIN
    INSERT INTO copy_example (SELECT * FROM example);
    RETURN NEW;
END;
$$;

CREATE CONSTRAINT TRIGGER execute_copy
AFTER INSERT
ON example
NOT DEFERRABLE 
FOR EACH ROW
EXECUTE FUNCTION save_copy();

INSERT INTO example VALUES (1), (2);
SELECT * FROM copy_example;

The code outputs a table with values (1, 2, 1, 2) and not (1, 1, 2), as a normal trigger would do. So why bother to define a not deferrable constraint trigger instead of a normal trigger? Is there any difference in their behavior?

Laurenz Albe Laurenz Albe · Accepted Answer · 2020-10-16T09:52:01

No, it is pointless to define a non-deferrable constraint trigger.

The documentation describes the behavior exactly:

When the CONSTRAINT option is specified, this command creates a constraint trigger. This is the same as a regular trigger except that the timing of the trigger firing can be adjusted using SET CONSTRAINTS. Constraint triggers must be AFTER ROW triggers on plain tables (not foreign tables). They can be fired either at the end of the statement causing the triggering event, or at the end of the containing transaction; in the latter case they are said to be deferred. A pending deferred-trigger firing can also be forced to happen immediately by using SET CONSTRAINTS. Constraint triggers are expected to raise an exception when the constraints they implement are violated.

Is there any difference between a “not deferrable constraint trigger” and a normal “after event row level trigger”?

1 Answers