Re: Foreign key reference counting strategy?

Michael Fuhr <mike@fuhr.org> writes:
> My intent wasn't to assert that IF EXISTS adds a new way for the
> DELETE to fail.  I was just pointing out that the test "if no
> referencing rows exist then delete the referenced row" isn't
> foolproof, viz., the DELETE can fail even though IF EXISTS said
> there were no referencing rows.

Actually, it's me that was confused --- I was thinking in terms of
the original DELETE being done on the PK row, but of course that's
not the case here --- the original DELETE would be on some FK row.

It might be possible to make it work by adding a SELECT FOR UPDATE
that locks the PK row in the trigger before doing the IF EXISTS test,
but I think that that could create deadlock failures.  You'd basically
be making the transaction upgrade a share row lock to an exclusive
lock, and that's generally unsafe.  If you could ensure that your
trigger runs before the RI integrity trigger, maybe it would work...
        regards, tom lane