Re: [HACKERS] Inheritance, referential integrity and other constraints

Peter Eisentraut wrote: >On 2000-01-24, Oliver Elphick mentioned: > >> I would like to work on improving implementation
ofinheritance, >> especially with regard to referential integrity.   I suspect there are >> a number of issues that may
berelated and will need to be done together. > >What I really consider a problem, and it would be great if you could
>tacklethat, is that there is no real standard that all of this does or >even could follow.
 

That is the point of this thread: to settle the design.
 >                    For example, I wrote the other day that depending on >which way you see it, the behaviour of
altertable x* add colum might be >considered right. Also I just looked into item 'Disallow inherited columns >with the
samename as new columns' and it seems that someone actually made >provisions for this to be allowed, meaning that
>createtable test1 (x int); >create table test2 (x int) inherits (test1); >would result in test2 looking exactly like
test1.No one knows what the >motivation was. (I removed it anyway.)
 

That's a relief!  Unless you have actually removed the ability to do
repeated inheritance?
 >> It will also be necessary to ensure that >> added constraints get inherited, when ALTER TABLE ... ADD/DROP >>
CONSTRAINTgets implemented. > >I assume the semantics of ADD CONSTRAINT will be exactly the same as of >all the other
altertable commands, in that if you specify a star then it >gets inherited, if not then not. 
 

This is the point of policy that needs deciding.  The fact that we can
say `SELECT ... FROM table*' implies to me that inheritance is a
permanent relationship between tables.  That is why we cannot DROP an
inherited column.  The question is, how close is the relationship?
We have to decide what model of inheritance we are using, because a
lot of design will flow automatically from that decision.

We can choose to follow a language model, but we must then decide which
language - Smalltalk, Eiffel, C++?  The fact that multiple inheritance
is possible seems to exclude Smalltalk; C++ is a conceptual mess (OK,
you can guess I'm an Eiffel fan!).  As a matter of fact, I don't think
that language models are very useful to PostgreSQL - an RDBMS with
inheritance is a unique animal!  I think we must devise a consistent and
useful scheme and not trouble overmuch about fitting it into a theoretical
framework, not least because the amount of work involved in a pure 
implementation is likely to be horrendous.  At present, PostgreSQL
supports multiple, repeated inheritance in reading tables, and partially
supports it in creating and altering them.  This scheme needs tidying
and completing.

The question to answer, then, is what inheritance is useful for; those are
the uses to be catered for.  I see its main use as being in the division
of similar data by kind.  I have used it like this:
                                      /----  customer               /------- organisation <              /
        \----  supplier      person <              \                       /----  staff               \-------
individual  <                                      \----  contact
 

the idea being that each sub-level gives a more specialised view.

I want to be able to say `REFERENCES person*' to refer to the whole
group, or `REFERENCES organisation*' for a sub-group, or
`REFERENCES customer' for a single table.  Each is a valid use according
to how much specialisation is required in the individual case.
(The data is only at the lowest level descendant tables of this group
In Eiffel terms, person, organisation and individual would be deferred
classes, that cannot be used directly but must have at least one
descendant.)

With this kind of scheme, some constraints can, perhaps, be allowed to
differ, but I feel that PRIMARY KEY and REFERENCES, at the very least,
should be inherited.  UNIQUE should probably be inherited, and CHECK
constraints, DEFAULT and NOT NULL, can quite likely be allowed to differ.
What do you all think about this?

If we do allow differences, I think that they should not depend on the
user's remembering to add * to the table name.  I think that an
alteration to a parent table alone should require a UNINHERITED keyword
to make the intention explicit. (After all, the user may not realise
that the table is a parent; I think the RDBMS should protect him against
obvious traps.)
 >                                 But the problem with ADD CONSTRAINT is of >course that the entire table needs to be
verifiedagainst the constraint >before allowing it to be added. This is fine if you do ADD CONSTRAINT >UNIQUE (a, b),
becausethe index will take care of it, but it's trickier >if you add a trigger based constraint. The former might get
into7.0 if I >hurry, the latter most likely not. > >What needs discussion is whether indexes should be shared between
>inheritedtables, or whether each new descendant table needs a new >one. Not sure if this just made sense, though. 
 
