Re: FK's to refer to rows in inheritance child

On Dec1, 2010, at 15:27 , Tom Lane wrote:
> Yeb Havinga <yebhavinga(at)gmail(dot)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 that the 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, which is 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 columns in the individual child table would be enough to guarantee global uniqueness. For efficiently, you'd probably need to teach the constraint exclusion mechanism that every table has an implicit constraint of the form "tableoid = <the table'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 yet to be seen, though - currently there doesn't seem to be unanimous vote one way or the other.

best regards,
Florian Pflug