Thread: AFTER triggers and constraints
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.
The last delete statement will throw a referential integrity error. In Oracle, same example, it does not as the trigger deletes the child.
Not sure your real case, but why not just make the FK on delete cascade and get rid of your trigger entirely?
From: Vick Khera [mailto:vivek@khera.org]
Sent: Friday, June 28, 2013 9:35 AM
To: David Greco
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] AFTER triggers and constraints
On Fri, Jun 28, 2013 at 8:45 AM, David Greco <David_Greco@harte-hanks.com> wrote:
The last delete statement will throw a referential integrity error. In Oracle, same example, it does not as the trigger deletes the child.
Not sure your real case, but why not just make the FK on delete cascade and get rid of your trigger entirely?
Alternatively, what if you make your constratint initially deferred?
The actual use case is a bit different and complicated. When the constraint is initially deferred, it works as expected, and that is how I will work around the issue. But my point is, is this how it is SUPPOSED to work? It’s not clear to me yet that is the case. I would expect the statement to include the after row triggers (but not the after statement triggers).
David Greco <David_Greco@harte-hanks.com> writes:
> Since the trigger is defined as AFTER ROW, versus AFTER STATEMENT, I believe the trigger should be considered part of
thestatement, therefore the constraint should not be checked until after the row triggers have run. Any thoughts?
Not sure that this is terribly well documented, but you can arrange for
your triggers to fire before the FK-enforcement triggers. Triggers on
the same table and event type fire in alphabetical (in ASCII) order, so
just choose a name that's before the FK triggers, which if memory serves
have names starting with "RI_". So for instance
CREATE TRIGGER "Parent_ar_trg" ...
would have worked the way you want.
regards, tom lane
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, June 28, 2013 10:10 AM
To: David Greco
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] AFTER triggers and constraints
David Greco <David_Greco@harte-hanks.com> writes:
> Since the trigger is defined as AFTER ROW, versus AFTER STATEMENT, I believe the trigger should be considered part of
thestatement, therefore the constraint should not be checked until after the row triggers have run. Any thoughts?
Not sure that this is terribly well documented, but you can arrange for your triggers to fire before the FK-enforcement
triggers. Triggers on the same table and event type fire in alphabetical (in ASCII) order, so just choose a name that's
beforethe FK triggers, which if memory serves have names starting with "RI_". So for instance
CREATE TRIGGER "Parent_ar_trg" ...
would have worked the way you want.
regards, tom lane
Thanks Tom,
Yes, renaming the trigger does in fact work. Any thoughts on the theory of this behavior? i.e. is this ANSI compliant?
Orshould there be a mechanism in place that guarantees the FK-enforcement trigger runs after all others?
Yes, renaming the trigger does in fact work. Any thoughts on the theory of this behavior? i.e. is this ANSI compliant? Or should there be a mechanism in place that guarantees the FK-enforcement trigger runs after all others?
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Simon Riggs <simon@2ndQuadrant.com> writes:
> We claim conformance to the standard on this.
Not really. The fact that we do RI actions via triggers is already not
what the spec envisions. As an example, it's well known that you can
subvert RI actions entirely by installing triggers on the target table
that make the RI actions into no-ops. It would be difficult to justify
that behavior by reference to the standard, but we leave it like that
because there are effects you really couldn't get if RI actions were
somehow lower-level than triggers. (Simple example: if you have a
business rule that updates on a table should update a last-modified
timestamp column, you might wish that updates caused by an ON UPDATE
CASCADE action did that too.)
> Should we have a parameter to define precedence of RI checks?
That seems like a recipe for breaking things. Apps already have the
ability to control whether their triggers fire before or after the RI
triggers; changing the rule for trigger firing order is going to break
anybody who's depending on that. I'm inclined to leave well enough
alone here --- especially given that, AFAIR, this is the first complaint
of this sort in the fifteen years or so that PG's RI actions have worked
this way.
regards, tom lane
Simon Riggs <simon@2ndQuadrant.com> writes:Not really. The fact that we do RI actions via triggers is already not
> We claim conformance to the standard on this.
what the spec envisions. As an example, it's well known that you can
subvert RI actions entirely by installing triggers on the target table
that make the RI actions into no-ops. It would be difficult to justify
that behavior by reference to the standard, but we leave it like that
because there are effects you really couldn't get if RI actions were
somehow lower-level than triggers. (Simple example: if you have a
business rule that updates on a table should update a last-modified
timestamp column, you might wish that updates caused by an ON UPDATE
CASCADE action did that too.)
That seems like a recipe for breaking things. Apps already have the
> Should we have a parameter to define precedence of RI checks?
ability to control whether their triggers fire before or after the RI
triggers; changing the rule for trigger firing order is going to break
anybody who's depending on that. I'm inclined to leave well enough
alone here --- especially given that, AFAIR, this is the first complaint
of this sort in the fifteen years or so that PG's RI actions have worked
this way.
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services