Efficient ON DELETE trigger when referential integrity is involved ?

From: Cédric Dufour <cedric(dot)dufour(at)freesurf(dot)ch>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Efficient ON DELETE trigger when referential integrity is involved ?
Date: 2002-11-12 09:42:51
Message-ID: NDBBIFNBODNADCAOFDOAKECDCEAA.cedric.dufour@freesurf.ch
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.

Browse pgsql-sql by date

  From Date Subject
Next Message Cédric Dufour (Cogito Ergo Soft) 2002-11-12 09:50:26 Efficient ON DELETE trigger when referential integrity is involved ?
Previous Message dnaren 2002-11-12 07:22:17