Thread: Primary Key on Inherited Table

Primary Key on Inherited Table

From
Wenjin Zheng
Date:
Hi,

I was trying to create a table that inherites from a parent table.  I have
to use the fields both from the parent table and the child table to define
the  primary key.  Unfortunately I was not able to do that.  It can not find
the fields from the parent table and give me the error "column .. does not
exist".  I can not specify the fields by using parenttable.column either.  I
have read the documentations and there is nothing in the inheritance chapter
talking about primary key.  Is there a way to specify primary key in the
child table using columns from parent table?  I am using 6.5.0.  Helps will
be greatly appreciated.

Wenjin Zheng
Bioinformatic Analyst
Large Scale Biology
3333 Vaca Valley Parkway
Vacaville, CA 95688
(707)469-2353
email: wenjin.zheng@lsbc.com 



Re: Primary Key on Inherited Table

From
Tom Lane
Date:
Wenjin Zheng <wenjin.zheng@lsbc.com> writes:
> Is there a way to specify primary key in the child table using columns
> from parent table?  I am using 6.5.0.

It sounds like the PRIMARY KEY spec is getting processed before the
parent table reference is expanded.  That's probably a bug, but it's
not going to get fixed in 6.5 ;-).  What I'd suggest is not relying
on the PRIMARY KEY syntax, but just writing out the equivalent CREATE
UNIQUE INDEX command separately after you create the child table.

