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.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
> 
> ************