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

On 2010-12-01 16:58, Florian Pflug wrote:
> On Dec1, 2010, at 15:27 , Tom Lane wrote:
>> 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.
Me too, but Tom wrote, in one of the archive threads linked to from the
todo wiki, that it was in the SQL spec, so..
> 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.
Yes that would work, but if the database user must make the tableoid
column, and include in the PK and FK's (i.e. not transparant for the
user), then it would still be a workaround.

> 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.
Thanks for the reference, I will try and see how to use it for this purpose.

regards,
Yeb Havinga