Hi.
I'm developing an application using PostgreSQL and it happened table
inheritance is THE solution to some design problems I have.
Unfortunately the feature doesn't exactly work as true class/object
inheritance would. Main problems are well recognized and documented:
child table doesn't inherit parent constraints and parent's index
doesn't get updated with child's keys. While I didn't dig in the
Postgres internals, from the symptoms I guess the inheritance is
implemented as implicit UNION of the tables. To be more specific, I
have:
CREATE TABLE parent (
p int PRIMARY KEY
);
CREATE TABLE child (
c int
);
If I'm right, in the backend there are two tables: parent(pid) and
child(pid,cdata) and
INSERT INTO child ...
just go to child. Then when I
SELECT ... FROM parent
Postgres does
SELECT ... FROM parent UNION SELECT ... FROM child
for me (might be syntax error, I'm not so familiar with SQL).
This scenario of course explains these problems and I understand solving
them won't be easy. But I have another question: why can't be
inheritance implemented as implicit JOIN?
I mean, in the backend there would be tables parent(p) and child(c) plus
some glue added (if oids/tids are not enough). So
INSERT INTO child VALUES (1,2)
would
INSERT INTO parent VALUES (1)
INSERT INTO child (2)
And
SELECT ... FROM parent
would work as is, but
SELECT ... FROM child
would effect in
SELECT ... FROM parent JOIN child ON glue
It seems to me that it would solve both mentioned problems in one shot:
parent contains all keys it should have (and so index does) and parent's
constraints are enforced at the same time.
The glue can be issue or may be not. The real issue would be with
overriding parent's constraints (from my point of view it's minor one
compared to contemporary problems). There may be other deficiencies I'm
not aware of. On the bright side, I think this implementation (or at
least some functionality of) can be made with rules.
Anyone share thought about the whole idea? Or details?
Best regards.
--
Grzegorz Nowakowski