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
columnsas
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 ofthe current FROM ONLY would have the expected
(byme :) 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!