Re: [HACKERS] Inheritance, referential integrity and other constraints - Mailing list pgsql-hackers

On 2000-01-24, Oliver Elphick mentioned:

> I would like to work on improving implementation of inheritance,
> especially with regard to referential integrity.   I suspect there are
> a number of issues that may be related and will need to be done together.

What I really consider a problem, and it would be great if you could
tackle that, is that there is no real standard that all of this does or
even could follow. For example, I wrote the other day that depending on
which way you see it, the behaviour of alter table x* add colum might be
considered right. Also I just looked into item 'Disallow inherited columns
with the same name as new columns' and it seems that someone actually made
provisions for this to be allowed, meaning that
create table 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.)

> It will also be necessary to ensure that
> added constraints get inherited, when ALTER TABLE ... ADD/DROP
> CONSTRAINT gets implemented.

I assume the semantics of ADD CONSTRAINT will be exactly the same as of
all the other alter table commands, in that if you specify a star then it
gets inherited, if not then not. But the problem with ADD CONSTRAINT is of
course that the entire table needs to be verified against the constraint
before allowing it to be added. This is fine if you do ADD CONSTRAINT
UNIQUE (a, b), because the index will take care of it, but it's trickier
if you add a trigger based constraint. The former might get into 7.0 if I
hurry, the latter most likely not.

What needs discussion is whether indexes should be shared between
inherited tables, or whether each new descendant table needs a new
one. Not sure if this just made sense, though.


> I think that the implications of inheritance have never been fully
> explored and I would like to establish the framework in which future
> work that involves inheritance will be done.

Precisely.

> It seems to me that declaring a table to inherit from another, and
> enabling both to be read together by the table* syntax, together
> imply certain 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?

> 
> 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
> constraints to be shared upwards.

Not sure about this one. See the ranting about the shared indexes
above. Might be a great pain.

> 
> 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 adding a 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's more a matter of man hours than conceptual difficulty. Then
again, I could be wrong.


> The grammar for ALTER TABLE allows either `ALTER TABLE table ...' or
> `ALTER TABLE table* ...'.  I would like to suggest that an alteration
> to a parent table must necessarily involve all its descendants and
> that alterations to inherited columns must be done in the appropriate
> parent.  So, given this hierarchy of tables:

It's been a while since I looked into C++, but when you alter a descendant
(such as making a formerly public method private) you surely do not affect
the parents. The other way around I think the choice of star-or-not should
be given to the user. But this is again one of the issues that have no
point of reference, so I'm glad you bring it up for discussion.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden