Re: Foreign key quandries

On Sat, 1 Mar 2003, Tom Lane wrote:

> Seems like this sort of approach is going to introduce a huge amount of
> very fragile mechanism, and probably a wide variety of hard-to-reproduce
> bugs :-(.

Possibly, that's why I brought it up here because more minds to find
problems are better.

> ISTM the only thing we really need to do to address the complaints about
> FKs is to invent some kind of sharable row-level lock. Then
> transactions adding references to an FK table would take out shared
> instead of exclusive locks on PK rows; nothing else changes.

That gets rid of most of the problems. There are problems with read locks
locking more than they may need to (insert into fk followed by another
transaction doing a modification of the referenced row that doesn't affect
the key).

> Of course, it's easy to say "sharable row-level lock" and not so easy to
> come up with an implementation that has decent performance. But when it
> was done, I think we might have some faith that it works. I'm going to
> have great difficulty putting any faith at all in an FK implementation
> that relies on dirty reads.

As a thought exercise, what all is involved in making sharable row-level
locks... No matter what is done for foreign keys, they're still useful to
constraints written by users, and even using them now in foreign keys
doesn't prevent a future change if the other issues are worked out.