Re: on update / on delete performance of foreign keys

Stephan Szabo wrote:

>>>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...
>
> It's not sufficient to do the delete for non existant pk rows in the
> deferred case. I also think we'd need to decide on the behavior for the
> PostgreSQL case where a user trigger runs in between the delete and the
> action (for example, if I delete where pk=1 and then in between the delete
> and its action insert a row with pk=1 does the delete fire? The spec
> doesn't say much because I don't think you can run anything between the
> two.)
>
> insert into pk values (1);
> begin;
> insert into fk values (2);
> delete from pk;
> commit;
>
> AFAICT to follow the foreign key semantics if the foreign key check is
> deferred an error occurs on commit. Deleting the fk row on the delete
> from pk is not allowed.
So, does that mean that on-delete-cascade effectivly doesn't cascade if
deferred? Or only to rows created _before_ the delete? (Altough this is
not what your example shows)

> I think it may be valid for on delete no action even in the deferred
> case(*) , but I haven't done alot of thinking about it, but I think it's
> also invalid for deferred restrict since only the rows being deleted have
> the restrict applied to them, so an insert into pk values (2) between the
> delete and commit would allow the transaction to succeed AFAIK.
>
> (*) - I'm not sure how you'd necessarily give a complete error message if
> the error should really be that an insert was invalid but you noticed it
> on a delete check.
>
> I haven't thought about the update cases at all.
Hm... but, if postgres is required to show the exactly same behaviour,
no matter if constraints are deferred or not, what then is the point of
deferring constraints at all (apart from getting the error at a later
point)? Or did I completly missunderstand you?

Is the SQL-Standard online somewhere? I'd like to read what it has
to say on deferred triggers - I'm still confused about their semantics
(I couldn't even say what semantics they should have ;-) ).

greetings, Florian Pflug
PS: And thanks for your patience while explaining this stuff to me.