Stephan Szabo wrote:
So, does that mean that on-delete-cascade effectivly doesn't cascade if deferred? Or only to rows created _before_ the delete? (Altough this isThe 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.
not what your example shows)
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 ofI 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.
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.
Attachment:
smime.p7s
Description: S/MIME Cryptographic Signature
