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

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

From: Jim Nasby <jim(at)nasby(dot)net>
To: Yeb Havinga <yebhavinga(at)gmail(dot)com>
Cc: 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-02 00:18:25
Message-ID: 74431A07-CEC1-48BF-A9C6-F71D12A3338B@nasby.net (view raw or flat)
Thread:
Lists: pgsql-hackers
On Dec 1, 2010, at 8:07 AM, Yeb Havinga wrote:
> FK's cannot refer to rows in inheritance childs.

We have partially solved this issue at work. In our scenario, we're not using inheritance for partitioning, we're using it for, well, inheriting. As part of that, we have a field in the parent table that tells you what "type" of object each row is, and constraints on the child tables that enforce that. We've created triggers that perform the same operations that the built-in RI triggers do, namely grabbing a share lock on the target row. The difference is that our trigger looks at the "type" field to determine exactly what table it needs to try and grab shared locks on (we need to do this because the backend doesn't allow you to SELECT ... FROM parent FOR SHARE).

Our solution is not complete though. Offhand, I know it doesn't support cascade, but I think there's more stuff it doesn't do. AFAIK all of those shortcomings could be handled with whats available at a user level though, so someone with enough motivation could produce an entire RI framework that worked with inheritance (though the framework would need a way to work around the uniqueness issue).
--
Jim C. Nasby, Database Architect                   jim(at)nasby(dot)net
512.569.9461 (cell)                         http://jim.nasby.net



In response to

Responses

pgsql-hackers by date

Next:From: Jim NasbyDate: 2010-12-02 00:22:13
Subject: Re: Proposal: First step towards Intelligent, integrateddatabase
Previous:From: Jim NasbyDate: 2010-12-01 23:59:11
Subject: Re: improving foreign key locks

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