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 - IOn 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).
use them only when doing bulk inserts, and there are either circular
dependencies, or I don't feel like find the right table order ;-))
This I do not understand. Isn't it sufficient to delete any rows whoseThe 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.
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...
Guess without understanding your previous comment I'm lost here too - IThe 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.
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
Attachment:
smime.p7s
Description: S/MIME Cryptographic Signature