Perhaps we need a concept of grouped indexes to go with the grouped
tables that inheritance creates.  Clearly this is one of the issues
that the original designers didn't think through.  If we consider the
uses of an index, we can see that it is used first for fast access to
tuples and second to enforce uniqueness.  If (as I am suggesting)
the constraints that require an index (PRIMARY KEY, REFERENCES and UNIQUE)
are forced to be group-wide, it will follow that the corresponding
indexes should also be group-wide.  On the other hand, a user-created
index for fast access could apply to a single table in the group.
 >> I think that the implications of inheritance have never been fully >> explored and I would like to establish the
frameworkin which future >> work that involves inheritance will be done. > >Precisely. > >  >> It seems to me that
declaringa table to inherit from another, and >> enabling both to be read together by the table* syntax, together >>
implycertain things about an inheritance group: >>  >> 1. All tables in the group must possess all the columns of their
>>ancestor, and all those columns must be of the same type. > >Isn't it this way now?
 

Not if you allow columns to be dropped from or added to an individual
table, after it has become a parent, without enforcing the same change
on its descendants.  I am suggesting that this must be disallowed.  I am
also suggesting that adding columns to a parent requires either logical
column numbering or else physical insertion into the descendants in the
correct sequence.  >> 2. Some constraints at least must be shared - primary key is the most >> obvious example; I think
that_all_ constraints on inherited columns >> should be shared.  It is probably not practicable to force table >>
constraintsto be shared upwards. > >Not sure about this one. See the ranting about the shared indexes >above. Might be
agreat pain.
 

I fear it will be; but I suspect it is necessary in at least some cases
(see below).
 >> 4. Dropping a table implies dropping all its descendants. > >Actually what it does now is to refuse dropping when
descendants>exist. What seems to be the proper solution to this is to implement the >proper DROP TABLE SQL syntax by
addinga RESTRICT/CASCADE at the >end. Restrict refuses dropping if anything (descendants, views, >etc.) references the
table,cascade drops everything else as >well. Implementing this could be your first step to glory ;) since it >seems
it'smore a matter of man hours than conceptual difficulty. Then >again, I could be wrong.
 

In this case, why not simply require `DROP TABLE table*', if table has
descendants?  I'm not at all sure that allowing a CASCADE option for DROP
TABLE is a good idea; someone could end up wiping out most of the database
with an ill-considered command; and RESTRICT should be the normal case.
 >> The grammar for ALTER TABLE allows either `ALTER TABLE table ...' or >> `ALTER TABLE table* ...'.  I would like to
suggestthat an alteration >> to a parent table must necessarily involve all its descendants and >> that alterations to
inheritedcolumns must be done in the appropriate >> parent.  So, given this hierarchy of tables: > >It's been a while
sinceI looked into C++, but when you alter a descendant >(such as making a formerly public method private) you surely
donot affect >the parents. The other way around I think the choice of star-or-not should >be given to the user. But
thisis again one of the issues that have no >point of reference, so I'm glad you bring it up for discussion.
 

Here, my point is that `SELECT * FROM table*' must be able to get a
consistent view throughout the inheritance group.  If an inherited
column is altered, the alteration may be one that would break that view.
The question to be decided is how far we go in enforcing similarity in
the columns that are shared.

Some things cannot be allowed: renaming columns must only be done to
the group as a whole; inherited columns can only be dropped from the 
whole group; a column cannot change its type in a descendant.

However, some differences are going to be allowed.
Consider this, as a case in point:
 a (id char2 primary key, name text not null) b (tp char(1) not null default 'B', supplier text) inherits (a); c (tp
char(1)not null default 'C', customer text) inherits (a);
 

It seems quite a sensible use of inheritance to allow different defaults
for tp in tables b and c.  However, we then have difficulty here:
 d (c1 text) inherits (b,c)

Which tp is to be inherited?  At present, PostgreSQL avoids the problem
by not inheriting any constraints.  We need something like:
 d (c1 text) inherits (b,c) using b.tp


Now I have finished writing this, I can see that I have changed my mind
about the necessity of rigorously enforcing column sharing.  I think this
shows that I am still confused about what we want from inheritance; we
probably need to discuss this quite a bit more thoroughly before we
can come up with a design that we can all be happy with and that will
last.



Final note: I have just realised that most of what I am using inheritance
for could be done with views and unions, provided that we can REFERENCE a
view (which I haven't tested).  One really radical option would be to strip
out inheritance altogether!

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver              PGP key from public servers; key
ID32B8FAA1                ========================================    "Wash me thoroughly from mine iniquity, and
cleanseme      from my sin. For I acknowledge my transgressions; and      my sin is ever before me. Against thee, thee
only,     have I sinned, and done this evil in thy sight..."                                  Psalms 51:2-4