Re: [Bizgres-general] A Guide to Constraint Exclusion

On R, 2005-07-15 at 00:24 +0300, Hannu Krosing wrote:

> There is a good fundamental reason why we dont let people drop columns
> from children or to add them to parent ONLY: if we did not, then there
> would be no way to query from the hierarchy.
>
> I can't see any such reason for forbidding dropping constraints from
> child tables or disallowing CREAETE CONSTRAINT ON parent ONLY , at least
> not for CHECK constraints.
>
> And even disallowing it can probably be circumvented by a clever
> functional CHECK constraint, which checks for also the table it is
> defined on.
>
> OTOH, disallowing all this for child FOREIGN KEY's seems logical though.

Well, having thought more about it, I don't think that row-level
constraints (CHECK, FK and NULL) should be restricted at all by
inheritance hierarchy.

Table-level constraints (UNIQUE and PK) should, but they need to be
fixed first.

What should be done with CHECK, FK and NULL is making a way for marking
them as being defined on some table 'ONLY' if created as such. It should
also be marked with ONLY when any of the constraints inherited from this
one is dropped from child. Then all direct ancestor copies should get
'ONLY' status so thet they can be dumped properly.

Or perhaps just disallow dropping constraints from children, but still
allow creating constraints on ONLY parent table, which seems a cleaner
solution.

Why I also think that allowing this for FK's is good, is that it allows
partitioning tables on both sides of FK relation even without global
UNIQUE/PK constraints, by partitioning both of them on same or
compatible boundaries. By compatible I mean here that we could partition
the PK table on say PK values with a step of 1M but FK table on values
with step of 100K, so that they both will have approximately the same
number of rows per partition and there will be 10 tables with FK
constraints pointing to the same PK partition.

What comes to Toms's May 20 argument that people would be surprised when
they select form a table whith check(i>0) constraint and get out i<0
then I think this is a question of education.
If they don't know better, they will be equally surprised when changing
a row in parent table actually changes a row in child table, or when a
row inserted into child shows up in parent.

The ONLY status of constraints should be accounted for in pg_dump so it
will work right and also shown in psql's \d output so it confuses users
less :)

--
Hannu Krosing <hannu(at)tm(dot)ee>