Re: unexpected effect of FOREIGN KEY ON CASCADE DELETE

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.

--
GJ