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

From: Florian Pflug <fgp(at)phlo(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Yeb Havinga <yebhavinga(at)gmail(dot)com>, PostgreSQL-development Hackers <pgsql-hackers(at)postgresql(dot)org>, "w(dot)p(dot)dijkstra(at)mgrid(dot)net" <w(dot)p(dot)dijkstra(at)mgrid(dot)net>
Subject: Re: FK's to refer to rows in inheritance child
Date: 2010-12-01 15:58:48
Message-ID: C0B3EDB6-F115-47A4-9787-E9BB32FAF0AF@phlo.org
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Fetter 2010-12-01 15:59:29 Re: Proposal: First step towards Intelligent, integrateddatabase
Previous Message Robert Haas 2010-12-01 15:46:21 Re: crash-safe visibility map, take three