Re: BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Stephen Cuppett <Stephen(dot)Cuppett(at)sas(dot)com>
Cc: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key
Date: 2008-09-03 17:08:53
Message-ID: 26811.1220461733@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Stephen Cuppett <Stephen(dot)Cuppett(at)sas(dot)com> writes:
> When A is deleted, B is deleted, E is set null. Then, D is deleted and E is deleted.

> FOR EVERY ROW AFTER UPDATE is run on E for rows that no longer exist.

> Not sure if that's a bug or not... not sure it would be undesirable under conditions for that not to run for those tuples that are already gone.

Well, not firing the trigger would be pretty bad in some cases too. For
instance if you're trying to log updates in a history table, you'd not
want to miss updates just because they were immediately obsoleted.

Looking again at your example, that's exactly what your trigger is
doing, isn't it? Why in the world has your history table got an FK
back to the live tables? That presumes that you'll never delete any
objects ... or that you're willing to lose all history about them
as soon as they're deleted, which seems a pretty odd choice.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Stephen Cuppett 2008-09-03 18:11:22 Re: BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key
Previous Message Tom Lane 2008-09-03 17:00:53 Re: BUG #4397: crash in tab-complete.c