Came across an interesting situation as part of our Oracle to PostgreSQL migration. In Oracle, it appears that immediate constraints are checked after the entire statement is run, including any AFTER ROW triggers. In Postgres, they are applied before the AFTER ROW triggers. In some of our AFTER ROW triggers, we had logic and deletes that will satisfy the constraint. In Postgres, these are causing problems.
Excerpt from ISO SQL 92, section 4.10.1:
If the constraint mode is immedi-
ate, then the constraint is effectively checked at the end of
each SQL-statement.
Since the trigger is defined as AFTER ROW, versus AFTER STATEMENT, I believe the trigger should be considered part of the statement, therefore the constraint should not be checked until after the row triggers have run. Any thoughts?
Here is a simplified example:
CREATE TABLE demo.parent ( id integer PRIMARY KEY );
CREATE TABLE demo.child ( id integer PRIMARY KEY, parent_id integer );
ALTER TABLE demo.child ADD CONSTRAINT parent_fk FOREIGN KEY (parent_id) REFERENCES demo.parent (id)
ON DELETE NO ACTION
DEFERRABLE
INITIALLY IMMEDIATE;
CREATE OR REPLACE FUNCTION demo.parent_delete_trg_fnc() RETURNS trigger AS $BODY$
BEGIN
DELETE FROM demo.child WHERE parent_id = OLD.id;
return OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER parent_ar_trg
AFTER DELETE
ON demo.parent
FOR EACH ROW EXECUTE PROCEDURE demo.parent_delete_trg_fnc();
INSERT INTO demo.parent VALUES (1);
INSERT INTO demo.child VALUES (1, 1);
delete from demo.parent WHERE id=1;
The last delete statement will throw a referential integrity error. In Oracle, same example, it does not as the trigger deletes the child.