Re: on update / on delete performance of foreign keys

On Tue, 25 Jan 2005, Florian G. Pflug wrote:

> Stephan Szabo wrote:
>
> > 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)

No, just that a delete cascade only deletes rows that are dependent upon
rows marked for deletion in the referenced table. If there were an fk row
with 1 in the above as well, that row would be removed.

> > 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?

I think you misunderstood, although I'm not sure where. One point is that
while the check portion of the constraint may be deferred the actions are
not.

In the case of restrict with a deferred constraint, the same logic applies
as per the delete cascade above. The action is defined upon the rows that
were marked for deletion and their dependent rows, so flagging the fk=2
row on the delete would be invalid because it didn't have a dependent row
to be marked for deletion. The reason why this would matter is that you
could satisfy the constraint after the delete but before the commit in
which case no error occurred. In the immediate case it's hard to come up
with a situation where it matters because the invalid row would have
already caused an error.