Re: unexpected effect of FOREIGN KEY ON CASCADE DELETE

On Thursday 24 June 2010 1:48:12 am Grzegorz Jaśkiewicz wrote:
> On Wed, Jun 23, 2010 at 7:31 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Thom Brown <thombrown(at)gmail(dot)com> writes:
> >> Yes, I'm still not exactly sure why it's seeing uncommitted changes. :/
> >
> > Because it's all one transaction.  A transaction that couldn't see its
> > own changes wouldn't be very useful.
> >
> > I think what the OP is unhappy about is that he imagines that the ON
> > CASCADE DELETE action is part of the original DELETE on the primary-key
> > table.  But it is not: per SQL spec, it is a separate operation
> > happening after the original DELETE.  (In fact, it might be quite a lot
> > after the original delete, if you have the FK constraint set as
> > deferred.)  The trigger on the referencing table fires before the actual
> > delete of the referencing row, but it's going to see the original DELETE
> > statement as already completed, because it was a previous operation
> > within the current transaction.
>
> That's all great Tom, but it breaks useful example like mine, and
> gives no other benefits.
>
> I will have to do something ugly, and create temp table to hold fooB
> deleted values, for reference from other threads.
> Temp, on commit drop. Not a very nice programming trick, but cleanest
> I can come up with.

I know I was confused before, but now I am not sure. Unless there is more to
this problem, why not put the trigger on fooB?

CREATE FUNCTION foobarrb() RETURNS trigger AS
$_$
BEGIN
RAISE NOTICE 'foobarred %', (OLD.name );
RETURN OLD;
END;
$_$ LANGUAGE 'plpgsql';

CREATE TRIGGER foobarrrred BEFORE DELETE ON foob FOR EACH ROW EXECUTE
PROCEDURE foobarrb();

test=> DELETE FROM foob where id=8;
NOTICE: foobarred 0.37025912059471
DELETE 1

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com