Re: on update / on delete performance of foreign keys

On Mon, 24 Jan 2005, Florian G. Pflug wrote:

> Since postgres already incoporates code to check foreign keys more
> efficiently (when doing alter table ... add constraint .. foreign key,
> postgres seems to use a merge or a hash join, instead of a nested loop),
> I wondered how hard it would be to use this for the triggers too.
>
> I imagined creating a statement-level trigger in parallel to the
> row-level triggers, and defining some threshold (let's say, more than
> 10% of the rows deleted). If the threshold is reached, the row-level
> trigger would just do nothing, and the statement-level trigger would
> delete the referencing records doing a join.
>
> Would this be feasable? And would it be something a newbie could tackle,
> or is it more involved than I think?

It's a little more involved. The first is that I think there's no good
way to tell the row trigger to do nothing (remember that the constraints
may be deferred so simple flags aren't sufficient). The second is that
these triggers will want to know which rows are deleted, but AFAIK
statement-level triggers don't currently give you that information and
deleting/changing any rows that aren't satisfied does not give the correct
behavior. The no action case is actually a little more involved again as
it needs to remove rows from the set of changed pk rows if new pk rows
have come into existance for matching keys.