Inheritance, Primary Keys and Foreign Keys

Hi,
I'm developing an object persistency framework for which I'd love to have
better support for inheritance in PostgreSQL. I could already map subclasses
with the current inheritance facilities, but the problem is with Primary and
Foreign Keys.

There's a TODO for implementing Indexes that hold information contained in
different tables, but that seems to be difficult because of the need to
create a new index structure. The new structure shouldn't be used by tables
that don't have inherited tables because the new structure would hold a
pointer to the appropiate table per entry and thus redundant in these cases.
Even more, I've seen pointed by Tom Lane in a previous thread, that this
would cause lock problems where a lock in a table is needed, as locking a
table means locking its indexes.

In my particular case (don't know about the SQL standard or other cases),
it'd be enough if when an inherited table is created:
- A primary key in the inherited table is created with the same columns as
the super table.
- A trigger is created in the new table that ensures that this primary key
doesn't exist in the super table.
- A trigger is created in the super table that ensures that this primary key
doesn't exist in it's sub tables.

As I'm not an expert at all, I don't know if these would cause some side
effects or if it's a good enough solution for the general problem. I don't
know how multiple inheritance of tables with primary keys should be held
(maybe all super tables should have the same primary key).

For foreign keys, it seems as if simply selecting FROM a table instead of
the current FROM ONLY would have the expected (by me :) behaviour.

I'm very interested in improving inheritance support in PostgreSQL, and I'm
willing to learn the current design and implementation in order to do it
myself, or help wherever possible. So I'd like to know your ideas or problems
you may find with this solution (if it's a solution at all :)

Thanks in advance!

On Tue, 2006-05-09 at 01:20 +0200, Albert Cervera Areny wrote:

> In my particular case (don't know about the SQL standard or other cases),
> it'd be enough if when an inherited table is created:
> - A primary key in the inherited table is created with the same columns as
> the super table.
> - A trigger is created in the new table that ensures that this primary key
> doesn't exist in the super table.
> - A trigger is created in the super table that ensures that this primary key
> doesn't exist in it's sub tables.

Why not add these in your design rather than into the database?

All of the above can be added using existing DDL and you can group
things together in a transaction and call when required.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Of course, that's an option for my case. Just wanted to know if this solution
could be useful for PostgreSQL in general. Mainly because I'll add some
triggers to check what maybe PostgreSQL should do itself but it's
unimplemented.

If that's not interesting or a proper solution for PostgreSQL I'll add it
using the existing DDL in my application and that's all.

What do you think?

A Tuesday 09 May 2006 21:43, Simon Riggs va escriure:
> On Tue, 2006-05-09 at 01:20 +0200, Albert Cervera Areny wrote:
> > In my particular case (don't know about the SQL standard or other
> > cases), it'd be enough if when an inherited table is created:
> > - A primary key in the inherited table is created with the same columns
> > as the super table.
> > - A trigger is created in the new table that ensures that this primary
> > key doesn't exist in the super table.
> > - A trigger is created in the super table that ensures that this primary
> > key doesn't exist in it's sub tables.
>
> Why not add these in your design rather than into the database?
>
> All of the above can be added using existing DDL and you can group
> things together in a transaction and call when required.

Ühel kenal päeval, N, 2006-05-11 kell 23:28, kirjutas Albert Cervera
Areny:
> Of course, that's an option for my case. Just wanted to know if this solution
> could be useful for PostgreSQL in general. Mainly because I'll add some
> triggers to check what maybe PostgreSQL should do itself but it's
> unimplemented.
>
> If that's not interesting or a proper solution for PostgreSQL I'll add it
> using the existing DDL in my application and that's all.
>
> What do you think?
>
> A Tuesday 09 May 2006 21:43, Simon Riggs va escriure:
> > On Tue, 2006-05-09 at 01:20 +0200, Albert Cervera Areny wrote:
> > > In my particular case (don't know about the SQL standard or other
> > > cases), it'd be enough if when an inherited table is created:
> > > - A primary key in the inherited table is created with the same columns
> > > as the super table.

This is the simple part.

> > > - A trigger is created in the new table that ensures that this primary
> > > key doesn't exist in the super table.

This grows very expensive fast, once you have more thean one inherited
table

> > > - A trigger is created in the super table that ensures that this primary
> > > key doesn't exist in it's sub tables.

Ditto

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com

NOTICE: This communication contains privileged or other confidential
information. If you have received it in error, please advise the sender
by reply email and immediately delete the message and any attachments
without copying or disclosing the contents.