On Dec1, 2010, at 15:27 , Tom Lane wrote:
> Yeb Havinga <yebhavinga@gmail.com> writes:
>> FK's cannot refer to rows in inheritance childs. With some changes in
>> LockRows, together with removing the ONLY keyword in ri_trigger.c, it
>> was possible to refer to the rows in child relations. (WIP patch attached)
>
>> Though it passes simple tests, it is far from complete.
>
> Indeed. This isn't even worth the time to review, unless you have a
> proposal for fixing the unique-index-across-multiple-tables problem.
I've wondered in the past if a unique index is really necessary on the columns referenced by a FK in all cases, though.
It certainly is the only sane thing to do for For FKs which are marked ON UPDATE/DELETE CASCADE, SET NULL or SET
DEFAULT.But couldn't we weaken that requirement for FKs marked ON UPDATE/DELETE NO ACTION or RESTRICT? It seems to me
thatthe current RI code already handles that case quite nicely once "ONLY" is removed from the queries
The only flaw i can see is that INSERTs or UPDATEs on the child table would lock *all* matching parent table rows,
whichis more than is necessary in theory. But given that solving this would probably require full-fledged predicate
locking,I believe this flaw can be lived with. It might be missing something, though...
Another idea might be to allow this if "tableoid" is part of the FK. In that case, unique indices on the remaining
columnsin the individual child table would be enough to guarantee global uniqueness. For efficiently, you'd probably
needto teach the constraint exclusion mechanism that every table has an implicit constraint of the form "tableoid =
<thetable's oid>". I haven't thought this through, though - so there might be other obstacles as well...
BTW, my "serializable_lock_consisteny" patch would allow you to do this purely within pl/pgsql in a race-condition free
way.So if that patch should get applied you might want to consider this as a workaround. Whether it will get applied is
yetto be seen, though - currently there doesn't seem to be unanimous vote one way or the other.
best regards,
Florian Pflug