As a side effect of fixing timing issues with the new trigger
timing, Tom and I noticed that some foreign key actions were broken
(especially with deferred constraints) and as part of looking at that we
believe that we had made the wrong decision as to when the actions were
meant to fire in the first place.

	We had used a note in the foreign key section
            NOTE 182 - Subclause 10.9, "<constraint name definition> and
            <constraint characteristics>", specifies when a constraint is
            effectively checked.
and the referenced section to run both the constraint check and
referential actions at the time specified by the constraint
characteristics. However, both the note and section talk about checking
and not other rules of the constraint.

	While going through the spec, Tom noticed that SQL99 14.20 "Effect
of replacing rows in base tables" mentions as part of the actual
replacement of rows for updates (GR7), that "The General Rules of
Subclause 11.8, "<referential constraint definition>", are now
applicable." The likely general rules being mentioned are the referential
action rules, which make statements like: "If a non-null value of a
referenced column in the referenced table is updated to a value that is
distinct from the current value of that column, then for every member F of
the subtable family of the referencing table:..." The rules seem to define
the actions as if they happen as part of the statement's main execution
rather than as part of integrity constraint checking, due to things like
interactions between 11.8 GR15 and 11.8 GR7: "15) All rows marked for
deletion are effectively deleted at the end of the SQL-statement prior to
the checking of any integrity constraints", "7) If a row of the referenced
table that has not previously been marked for deletion is marked for
deletion, then [description of on delete action behavior]".

	While not exactly what the spec invisions, I believe we can come
closer to the correct behavior by treating all of the referential actions
as non-deferrable while allowing deferment of NO ACTION and the check
itself. The behavior is not quite right because we can both put an after
trigger before the referential action trigger (which might be seen as a
feature) and because I think an after trigger on one row of a multi-row
update may see the old referencing rows for later rows of the update that
haven't had their triggers run yet. In addition, to handle an odd case for
set default, we are currently immediately checking for referencing rows
after doing the action inside the function. For deferred constraints, this
check would now happen immediately where it should presumably be deferred.
Tue, 5 Oct 2004 08:26:48 -0700 (PDT)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Foreign key referential action timing
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> 	While not exactly what the spec invisions, I believe we can come
> closer to the correct behavior by treating all of the referential actions
> as non-deferrable while allowing deferment of NO ACTION and the check
> itself.

Since no one seems to have objected, I've applied this patch.

			regards, tom lane

Thu, 21 Oct 2004 17:36:36 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Foreign key referential action timing 