Stephan Szabo wrote:
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.
Well, regardless of how RI_FKey_Check() itself works, ISTM there is no need to enqueue the RI trigger in the first place. That's when the update-on-PK-table optimization is applied -- see trigger.c circa 3005. The specific case I was looking at resulted in the postgres backend allocating a few hundred MB just to store all the pending RI triggers, even though the UPDATE in question didn't change the foreign key field, so it didn't matter a great deal how quickly RI_FKey_Check() was able to bail out.
If I'm understanding the question, there's two things. First is deferred constraints
Right -- obviously we can't fire RI triggers for deferred constraints immediately. Immediate constraints are the common case, though.
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;
Hmm, good point. But ISTM there are still some circumstances in which we can safely check the RI trigger immediately, rather than at end of statement. For example, updating the FK table, inserting into the FK table, or deleting from the PK table.
-Neil ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
