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

On Mon, 18 Jul 2005, Tom Lane wrote:

> 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).

Except that before I think the order would have looked like (for 1 row)
Originating Action
Trigger A on originating table that does update
Trigger B on originating table that does update
Trigger A1 caused by A
Trigger B1 caused by B

I think now it acts like:
Originating Action
Trigger A on originating table that does update
Trigger A1 caused by A
Trigger B on originating table that does update
Trigger B1 caused by B

Right?

> > 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?

I think that's probably enough for now. We probably will need to do
something, but since we still haven't managed to work out all of these
timing problems, I think it needs a bunch of going over the spec before
trying to actually do any changes. I have a feeling that it'll turn out
that normal triggers happen as we do now, but constraint checks caused
by referential actions are special and should be considered as part of
their parent statement. I'll try to see if rereading 99 or 03 (if
work has one) helps any.