Re: foreign keys and RI triggers

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