Re: Changes to not deferred FK in 8.0.3 to 7.4? - Mailing list pgsql-general

On Mon, 18 Jul 2005, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > On Mon, 18 Jul 2005, Tom Lane wrote:
> >> I don't see why.
>
> > 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
>
> Ah, of course.  So that could explain Janning's difference in results
> without having to assume any rearrangement from pg_dump (not but what
> we shouldn't take a second look at pg_dump's behavior anyway).
>
> >> 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.
>
> Agreed, this doesn't seem like an area for hasty solutions.
>
> The short-term answer for Janning is definitely to make his check
> constraints deferred, but we should look at whether our current ordering
> of non-deferred checks is really per spec or not.

It seems like we're off from reading SQL03 in various ways, but I don't
fully understand all of the rules and how they're set up.

It seems to me that the referential action text is now implying that
after triggers caused by referential actions become part of the
trigger context of the statement that triggered them. GR5 sets SSC to the
current trigger execution context (presumably for the statement doing the
action) and the ref action texts add state changes to SSC.

Taking the above with the fact that the "statement"  triggers for
referential action are always empty implies to me that a referential
action change isn't a statement; GR9-11 don't seem to affect this, but
give some weight, since otherwise separately triggered events would not
count for triggered data change events I think.

GR14 seems to codify the fact that referential actions that cause other
referential actions happen immediately.

I really don't understand what they've done with GR15 and 16.  15 would
seem to say that in the case of a cascade delete, the rows are marked for
deletion and then the before triggers are called, but that doesn't make
much sense to me.

-------------

So, it looks to me like the following (minus before deletion triggers
caused by ref actions) from combining the 14.* effect list and the above
for delete (update looks similar).  I'm also a little unsure of the last
two.  The notes on the rules that applies after trigger says, "All
constraints have already been checked..." which makes it sound like that
comes before the after triggrs to me.  I'm going to keep looking through
this, but figured I'd throw something out for people to rip apart.

On delete statement:
 Make a new trigger context
 Run before triggers
 Mark for deletion
begin 11.8 rules:
 If there are any referential actions,
  For each action,
   If it's a cascade,
    Mark referencing rows for deletion
    Add after row triggers for that to the context created above
   If it's a restrict,
    Error or not
   If it's a SET *,
    Run row level before triggers for the triggered update
    Identify for replacement
    Add after row triggers for triggered update
 If any state changes were done by the preceding, apply 11.8 rules
  again until there aren't any more changes
end 11.8 rules
 Delete the rows marked for deletion
 Evaluate constraints
 Run after triggers