Foreign key referential action timing
От | Stephan Szabo |
---|---|
Тема | Foreign key referential action timing |
Дата | |
Msg-id | 20041005075805.O25039@megazone.bigpanda.com обсуждение исходный текст |
Ответы |
Re: Foreign key referential action timing
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
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.
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: -HEAD build failure on OpenBSD 3.6-current/Sparc64 +patch