(Strictly speaking, PRIMARY KEY also implies NOT NULL on each column,
which might be hard to duplicate if you don't want the same columns
to be NOT NULL in the parent, but as long as you can set them that
way in the parent you don't need PRIMARY KEY.)
        regards, tom lane


Re: Primary Key on Inherited Table

From
Hannu Krosing
Date:
Tom Lane wrote:
> 
> Wenjin Zheng <wenjin.zheng@lsbc.com> writes:
> > Is there a way to specify primary key in the child table using columns
> > from parent table?  I am using 6.5.0.
> 
> It sounds like the PRIMARY KEY spec is getting processed before the
> parent table reference is expanded.  That's probably a bug, but it's
> not going to get fixed in 6.5 ;-).  What I'd suggest is not relying
> on the PRIMARY KEY syntax, but just writing out the equivalent CREATE
> UNIQUE INDEX command separately after you create the child table.
> 
> (Strictly speaking, PRIMARY KEY also implies NOT NULL on each column,
> which might be hard to duplicate if you don't want the same columns
> to be NOT NULL in the parent, but as long as you can set them that
> way in the parent you don't need PRIMARY KEY.)

IMHO primary key itself should be one of inherited "attributes", and 
it should be unique over all parents and children.

This is hard to enforce under current "inheritance".

I think that use of inheritance as it is in current PostgreSQL should 
be discouraged.

There has been some discussion about making PostgreSQL a real ORDBMS
but as I understood the work was pushed back to wait for 7.0.

Doing small incremental "fixes" like the above would make it harder 
to get true OO features in the main code later, as people start to 
rely on "inheritance" for convenience features (like creating some 
tables with similar fields) some of which will have to be altered 
when fixing some more fundamental flaws in current PostgreSQL OO 
architecture (inheriting primary and foreign keys and other 
constraints, making alter table add column work, making it 
possible to dump/reload inherited tables that have added columns, 
etc.).

------------
Hannu


Re: Primary Key on Inherited Table

From
"Robert B. Easter"
Date:
On Wed, 10 May 2000, Hannu Krosing wrote:
> Tom Lane wrote:
> > 
> > Wenjin Zheng <wenjin.zheng@lsbc.com> writes:
> > > Is there a way to specify primary key in the child table using columns
> > > from parent table?  I am using 6.5.0.
> 
> IMHO primary key itself should be one of inherited "attributes", and 
> it should be unique over all parents and children.
> 
> I think that use of inheritance as it is in current PostgreSQL should 
> be discouraged.
> 
> There has been some discussion about making PostgreSQL a real ORDBMS
> but as I understood the work was pushed back to wait for 7.0.
> 

I also ran into this problem of not being able to use an inherited attrib
as a primary key.  I sent out some email (by accident when I wasn't really even
finished with it (strange)!) about the distinction between inheriting and
extending a class.  Inherit is a very general term.  I have broken it down into
CLONES, EXTENDS, and ASSIMILATES (please consider):

EXTENDS is the case where a child shares the same primary key as one of its
parents.  The parent and child use the same storage of
common attributes and the child table really only stores the unique information
with row references back to its parent(s) to get the other attribs.  EXTENDS
creates a connected tree where the parent can't be dropped because its dependent
children are connected to it.  The child IS the parent(s), just concatenated
together and with some (possibly) new attributes.  In a way, this might be like
a special VIEW that also has its own new attributes.

In INHERITS (CLONES), the child just gets a COPY of the parent(s) structure with
options to specify inherited attribs in a primary key.  The child's primary key
values, like all its other attribs, are not shared with the parent. The child
could use the same sequence as a parent from which to get unique id values.  The
child would be independent of the parent after being created so that the parent
could be dropped without any problem.  This is like a new tree or cloning a
tree.  The parent would store a reference to the children so that select * from
parent*; would work.  This seems to be about how the current implementation of
INHERITS works now except you can't drop the parent.  It will give you an error
if you try.  Instead of saying INHERITS, the word CLONES might be more clear.
The original is the master of its clones - it can see them.  But like real
cloning, the originals can die, leaving just the clones.

ASSIMILATES might be a combination of CLONES and EXTENDS, where
reference counts on attributes would be used to keep track of them.  When the
reference count on an attribute is 0, no parent or child uses it, and it can be
dropped.  A child could assimilate a parent, but the parent could still be
dropped.  The child would continue to keep a reference on the attributes of the
parent.  Only data rows used by just the parent alone would get deleted from the
attributes when the parent is dropped.  You might say something like CREATE
TABLE child () ASSIMILATES(parent); This method allows the ability to exclude
some of the parents attributes from being inherited by the child.  SELECT
attrib1, attrib2, ... from child*; would output all of the selected
attributes values, not just the instances it contributed to the columns.  Of
course, when doing the select above, it would have to output entire rows. The
system would have to find all classes that use the same rows together and make a
union.  The storage of the attributes might involve maintaining
sections for each class that uses it. Actually, all classes that share
attributes using this method form a collective, not really a parent/child
relationship.  For this to work, the attributes have to be more
object-oriented, independent, and external to the classes that actually use
them.  So, when creating a table, you are in the process of creating new
attribute objects that other new classes can assimilate.

Here are some examples of cases using all three of these:

Ex. 1 CLONES
I want to create a child based on parent.  All attributes are inherited.  Parent
should be able to see all the children and select common attributes recursively
through the children and grandchildren etc.  The parent should be able to die
and the children remain independent.
Solution: CREATE TABLE child (...) CLONES(parent);

Ex. 2 EXTENDS
I want to create a child based on parent.  I want to share the primary key and
attributes inherited from the parent with the parent.  All attributes are
inherited.  The child simply extends the parent with additional attributes that
make it more specialized.  The child will depend on the parent for storage of
attributes it inherits(uses).  When I insert into child, I want the parents
attributes inserted into the parent, and just the extended attributes stored in
the child.  The child can be dropped and the attributes it added to the parent
may persist or be deleted by option of child (the child may leave an
inheritance to the parent!).
Solution: CREATE TABLE child (...) EXTENDS(parent);

Ex. 3 ASSIMILATES
I want to create a child based on parent.  I might not want to inherit all
attributes of parent.  I don't care if the parent is later dropped - the child
shares the parents attributes with the other survivers.  I want to share
attributes storage with the parent (while it exists) and its other siblings,
that is, the parent see the data it has in common with the child.  The child
can select attributes in common with the parent and all its other siblings!  I
might want the child to share a primary key with the parent (which might also
be a primary key of other siblings) by choosing to inherit it.  I want the
child to join the parent (and its other siblings) as a collective. Solution: 
CREATE TABLE child (...) ASSIMILATES(parent);

All the methods above use inheritance of attributes from a base class, just in
different ways.  INHERIT may be too general a term to use in describing all the
possibilities.

Well, anyhow, I hope some of this will be considered when the inheritance
system in PostgreSQL is maybe redesigned.

-- 
Robert B. Easter
reaster@comptechnews.com


Re: Primary Key on Inherited Table

From
Tom Lane
Date:
"Robert B. Easter" <reaster@comptechnews.com> writes:
> Inherit is a very general term.  I have broken it down into
> CLONES, EXTENDS, and ASSIMILATES (please consider):
> [ much snipped ]

I think you've put your finger on a fundamental issue: "inheritance"
seems to mean different things to different people, depending on how
they are trying to use it.  Rather than arguing about who is right,
we have to recognize that they're all right in their own contexts.
The trick is to figure out how to support all those variant usages.

I'm not sure that you've proposed quite the right conceptual breakdown.
My inclination would be to try to specify several independent
implementation properties that can be mixed & matched to create the
behaviors that different applications want.  But clearly you are
thinking about the right problem.

I hope that Chris Bitmead will pop up and contribute to this thread;
awhile ago he was bugging us regularly about shortcomings in Postgres'
inheritance support, but I'm afraid he might've got discouraged and
gone away :-(.  (If you have not done so already, I suggest you search
the pghackers archives for threads mentioning inheritance.  Chris
pointed out a lot of problems and interesting application examples
a year or so ago.)

I do have to tell you that most of the core developers are not thinking
much about inheritance --- we are busy with SQL92 compatibility,
performance, reliability, and other low-level concerns.  I'd love to see
someone step up to the plate and start working on inheritance as such.

> Well, anyhow, I hope some of this will be considered when the inheritance
> system in PostgreSQL is maybe redesigned.

Do I hear a volunteer? ;-)
        regards, tom lane