Re: unexpected effect of FOREIGN KEY ON CASCADE DELETE

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.

regards, tom lane