From cedric.dufour@freesurf.ch Tue Nov 12 09:43:37 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id EEEFE475C98 for ; Tue, 12 Nov 2002 04:43:37 -0500 (EST) Received: from aristote1.infomaniak.ch (aristote1.infomaniak.ch [212.23.249.238]) by postgresql.org (Postfix) with ESMTP id 74822475CA6 for ; Tue, 12 Nov 2002 04:43:35 -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:41:13 +0100 From: =?iso-8859-1?Q?C=E9dric_Dufour?= To: Subject: Efficient ON DELETE trigger when referential integrity is involved ? Date: Tue, 12 Nov 2002 10:42:51 +0100 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000F_01C28A38.3FA1DF90" 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/180 X-Sequence-Number: 10322 This is a multi-part message in MIME format. ------=_NextPart_000_000F_01C28A38.3FA1DF90 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. ------=_NextPart_000_000F_01C28A38.3FA1DF90 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.
 
 
------=_NextPart_000_000F_01C28A38.3FA1DF90-- From cedric.dufour@cogito-ergo-soft.com Tue Nov 12 09:50:34 2002 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-- From sszabo@megazone23.bigpanda.com Tue Nov 12 16:30:27 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4DBC8475D70 for ; Tue, 12 Nov 2002 11:30:27 -0500 (EST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 182A5475AE5 for ; Tue, 12 Nov 2002 11:30:25 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 37C90D615; Tue, 12 Nov 2002 08:30:30 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 2D3F85C03; Tue, 12 Nov 2002 08:30:30 -0800 (PST) Date: Tue, 12 Nov 2002 08:30:30 -0800 (PST) From: Stephan Szabo To: =?iso-8859-1?Q?C=E9dric_Dufour_=28Cogito_Ergo_Soft=29?= Cc: Subject: Re: Efficient ON DELETE trigger when referential integrity is In-Reply-To: Message-ID: <20021112082305.V67144-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: QUOTED-PRINTABLE X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/187 X-Sequence-Number: 10329 On Tue, 12 Nov 2002, [iso-8859-1] C=E9dric Dufour (Cogito Ergo Soft) wrote: > 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 thro= ugh > 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 ? Hmm, by the time that the on delete trigger runs after the foreign key action, I believe that the row in MASTER is already gone. Running a simple test in 7.3 seems to confirm this. Unfortunately it's still going to look for the row to update it. I can't think of a good way to tell if you're in an action that was caused by a foreign key rather than some other user trigger or rule or straight delete apart from some vague notions of really complicated workarounds which only partially help.