Re: [PATCHES] Inherited Constraints

Ühel kenal päeval, N, 2005-12-08 kell 11:10, kirjutas Simon Riggs:
> On Wed, 2005-12-07 at 21:24 +0000, Simon Riggs wrote:
> > Following patch implements record of whether a constraint is inherited
> > or not, and prevents dropping of inherited constraints.
>
> Patch posted to -patches list.
>
> > What it doesn't do:
> > It doesn't yet prevent dropping the parent constraint, which is wrong,
> > clearly, but what to do about it?
> > 1. make dropping a constraint drop all constraints dependent upon it
> > (without any explicit cascade)
> > 2. add a new clause to ALTER TABLE .... DROP CONSTRAINT .... CASCADE
> >
> > I prefer (1), since it is SQL Standard compliant, easier to remember and
> > automatic de-inheritance is the natural opposite of the automatic
> > inheritance process.
>
> Comments, please -hackers?

It would be logical to do the same as DROP TABLE does, i.e (2).

hannu=# create table parent(i int);
CREATE TABLE
hannu=# create table child() inherits(parent);
CREATE TABLE
hannu=# drop table parent;
NOTICE: table child depends on table parent
ERROR: cannot drop table parent because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.
hannu=#

Maybe there should be another option in addition to CASCADE, say
DISINHERIT, which leaves all child constraints as heads of new
ingeritance hierarchies. DROP CASCADE + ADD BACK ALL CHILD CONSTRAINTS
may be prohibitively expensive for biggish tables.

Another nice (but no doubt more complex) thing would be ability to add
multiple constraints at once, needing only one seqscan to check for
compliance with added constraints and/or making constraint checks
smarter, so that for.ex. "ADD CONSTRAINT CHECK i > 0" could make use of
index on i instead of doing a seqscan. Or if there is a constraint
"CHECK i > 0" then adding another like "CHECK i > -1" would not need to
check actual data either.

> Which implementation should I pick (or another)?
>
> > Further patch will utilise this new knowledge to reduce the number of
> > tests made during constraint_exclusion.

So will hierarchical inheritance be the thing to do to take advantage of
i then ?

year
+- month1
|+-day1
|+-day2
.....
|\-day31
+- month2

etc.

btw, will your DROP patch support multiple inheritance ?

-----------
Hannu