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

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 (or later
the constraint added). But there is no way, except blowing
up the DROP INDEX, to prevent someone from removing it 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(at)debis(dot)com (Jan Wieck) #