pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedEfficient ON DELETE trigger when referential integrity is involved ?
3+ messages / 3 participants
[nested] [flat]
* Efficient ON DELETE trigger when referential integrity is involved ?
@ 2002-11-12 09:42 Cédric Dufour <cedric.dufour@freesurf.ch>
0 siblings, 0 replies; 3+ messages in thread
From: Cédric Dufour @ 2002-11-12 09:42 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Efficient ON DELETE trigger when referential integrity is involved ?
@ 2002-11-12 09:50 Cédric Dufour (Cogito Ergo Soft) <cedric.dufour@cogito-ergo-soft.com>
0 siblings, 1 reply; 3+ messages in thread
From: Cédric Dufour (Cogito Ergo Soft) @ 2002-11-12 09:50 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Efficient ON DELETE trigger when referential integrity is
@ 2002-11-12 16:30 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Cédric Dufour (Cogito Ergo Soft) <cedric.dufour@cogito-ergo-soft.com>
0 siblings, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2002-11-12 16:30 UTC (permalink / raw)
To: Cédric Dufour (Cogito Ergo Soft) <cedric.dufour@cogito-ergo-soft.com>; +Cc: pgsql-sql
On Tue, 12 Nov 2002, [iso-8859-1] Cédric 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 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 ?
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-11-12 16:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-12 09:42 Efficient ON DELETE trigger when referential integrity is involved ? Cédric Dufour <cedric.dufour@freesurf.ch>
2002-11-12 09:50 Efficient ON DELETE trigger when referential integrity is involved ? Cédric Dufour (Cogito Ergo Soft) <cedric.dufour@cogito-ergo-soft.com>
2002-11-12 16:30 ` Re: Efficient ON DELETE trigger when referential integrity is Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox