Thread: Inheritance, referential integrity and other constraints
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. In addition, this will be my first attempt to do anything serious in the PostgreSQL code itself, so I would like to get some hints as to what I haven't even thought about! First, I would like to change the definition of the foreign key constraints to allow the inheritance star to follow a table name. This would mean that, for RI purposes, the named table would be aggregated with its descendants. So "REFERENCES tbl" would mean that the foreign key must exist in tbl, but "REFERENCES tbl*" would allow it to exist either in tbl or in any of tbl's descendants. Implications: where * is used, dropping a descendant table is OK, so long as the parent continues to exist. ON DELETE actions would apply to all the relations in the table to be dropped; to reduce complexity, this should be broken down into: `DELETE FROM descendant; DROP TABLE descendant' and the whole should be treated as atomic. If any one relation could not be deleted, the whole operation would fail. Use of ON DELETE or ON UPDATE implies there must be an index on the referring column, to enable checking or deletion to be done speedily. This doesn't seem to happen at the moment. If the reference is to an inheritance group, it would seem to be appropriate that all the tables in the group should use the same index. Similarly, where a unique or primary key constraint is inherited, it may be desirable to use a single index to manage the constraint. The implication of this would be that there must be a check when a table is dropped to make sure that a grouped index is not dropped until the last table in the group is dropped. Is this feasible, or would it require too many changes elsewhere? Another item I would like to get fixed is to make sure that all constraints are inherited when a descendant table is created; this is a current TODO item. It will also be necessary to ensure that added constraints get inherited, when ALTER TABLE ... ADD/DROP CONSTRAINT gets implemented. ====== Design proposal ======= 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. 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. 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. 3. There seems to be no need to enforce similar restrictions on GRANT. In fact it is quite likely that different permissions could apply to different tables in the hierarchy. 4. Dropping a table implies dropping all its descendants. ============================== I would like to consider the implications of this proposal in the light of the ALTER TABLE commands that have recently been added. 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: t1 (c1 char(2) primary key, c2 text) t2 (c3 int not null c4 timestamp default current_timestamp) inherits (t1) t3 (c5 text not null) inherits (t2) adding a column to t1, means the same column must be added to t2 and t3 and must appear before any columns originating in t2; columns c1 to c4 cannot be dropped from table t3 unless they are also dropped from the parents. Alterations to c2 must be done in t1, and alterations to c4 must be done in t2. Any table constraint applied to t1 would automatically be inherited by t2 and t3, a new constraint added to t2 would be inherited by t3 but would not affect t1. Attempts to use ALTER TABLE to bypass these restrictions should be disallowed. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "If anyone has material possessions and sees his brother in need but has no pity on him, how can the love of God be in him?" I John3:17
As long as you're working on this area you could fix the problem where if you do ALTER table* ADD COLUMN ... pg_dump no longer works because the column orders have changed in different inherited tables. Oliver Elphick wrote: > > 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. > In addition, this will be my first attempt to do anything serious in > the PostgreSQL code itself, so I would like to get some hints as > to what I haven't even thought about! > > First, I would like to change the definition of the foreign key > constraints to allow the inheritance star to follow a table name. > This would mean that, for RI purposes, the named table would be > aggregated with its descendants. So "REFERENCES tbl" would mean that > the foreign key must exist in tbl, but "REFERENCES tbl*" would allow it > to exist either in tbl or in any of tbl's descendants. > > Implications: where * is used, dropping a descendant table is OK, so > long as the parent continues to exist. ON DELETE actions would apply > to all the relations in the table to be dropped; to reduce complexity, > this should be broken down into: > `DELETE FROM descendant; DROP TABLE descendant' > and the whole should be treated as atomic. If any one relation could > not be deleted, the whole operation would fail. > > Use of ON DELETE or ON UPDATE implies there must be an index on the > referring column, to enable checking or deletion to be done speedily. > This doesn't seem to happen at the moment. If the reference is to > an inheritance group, it would seem to be appropriate that all the > tables in the group should use the same index. Similarly, where > a unique or primary key constraint is inherited, it may be desirable > to use a single index to manage the constraint. The implication of > this would be that there must be a check when a table is dropped > to make sure that a grouped index is not dropped until the last > table in the group is dropped. > > Is this feasible, or would it require too many changes elsewhere? > > Another item I would like to get fixed is to make sure that all > constraints are inherited when a descendant table is created; this > is a current TODO item. It will also be necessary to ensure that > added constraints get inherited, when ALTER TABLE ... ADD/DROP > CONSTRAINT gets implemented. > > ====== Design proposal ======= > > 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. > > 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. > > 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. > > 3. There seems to be no need to enforce similar restrictions on > GRANT. In fact it is quite likely that different permissions could > apply to different tables in the hierarchy. > > 4. Dropping a table implies dropping all its descendants. > > ============================== > > I would like to consider the implications of this proposal in the light > of the ALTER TABLE commands that have recently been added. > > 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: > > t1 (c1 char(2) primary key, > c2 text) > > t2 (c3 int not null > c4 timestamp default current_timestamp) inherits (t1) > > t3 (c5 text not null) inherits (t2) > > adding a column to t1, means the same column must be added to t2 and t3 > and must appear before any columns originating in t2; columns c1 to c4 > cannot be dropped from table t3 unless they are also dropped from the > parents. Alterations to c2 must be done in t1, and alterations to c4 > must be done in t2. Any table constraint applied to t1 would automatically > be inherited by t2 and t3, a new constraint added to t2 would be > inherited by t3 but would not affect t1. > > Attempts to use ALTER TABLE to bypass these restrictions should be > disallowed. > > -- > Oliver Elphick Oliver.Elphick@lfix.co.uk > Isle of Wight http://www.lfix.co.uk/oliver > PGP key from public servers; key ID 32B8FAA1 > ======================================== > "If anyone has material possessions and sees his > brother in need but has no pity on him, how can the > love of God be in him?" > I John 3:17 > > ************
Chris Bitmead wrote: >As long as you're working on this area you could fix the problem where >if you do ALTER table* ADDCOLUMN ... pg_dump no longer works because >the column orders have changed in different inherited tables. It seems that this might be quite a problem; I would not like to have to do a physical insert into every row in a huge table. Would it be feasible to add a column order attribute to pg_attribute for tables altered in this way? A null entry in that would indicate the table was unaltered from its creation. Perhaps this could be combined with the idea of column hiding: a zero column number would mean it was hidden. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "My little children, let us not love in word, neither in tongue; but in deed and in truth." I John 3:18
On 2000-01-25, Chris Bitmead mentioned: > As long as you're working on this area you could fix the problem where > if you do ALTER table* ADD COLUMN ... pg_dump no longer works because > the column orders have changed in different inherited tables. This should be fixed in pg_dump then. As I see it, ALTER table* ADD COLUMN does exactly the right thing. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
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
Re: [HACKERS] Inheritance, referential integrity and other constraints
From
"Ross J. Reedstrom"
Date:
On Wed, Jan 26, 2000 at 07:34:29PM +0100, Peter Eisentraut wrote: > On 2000-01-25, Chris Bitmead mentioned: > > > As long as you're working on this area you could fix the problem where > > if you do ALTER table* ADD COLUMN ... pg_dump no longer works because > > the column orders have changed in different inherited tables. > > This should be fixed in pg_dump then. As I see it, ALTER table* ADD COLUMN > does exactly the right thing. No, the problem is that right now, the order of columns in a child table depends on the exact history of how all the columns got into each table. Ideally, we want to be able to describe all the tables without reference to history, only to (meta)content. The exact order of columns in a table really isn't much use to users, in any case (even though it is visible, technically. This had got to be a backward compatability feature of the original SQL, isn't it?) Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
At 07:35 PM 1/26/00 +0100, Peter Eisentraut wrote: >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. Are you basing this on your earlier comment: " Or maybe look at it this way: create table test1 (a int4); create table test2 (b int4) inherits (test1); ^ col #1 ^ col #2 alter table test1* add column c int4; ^ col #3 "? If so, I thought Oliver pointed out that you had the numbering wrong. I thought so, too... Which is right? - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
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
Peter Eisentraut wrote: > 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. Shared indexes should definitely be allowed, if not the default. When you've got deep hierarchies of inheritance it makes queries slow to have to consult a whole lot of indexes. > > 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? No, because you can do an ALTER TABLE (without the *) on the base, and it doesn't get propagated to the descendants. Possibly this should be disallowed, although it needs more thought. BTW, if I remember right, Informix/Illustra has made the "*" also include subclasses syntax the default. In other words you DON'T use the *. If you only want a particular class and not sub-classes you have to write "ONLY <classname>", or something. IMHO this is the RIGHT THING. For almost everything (eg ALTER TABLE above) you always want to include subclasses. Same goes for any random query. This is the OO way, you don't think about subclasses unless you are doing something strange. This is a pretty big change, but IMHO it should be made at some time. "*" syntax should be eliminated and made default and something like ONLY be added for when you really only want that one table. This won't affect anyone using postgres as a RDBMS, only those people using it as ORDBMS. > 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. I don't think we're talking about descendants. Rather parents. > The other way around I think the choice of star-or-not should > be given to the user. But then you can create a hierachy using ALTER that you couldn't have created using plain CREATEs. This is bad I think and also has never been done in any object system/language I've ever heard of.
Oliver Elphick wrote: > 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.) I agree, and think that the "*" is a trap in general. Which is why I suggest we go the Informix/Illustra route and dump "*" altogether, replacing it with "ONLY" or some such, when you don't want inherited. > 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 indexes too should be inherited (physically as well as logically) unless you choose the ONLY keyword. > 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 Hmmm. I don't think that's right at all. For example tp might be a different type in b and c, and code might depend on that. It would be logically unreasonable to have an inherited "d" not have BOTH tp from b and c. I think from memory, Eiffel solves this by renaming doesn't it? I think you need either renaming or scope resolving syntax. This would probably get very messy, and I think it's probably quite sufficient to force the user to not inherit the same name from b and C. If you want that, you have to rename tp to be something else in b and/or c. > 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! Please no! Yep, inheritance in SELECT is actually implemented as a UNION internally. But don't dump it!
Chris Bitmead wrote: >Oliver Elphick wrote: ... >> 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 inheritanceto allow different defaults >> for tp in tables b and c. However, we then have difficulty here: >> >> d (c1text) inherits (b,c) >> >> Which tp is to be inherited? At present, PostgreSQL avoids the problem >> by not inheritingany constraints. We need something like: >> >> d (c1 text) inherits (b,c) using b.tp > >Hmmm. I don't thinkthat's right at all. For example tp might be a >different type in b and c, and code might depend on that. No, the inheritance system doesn't allow them to be different types. You get an error if you try to create such a table: junk=> \d d Table = d +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | id | char() | 1 | | words | text | var | | nu | float8 | 8 | +----------------------------------+----------------------------------+-------+ junk=> \d b Table = b +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | id | char() | 1 | | words | text | var | | nu | numeric | 8.2 | +----------------------------------+----------------------------------+-------+ junk=> \d d Table = d +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | id | char() | 1 | | words | text | var | | nu | float8 | 8 | +----------------------------------+----------------------------------+-------+ junk=> create table e (x text) inherits (b,d); ERROR: float8 and numeric conflict for nu And this is right because `SELECT nu FROM b*' and `SELECT nu FROM d*' both need to work. > It would be >logically unreasonable to have an inherited "d"not have BOTH tp from b >and c. Because the column names are identical, they are overlaid and treated as the same column. This is so whether or not they ultimately derive from the same parent, so it isn't strictly a case of repeated inheritance as in Eiffel. (There, repeatedly inherited features of the same parent are silently combined, but identical names from unrelated classes are conflicts.) > I think from memory, Eiffel solves this by renaming doesn't it? I >think you need either renaming or scope resolvingsyntax. This would >probably get very messy, and I think it's probably quite sufficient to >force the user to notinherit the same name from b and C. If you want >that, you have to rename tp to be something else in b and/or c. But we do allow this at the moment; identically named and typed columns are taken to be the same column. This is so, even if they don't appear in the same order: junk=> \d m Table = m +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | c1 | char() | 2 | | c2 | int4 | 4 | | c3 | text | var | | c4 | numeric | 8.2 | +----------------------------------+----------------------------------+-------+ so it looks as if the recent discussion about column ordering and inheritance was off the point! >> 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 stri >p >> out inheritance altogether! > >Please no! Yep, inheritance in SELECT is actually implemented as a UNION >internally.But don't dump it! Well no; I didn't really mean it! -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "Come now, and let us reason together, saith the LORD; though your sins be as scarlet, they shall be as white as snow; though they be red like crimson, they shall be as wool." Isaiah 1:18
On 2000-01-26, Don Baccus mentioned: > If so, I thought Oliver pointed out that you had the numbering wrong. > I thought so, too... Someone made the good point that, independent of which numbering you might prefer, using add column gives you a setup which you could not achieve using only create table. That makes sense to me, so I'm withdrawing my argument. ;) -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
On 2000-01-26, Oliver Elphick mentioned:
> >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.)
>
> That's a relief! Unless you have actually removed the ability to do
> repeated inheritance?
Ugh, I just realized that of course you _have_ to allow duplicate column
names, e.g. in a scheme like b a < > d c
the columns of "a" would arrive duplicated at "d" and the above logic
would merge them. I haven't finished that fix yet, so I better scrap it
now. Seem like this TODO item was really a non-starter.
> 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!
Sure, I could live with that. It's not like it ever worked (in its
entirety). And any
table a (a1, a2, a3)
table b (b1, b2) inherits (a)
can also be implemented as
table a (a_id, a1, a2, a3)
table b_bare (b_id, b1, b2)
create view b as select a1, a2, a3, b1, b2 from outer join a, b on a_id = b_id
{or whatever that syntax was}
plus an insert rule or two.
It would make the rest of the code soooo much easier. (Sarcasm intended,
but a glimpse of truth as well.)
+++
Slashdot: "Self-proclaimed most advanced open-source database drops
object-oriented facilities to simplify code base"
AC reply: "Now when's KDE moving to C?"
+++
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
Oliver Elphick wrote: > No, the inheritance system doesn't allow them to be different types. > You get an error if you try to create such a table: Hmm. While it might allow it, I can't see the logic in it. Can't think of any OO language that thinks this way. All other languages you get two different variables either with :: scope resolution in C++ or renaming in Eiffel. > Because the column names are identical, they are overlaid and treated > as the same column. This is so whether or not they ultimately derive > from the same parent, so it isn't strictly a case of repeated inheritance > as in Eiffel. (There, repeatedly inherited features of the same parent > are silently combined, but identical names from unrelated classes are > conflicts.) Which seems like the right thing to me.
Re: [HACKERS] Inheritance, referential integrity and other constraints
From
wieck@debis.com (Jan Wieck)
Date:
Oliver Elphick wrote: > 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. > In addition, this will be my first attempt to do anything serious in > the PostgreSQL code itself, so I would like to get some hints as > to what I haven't even thought about! > > First, I would like to change the definition of the foreign key > constraints to allow the inheritance star to follow a table name. > This would mean that, for RI purposes, the named table would be > aggregated with its descendants. So "REFERENCES tbl" would mean that > the foreign key must exist in tbl, but "REFERENCES tbl*" would allow it > to exist either in tbl or in any of tbl's descendants. I haven't thought about it in depth up to now, but I think that would cause much trouble in the RI triggers. They don't even have the full functionality and must be tested well for 7.0. Can we wait with such an issue until after 7.0. > Use of ON DELETE or ON UPDATE implies there must be an index on the > referring column, to enable checking or deletion to be done speedily. > This doesn't seem to happen at the moment. If the reference is to > an inheritance group, it would seem to be appropriate that all the > tables in the group should use the same index. Similarly, where > a unique or primary key constraint is inherited, it may be desirable > to use a single index to manage the constraint. The implication of > this would be that there must be a check when a table is dropped > to make sure that a grouped index is not dropped until the last > table in the group is dropped. Yes and yes. I thought about checking if there is a unique index at the time, the referencing table is created (orlater the constraint added). But there is no way, except blowing up the DROP INDEX, to prevent someone from removingit later. And doing so would prevent then from fixing a corrupted index, so I'd be the first to vote against. > Another item I would like to get fixed is to make sure that all > constraints are inherited when a descendant table is created; this > is a current TODO item. It will also be necessary to ensure that > added constraints get inherited, when ALTER TABLE ... ADD/DROP > CONSTRAINT gets implemented. Yepp. But please don't start on it before 7.0. I would expect touching it right now could become a showstopper. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) #