Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group