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

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