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

From Simon Riggs
Subject Re: [Bizgres-general] A Guide to Constraint Exclusion
Date
Msg-id 1121377266.3970.462.camel@localhost.localdomain
Whole thread Raw
In response to Re: [Bizgres-general] A Guide to Constraint Exclusion  (Hannu Krosing <hannu@skype.net>)
Responses Re: [Bizgres-general] A Guide to Constraint Exclusion  (Hannu Krosing <hannu@skype.net>)
List pgsql-hackers
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 CREAETE 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



pgsql-hackers by date:

Previous
From: Hannu Krosing
Date:
Subject: Re: [Bizgres-general] A Guide to Constraint Exclusion
Next
From: Simon Riggs
Date:
Subject: Re: [Bizgres-general] A Guide to Constraint Exclusion