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.