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 :-(.
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.
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.
regards, tom lane