Inheritance, referential integrity and other constraints
От | Oliver Elphick |
---|---|
Тема | Inheritance, referential integrity and other constraints |
Дата | |
Msg-id | 200001242152.VAA25637@linda.lfix.co.uk обсуждение исходный текст |
Ответы |
Re: [HACKERS] Inheritance, referential integrity and other constraints
(Peter Eisentraut <peter_e@gmx.net>)
Re: [HACKERS] Inheritance, referential integrity and other constraints (wieck@debis.com (Jan Wieck)) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: "Ansley, Michael"Дата:
Сообщение: RE: [HACKERS] Well, then you keep your darn columns