Efficient ON DELETE trigger when referential integrity is involved ?

From: Cédric Dufour (Cogito Ergo Soft) <cedric(dot)dufour(at)cogito-ergo-soft(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Efficient ON DELETE trigger when referential integrity is involved ?
Date: 2002-11-12 09:50:26
Message-ID: NDBBIFNBODNADCAOFDOACECECEAA.cedric.dufour@cogito-ergo-soft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2002-11-12 10:19:48 Re:
Previous Message Cédric Dufour 2002-11-12 09:42:51 Efficient ON DELETE trigger when referential integrity is involved ?