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

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(dot)Elphick(at)lfix(dot)co(dot)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
>
> ************