Thread: Efficient ON DELETE trigger when referential integrity is involved ?
Efficient ON DELETE trigger when referential integrity is involved ?
From
Cédric Dufour (Cogito Ergo Soft)
Date:
Hello,
I am trying to figure out how to handle tuple deletion efficiently when ON DELETE triggers and referential integrity are involved. The scenario is about this one:
I have a MASTER and a SLAVE table, the latter referencing the former through a "FOREIGN KEY ... REFERENCES ... ON DELETE CASCADE" constraint. Besides, I have a ON DELETE trigger on the SLAVE table which updates a field in the MASTER table upon deletion.
Now, there no need to update the MASTER table if the SLAVE table deletion was actually fired by the FOREIGN KEY constraint. The ON DELETE trigger updates a tuple in the MASTER table which will be deleted itself right after. This can make the deletion of a MASTER table tuple very slow, if there are a lot of related tuples in the SLAVE table.
Is there any "by the book" way to handle this ?
Does the triggering mechanism (either for referential integirty or user triggers) in PostgreSQL allow to circumvent this problem ?
Somehow, the problem could be solved if there was a way to find out (in the ON DELETE trigger) that the deletion was fired because of referential integrity, and thus prevent the updating of the referenced tuple. Is there any way to find out ?
Does the usage of BEFORE/AFTER triggers affect this ?
Are the referential integrity triggers BEFORE or UPDATE triggers ? Can change "visibility" be of any help ?
Thanx for your answers.
Cedric D.
PS: Sorry if the message appears twice. I used the wrong e-mail address for the first one. Mea Culpa
On Tue, 12 Nov 2002, [iso-8859-1] C�dric Dufour (Cogito Ergo Soft) wrote: > Hello, > > I am trying to figure out how to handle tuple deletion efficiently when ON > DELETE triggers and referential integrity are involved. The scenario is > about this one: > > I have a MASTER and a SLAVE table, the latter referencing the former through > a "FOREIGN KEY ... REFERENCES ... ON DELETE CASCADE" constraint. Besides, I > have a ON DELETE trigger on the SLAVE table which updates a field in the > MASTER table upon deletion. > > Now, there no need to update the MASTER table if the SLAVE table deletion > was actually fired by the FOREIGN KEY constraint. The ON DELETE trigger > updates a tuple in the MASTER table which will be deleted itself right > after. This can make the deletion of a MASTER table tuple very slow, if > there are a lot of related tuples in the SLAVE table. > > Is there any "by the book" way to handle this ? Hmm, by the time that the on delete trigger runs after the foreign key action, I believe that the row in MASTER is already gone. Running a simple test in 7.3 seems to confirm this. Unfortunately it's still going to look for the row to update it. I can't think of a good way to tell if you're in an action that was caused by a foreign key rather than some other user trigger or rule or straight delete apart from some vague notions of really complicated workarounds which only partially help.