Re: Inheritence issue scheme advice?

On 2011-06-29 22:54, Casey Havenor wrote:
> Problem:
>   I like many other have come across the inherit issues.
>
> I found the thread here about such issue...
> http://postgresql.1045698.n5.nabble.com/FK-s-to-refer-to-rows-in-inheritance-child-td3287684.html
>
> I grabbed the "fk_inheritance.v1.patch" file and have been trying to install
> it for the last two hours. -- Got some help in hackers space so figured this
> out.  BUT was it was recommended not to utilize a hacked version of
> PostgreSQL.
>
> For inheritance I'm using it for the following.  ONLY on/with UNIQUE
> CONSTRAINTS and FOREIGN KEYS with OIDS enabled - which from my understanding
> that shouldn't be an issues as there shouldn't any duplicate entries that
> cause a deadlock?   -- So I would think this patch would be ok?
There are currently two caveats with the patch you mention

1: the user has to ensure global uniqueness of all pk's in an
inheritance hierarchy. This is not a problem at all if e.g. your root
relation has a pk with e.g. a default value from a sequence. The childs
will inherit that default value from the same sequence.

2: the patch enables inserting rows in a relation that has a fk to a
inheritance parent/root, with an fk key value that is found in one of
the child relations of the refered relation. The patch as is fails to
block deleting the referred record in the child relation: that check
currently only works for referred records in actual relation the fk
points to, not it's childs. It is not impossible to add this, but it
just hasn't been programmed yet. So a trigger check to prevent these
deletions also has to be made in user space.

I'd very much appreciate any feedback you have on the patch, if it
matches your usecase.

regards,

--
Yeb Havinga
http://www.mgrid.net/
Mastering Medical Data