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