Re: [Bizgres-general] A Guide to Constraint Exclusion - Mailing list pgsql-hackers

Bruce: could you change this TODO item
 o Prevent child tables from altering constraints like CHECK that were   inherited from the parent table

to
 o Prevent new child tables from inheriting constraints defined on   parents with keyword ONLY:     alter table ONLY t
addconstraint c check(x=y);   Prevent child tables from altering constraints that were   inherited from the parent
table(i.e were defined without    ONLY/with ALL)
 

This is a less restrictive solution to the same problem.

On N, 2005-07-14 at 22:41 +0100, Simon Riggs wrote:
> On Fri, 2005-07-15 at 00:24 +0300, Hannu Krosing wrote:
> > On N, 2005-07-14 at 21:29 +0100, Simon Riggs wrote:
> > > On Thu, 2005-07-14 at 00:13 +0300, Hannu Krosing wrote:
> > > > On K, 2005-07-13 at 11:53 +0100, Simon Riggs wrote:
> > > 
> > > > > We aren't able to exclude the parent table from the above query because
> > > > > no Constraint was defined upon it. Since, in our example, the parent is
> > > > > empty there will be little effect on the query performance. It would be
> > > > > a mistake to attempt to get around this by placing a Constraint on the
> > > > > parent, since that would then automatically be created on all child
> > > > > tables also. So we can never exclude the parent without automatically
> > > > > excluding *all* of the children also.
> > > > 
> > > > At least in 8.0 you can drop the inherited constraint from child table:
> > > 
> > > Yes, you can. But in discussion on -hackers on 20 May there was
> > > agreement (OK, me agreeing with Tom) that being able to drop inherited
> > > constraints was a bug (or at least a deprecated feature...)
> > 
> > Why is it a bug ?
> > 
> > Tom wrote on May 20:
> > > I think a good argument can be made that the above behavior is a bug,
> > > and that the ALTER command should have been rejected.  We've gone to
> > > great lengths to make sure you can't ALTER a child table to make it
> > > incompatible with the parent in terms of the column names and types;
> > > shouldn't this be true of check constraints as well?
> > 
> > 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 CREATE CONSTRAINT ON parent ONLY , at least
> > not for CHECK constraints. 
> 
> If we allow DROPing them, why allow them to be inherited in the first
> place? One or the other, not both.
> 
> I do still agree with Tom on that, but as I said at the time, I don't
> see it as a big enough problem to spend time removing that feature. But
> I personally wouldn't grow to rely on its existence either.
> 
> There are other ways...
> 
> > 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.
> > 
> > OTOOH, to be symmetrical with previous, we should also have UNIQUE and
> > PK constraints that span all the inheritance hierarchy, but I don't want
> > to go there now :) 
> 
> Well, I am working towards that.
> 
> Best Regards, Simon Riggs
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org
-- 
Hannu Krosing <hannu@skype.net>