|
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. |
