Re: on update / on delete performance of foreign keys

Stephan Szabo wrote:
> 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).
I would be content if my optimization works for the not-deferred case -
I'd don't fully understand how deferred foreign keys are handled in
postgres. (I guess I don't even fully understand their semantics - I
use them only when doing bulk inserts, and there are either circular
dependencies, or I don't feel like find the right table order ;-))

> 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.
This I do not understand. Isn't it sufficient to delete any rows whose
reference does not exist (for the on-delete-cascade case), or complain
if such rows exist (for the no-action/restrict case)? The
on-update-cascade case is difficult I guess - I'm not sure if my idea
even works for that case, now that I think about it...

> 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.
Guess without understanding your previous comment I'm lost here too - I
wouldn't care to check only changed rows - I would check them all - but
only if some estimate shows that it will probably cheaper.

At the moment I'm writing a few plpgsql functions that do what I want.
They disable all constraint-related trigger, do a deleted, and then
recursivly traverse all tables (following the foreign-keys), and do
a "delete from .. where not exists (select 1 from ... where ...)".
I'll if I stumble upon problems - maybe I'll suddenly understand your
comments ;-)))

greetings, Florian Pflug