Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 24576475C98 for ; Tue, 12 Nov 2002 04:50:34 -0500 (EST) Received: from aristote1.infomaniak.ch (aristote1.infomaniak.ch [212.23.249.238]) by postgresql.org (Postfix) with ESMTP id 4211547590C for ; Tue, 12 Nov 2002 04:50:31 -0500 (EST) Received: from cedserver (unverified [213.221.138.60]) by aristote1.infomaniak.ch (Rockliffe SMTPRA 4.5.6) with ESMTP id for ; Tue, 12 Nov 2002 10:48:49 +0100 From: =?iso-8859-1?Q?C=E9dric_Dufour_=28Cogito_Ergo_Soft=29?= To: Subject: Efficient ON DELETE trigger when referential integrity is involved ? Date: Tue, 12 Nov 2002 10:50:26 +0100 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0017_01C28A39.4F40F020" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Importance: Normal X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/174 X-Sequence-Number: 10316 This is a multi-part message in MIME format. ------=_NextPart_000_0017_01C28A39.4F40F020 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0017_01C28A39.4F40F020 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello,
 
I am trying to figure out how to handle tuple de= letion=20 efficiently when ON DELETE triggers and referential integrity are involved.= The=20 scenario is about this one:
 
I have a MASTER and a SLAVE table, the latter=20 referencing the former through a "FOREIGN KEY ... REFERENCES ... ON DELETE= =20 CASCADE" constraint. Besides, I have a ON DELETE trigger on the SLAVE table= =20 which updates a field in the MASTER table upon deletion.
 
Now, there no need to update the MASTER table if= the=20 SLAVE table deletion was actually fired by the FOREIGN KEY constraint. The = ON=20 DELETE trigger updates a tuple in the MASTER table which will be deleted it= self=20 right after. This can make the deletion of a MASTER table tuple very slow, = if=20 there are a lot of related tuples in the SLAVE table.
 
Is there any "by the book" way to handle this=20 ?
 
Does the triggering mechanism (either for refere= ntial=20 integirty or user triggers) in PostgreSQL allow to circumvent this problem= =20 ?
 
Somehow, the problem could be solved if the= re was=20 a way to find out (in the ON DELETE trigger) that the deletion was fired be= cause=20 of referential integrity, and thus prevent the updating of the referenced t= uple.=20 Is there any way to find out ?
 
Does the usage of BEFORE/AFTER triggers affect t= his=20 ?
 
Are the referential integrity triggers BEFORE or= UPDATE=20 triggers ? Can change "visibility" be of any help ?
 
Thanx for your answers.
 
    Cedric D.
 
PS: Sorry if the message appears twice. I used t= he=20 wrong e-mail address for the first one. Mea=20 Culpa
------=_NextPart_000_0017_01C28A39.4F40F020--