Re: foreign keys and RI triggers

On Thu, 26 May 2005, Stephan Szabo wrote:

> On Thu, 26 May 2005, Neil Conway wrote:
>
> > (2) For per-row RI triggers of all kinds, we save the trigger under
> > CurTransactionContext and invoke it at the end of the current query.
> > There is not even overflow to disk (the report that prompted me to look
> > into this was someone's database crashing because they kept running OOM
> > when doing an UPDATE of a large table with FKs on a pretty lowend
> > machine). While avoiding consuming a lot of memory for queued trigger
> > execution is worth doing anyway, ISTM we needn't queue RI triggers in
> > the first place. Is there a reason we can't just invoke after-row RI
> > triggers immediately?
>
> If I'm understanding the question, there's two things.  First is deferred
> constraints and the second is that constraints happen after the entire
> statement.
>
> In a case like:
> insert into pk values(2);
> insert into pk values(1);
> insert into fk values(2);
> update pk set key=key+1;
>
> In no action, that's not an error AFAIK because the constraint is
> satisfied at end of statement. If the order of updates happened such that
> the key=2 row were updated first we couldn't know whether or not the
> constraint would be satisfied by later updates to the same table.

Although, we could almost run referential actions that way.  The almost
comes from some behavior for set default cases to make sure the default
still exists which I think would still have to happen at end of statement
to be spec complient (I don't have easy access to my copy of SQL99 from
here).  I think we're still a little short of entirely complient on timing
in any case because unless I'm misremembering constraint checks happen
after user defined triggers and referential actions happen before which
would be difficult if not impossible to do right now with the way we fire
triggers.