Re: foreign keys and RI triggers

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> Are you sure? RI_FKey_Check seems to have a section on
> TRIGGER_FIRED_BY_UPDATE which seems to check if the keys are equal if the
> old row wasn't part of this transaction. I'm not sure why it's doing the
> transaction id check, but it looks like it will do an equals check at
> least some of the time.

I think the reason for the xact check is that if we have deferred
triggers and we do

begin;
insert new FK value;
update new row;
commit;

then when the trigger for the INSERT event fires, it will do nothing
because the tuple it's triggered on is now dead. So the trigger for
the UPDATE event had better make the check. It's possible we could
skip the UPDATE event if we could be certain the INSERT trigger had
already fired, but I'm not sure how to be certain about that.

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

Right. RI constraints are actually the only kind we do "right" in
terms of enforcing the check when the SQL spec says we should.

The thoughts I've had about special-casing RI events to save memory
have to do with the idea of lossy storage. As you accumulate more
per-row events, at some point it becomes more efficient to forget
the individual rows and just reapply the original full-table check
query when it's time to check the constraint. So if we could recognize
RI events as being associated with the same constraint, and keep track
of how many are pending for each constraint, we could make a decision to
discard the queue and instead register one event to apply a full-table
check. It's not clear how to do that efficiently though.

regards, tom lane