Re: Check constraints on partition parents only?

On Wed, Jul 27, 2011 at 6:39 AM, Nikhil Sontakke
<nikhil(dot)sontakke(at)enterprisedb(dot)com> wrote:
>>>
>>> Yeah, but I think we need to take that chance.  At the very least, we
>>> need to support the equivalent of a non-inherited CHECK (false) on
>>> parent tables.
>>
>> Indeed. I usually enforce that with a trigger that raises an exception,
>> but of course that doesn't help at all with constraint exclusion, and I saw
>> a result just a few weeks ago (I forget the exact details) where it appeared
>> that the plan chosen was significantly worse because the parent table wasn't
>> excluded, so there's a  non-trivial downside from having this restriction.
>>
>
> The downside appears to be non-trivial indeed! I cooked up the attached
> patch to try to allow ALTER...ONLY...CHECK(false) on parent tables.
>
> If this approach looks acceptable, I can provide a complete patch later with
> some documentation changes (I think we ought to tell about this special case
> in the documentation) and a minor test case along with it (if the need be
> felt for the test case).
> Although partitioning ought to be looked at from a different angle
> completely, maybe this small patch can help out a bit in the current scheme
> of things, although this is indeed a unusual special casing... Thoughts?

Well, I don't have anything strongly against the idea of an
uninherited constraint, though it sounds like Tom does. But I think
allowing it just in the case of CHECK (false) would be pretty silly.
And, I'm fairly certain that this isn't going to play nice with
coninhcount... local constraints would have to be marked as local,
else the wrong things will happen later on when you drop them.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company