Thread: foreign keys and RI triggers
I spent a little while looking into a performance issue with a large UPDATE on a table with foreign keys. A few questions: (1) When a PK table is updated, we skip firing the per-row UPDATE RI triggers if none of the referenced columns in the PK table have been modified. However, AFAICS we do not apply a similar optimization for updates of foreign key tables: if a user does not modify the foreign key column, we needn't check for the presence of the FK column value in the primary key table. Is there a reason we don't implement this? (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? (Hmm, I suppose we would need to defer firing the trigger until the command ID is incremented if the foreign key references its own table. But even so, this should not be an issue for non-self-referential foreign keys.) (3) This is minor, but AFAICS RI_FKey_check_upd() is not used -- RI_FKey_check_ins() is used to validate both inserts and updates on tables with foreign keys (see tablecmds.c circa 4423). Both functions are just wrappers over RI_FKey_check() anyway. This is rather confusing; would anyone object if I removed both functions and made RI_FKey_check() public? -Neil
On Thu, 26 May 2005, Neil Conway wrote: > I spent a little while looking into a performance issue with a large > UPDATE on a table with foreign keys. A few questions: > > (1) When a PK table is updated, we skip firing the per-row UPDATE RI > triggers if none of the referenced columns in the PK table have been > modified. However, AFAICS we do not apply a similar optimization for > updates of foreign key tables: if a user does not modify the foreign key > column, we needn't check for the presence of the FK column value in the > primary key table. Is there a reason we don't implement this? 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. > (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.
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.
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
Stephan Szabo <sszabo@megazone.bigpanda.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
On Fri, 27 May 2005, Neil Conway wrote: > 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. Okay, I can't think of cases even with triggers and the like where removing the check on equal valued rows would give appreciably different results, but I haven't thought too hard about it. > > 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. Unfortunately, I don't think so, if my assumption that user triggers are supposed to happen before constraint checks is true. In that case, we must wait until not only the action but all triggers fired by that action happen in order to run the constraint check because a trigger could make an otherwise invalid row valid.
On Thu, 26 May 2005, Stephan Szabo wrote: > On Fri, 27 May 2005, Neil Conway wrote: > > > 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. > > Okay, I can't think of cases even with triggers and the like where > removing the check on equal valued rows would give appreciably different > results, but I haven't thought too hard about it. Err, except the case that Tom mentions in his message.
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
>> Okay, I can't think of cases even with triggers and the like where
>> removing the check on equal valued rows would give appreciably different
>> results, but I haven't thought too hard about it.
> Err, except the case that Tom mentions in his message.
But the check could incorporate the same transaction ID test already
in use. I think Neil is right that it'd be a win to apply the test
before enqueueing the trigger instead of after.
regards, tom lane
On Thu, 26 May 2005, Tom Lane wrote: > Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > >> Okay, I can't think of cases even with triggers and the like where > >> removing the check on equal valued rows would give appreciably different > >> results, but I haven't thought too hard about it. > > > Err, except the case that Tom mentions in his message. > > But the check could incorporate the same transaction ID test already > in use. I think Neil is right that it'd be a win to apply the test > before enqueueing the trigger instead of after. Good point. That would help in many cases anyway.
On Thu, 26 May 2005, Tom Lane wrote: > 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 One problem with that is that it works for the constraint check but not for referential actions, although if we instead fired the referential actions truly immediately rather than queued to statement end that'd prevent those from being an issue. The only thing there is that we'd have to also have a constraint check for at least set default. > 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. Yeah, I was thinking we could keep a separate structure for (foreign key trigger oid, action) where we could keep track of a current count and whether or not we've consolidated already and scan the queue when we do the consolidation removing items for that oid. That's still not very good though.
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Thu, 26 May 2005, Tom Lane wrote:
>> The thoughts I've had about special-casing RI events to save memory
>> have to do with the idea of lossy storage.
> One problem with that is that it works for the constraint check but not
> for referential actions,
True. But even fixing it only for constraint checks would be a win.
Sooner or later, we will have to implement spill-to-disk for the trigger
event queue anyway ...
regards, tom lane
On Thu, 26 May 2005, Tom Lane wrote: > Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > > On Thu, 26 May 2005, Tom Lane wrote: > >> The thoughts I've had about special-casing RI events to save memory > >> have to do with the idea of lossy storage. > > > One problem with that is that it works for the constraint check but not > > for referential actions, > > True. But even fixing it only for constraint checks would be a win. Yeah, I'm just wondering if going the extra step and forcing really really immediate referential actions (even if that sometimes means adding a no action trigger on the event as well) would be worth doing as that could remove the queued pk actions for cascade and set null as well and at least turn the queued pk action for set default into one that could be consolidated.
Tom Lane wrote: > But the check could incorporate the same transaction ID test already > in use. I think Neil is right that it'd be a win to apply the test > before enqueueing the trigger instead of after. Speaking of which, does anyone see a reason why RI_FKey_keyequal_upd() is implemented as a pseudo-trigger function -- e.g. taking a pointer to a TriggerData? It seems a kludge. -Neil
Neil Conway <neilc@samurai.com> writes:
> Speaking of which, does anyone see a reason why RI_FKey_keyequal_upd()
> is implemented as a pseudo-trigger function -- e.g. taking a pointer to
> a TriggerData?
Because (a) it needs all the same arguments and (b) it can share
infrastructure with the other RI triggers.
regards, tom lane
Tom Lane wrote: > Because (a) it needs all the same arguments Well, it needs the Trigger that we're in the process of queueing, the old tuple, the new tuple, and the updated relation. It doesn't need the rest of the content of TriggerData. trigger.c has to manually construct a TriggerData to pass to it, so it's not like it's a notational convenience. > (b) it can share infrastructure with the other RI triggers. Such as? I don't see anything it allows us to share. -Neil
Neil Conway <neilc@samurai.com> writes:
>> (b) it can share infrastructure with the other RI triggers.
> Such as? I don't see anything it allows us to share.
Note the ri_BuildQueryKeyFull call, and the arguments thereto.
As to the notational convenience issue, I think it's good that
RI_FKey_keyequal_upd looks almost exactly like the other RI trigger
functions. Having it look different in order to save a couple lines
in trigger.c doesn't seem like a win to me.
regards, tom lane