Re: Changes to not deferred FK in 8.0.3 to 7.4?

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> On Mon, 18 Jul 2005, Tom Lane wrote:
>> AFAICS, if it worked for you in 7.4 it was only by pure chance. There
>> was not then, and is not now, any logic that would prevent the FK checks
>> from being applied in an order you don't want.

> True, although I think in 7.4 it was more likely to work since the check
> triggers would be put on the trigger queue after the first level of
> referential action triggers rather than be run immediately between, right?

I don't see why. They are all "AFTER UPDATE" triggers so the trigger
mechanism isn't going to make any distinction between them; it'll just
fire them in trigger name order (which for FK triggers will reduce to
time-of-creation order, ignoring OID wraparound issues).

> I'm not sure when the triggered update's constraint checks are supposed to
> fire (is it as part of the referential action's updating action or the
> original query's constraint checks at end of statement?)

Arguably the constraint checks should always fire later. I think it
would actually work correctly in 8.0 if Janning made the constraints all
deferred (using the proper syntax, ie, SET CONSTRAINTS ALL DEFERRED)
because the referential action triggers are not deferred in any case,
so they fire at end-of-query, but deferred constraint triggers fire
at end-of-transaction.

Do you think that's enough, or do we need to add more mechanism to
ensure that even non-deferred constraint checks fire after all
referential actions are complete?

regards, tom lane