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

The following bug has been logged online:

Bug reference: 4396
Logged by: Stephen Cuppett
Email address: stephen(dot)cuppett(at)sas(dot)com
PostgreSQL version: 8.3.3
Operating system: RHEL5.2 x86_64
Description: Trigger event fired "UPDATE" when "DELETE" happening via
foreign key
Details:

I have the following chain of tables:

release -> feature -> testcase -> execution -> execution_history

All tables have a parent_id column to the previous table with a foreign key
specified as ON DELETE CASCADE. One to Many the whole way down.

When I delete a release, the whole chain gets deleted.

I have this trigger defined:

CREATE OR REPLACE FUNCTION execution_history()
RETURNS trigger AS
$BODY$
BEGIN
IF (TG_OP = 'UPDATE') THEN
INSERT INTO execution_history (
execution_id, reporter, complete_units,
failed_units, blocked_units,
attempted_units, created, remote_rpt, remote_addr,
reported
) VALUES (
OLD.id, OLD.reporter, OLD.complete_units,
OLD.failed_units, OLD.blocked_units,
OLD.attempted_units, OLD.updated, OLD.remote_rpt, OLD.remote_addr,
OLD.reported
);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

CREATE TRIGGER execution_history
AFTER UPDATE
ON executions
FOR EACH ROW
EXECUTE PROCEDURE execution_history();

Yet, when I go to delete a release, I get the following error:

Query: DELETE FROM "releases" WHERE "id" IN ('3')
Warning: SQL Error: ERROR: insert or update on table "execution_history"
violates foreign key constraint "execution_history_execution_id_fkey"
DETAIL: Key (execution_id)=(5830) is not present in table "executions".
CONTEXT: SQL statement "INSERT INTO execution_history ( execution_id,
reporter, complete_units, failed_units, blocked_units, attempted_units,
created, remote_rpt, remote_addr, reported ) VALUES ( $1 , $2 , $3 , $4 , $5
, $6 , $7 , $8 , $9 , $10 )" PL/pgSQL function "execution_history" line 3 at
SQL statement

I previously didn't have the TG_OP comparison, so I added the one to ensure
it was an "UPDATE". This doesn't appear to be correct behavior?

On the release and feature tables I have both an UPDATE for every row and
INSERT for every row trigger and they are *not* also firing. So I can't
figure out why this one would?

"Stephen Cuppett" <stephen(dot)cuppett(at)sas(dot)com> writes:
> Description: Trigger event fired "UPDATE" when "DELETE" happening via
> foreign key

You're going to need to show a complete example if you want any help
with this. (My bet is that you've overlooked a trigger someplace...)

regards, tom lane

I can upload the whole schema someplace, or is attaching a few files here okay?

Noticed that if I delete from the feature level, I do not hit the problem.

---------------------------------------------------------------------------------------
Stephen Cuppett
SAS® Certified Advanced Programmer for SAS9®
Tel: +1 919 531 0659 ▪ Stephen(dot)Cuppett(at)sas(dot)com
www.sas.com

SAS® … THE POWER TO KNOW®

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, September 03, 2008 11:45 AM
To: Stephen Cuppett
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key

"Stephen Cuppett" <stephen(dot)cuppett(at)sas(dot)com> writes:
> Description: Trigger event fired "UPDATE" when "DELETE" happening via
> foreign key

You're going to need to show a complete example if you want any help
with this. (My bet is that you've overlooked a trigger someplace...)

regards, tom lane

Stephen Cuppett <Stephen(dot)Cuppett(at)sas(dot)com> writes:
> I can upload the whole schema someplace, or is attaching a few files here okay?

What would be easiest from this end is a SQL script to create the
tables, insert any test data needed, and then reproduce the problem,
starting from an empty database. A "pg_dump -s" script is usually
a good starting place for making that.

regards, tom lane

Nm, I think I see what is happening.

I started tracing the transaction and see that another high level object, when deleted, does an ON DELETE SET NULL, then the item updated is deleted. So the AFTER UPDATE is run for the row.. even though it's a deleted row later in the sequence...

So it looks like this

A -> B <--|
A -> D -> E

Where:

B references A ON DELETE CASCADE
D references A ON DELETE CASCADE
E references D ON DELETE CASCADE
E references B ON DELETE SET NULL

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.

Steve

---------------------------------------------------------------------------------------
Stephen Cuppett
SAS® Certified Advanced Programmer for SAS9®
Tel: +1 919 531 0659 ▪ Stephen(dot)Cuppett(at)sas(dot)com
www.sas.com

SAS® … THE POWER TO KNOW®

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, September 03, 2008 12:24 PM
To: Stephen Cuppett
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key

Stephen Cuppett <Stephen(dot)Cuppett(at)sas(dot)com> writes:
> I can upload the whole schema someplace, or is attaching a few files here okay?

What would be easiest from this end is a SQL script to create the
tables, insert any test data needed, and then reproduce the problem,
starting from an empty database. A "pg_dump -s" script is usually
a good starting place for making that.

regards, tom lane

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

Yes. In my case I want the history gone too. Keeping the history wouldn't hurt, but it's just a preference.

If I did keep the history I definitely don't want that last UPDATE where E is set null... but that's easily circumvented via the trigger.

Thanks for the pointer to look for the other path!

Thanks,

Steve

---------------------------------------------------------------------------------------
Stephen Cuppett
SAS® Certified Advanced Programmer for SAS9®
Tel: +1 919 531 0659 ▪ Stephen(dot)Cuppett(at)sas(dot)com
www.sas.com

SAS® … THE POWER TO KNOW®

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, September 03, 2008 1:09 PM
To: Stephen Cuppett
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key

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