Re: creating CHECK constraints as NOT VALID

On 11 June 2011 16:40, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> On 11 June 2011 14:40, Thom Brown <thom(at)linux(dot)com> wrote:
>> On 11 June 2011 14:32, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>>> On 1 June 2011 23:47, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>>>>
>>>> Here's a complete patch with all this stuff, plus doc additions and
>>>> simple regression tests for the new ALTER DOMAIN commands.
>>>>
>>>>    Enable CHECK constraints to be declared NOT VALID
>>>>
>>>>    This means that they can initially be added to a large existing table
>>>>    without checking its initial contents, but new tuples must comply to
>>>>    them; a separate pass invoked by ALTER TABLE / VALIDATE can verify
>>>>    existing data and ensure it complies with the constraint, at which point
>>>>    it is marked validated and becomes a normal part of the table ecosystem.
>>>>
>>>
>>> I think that you also need to update the constraint exclusion code
>>> (get_relation_constraints() or nearby), otherwise the planner might
>>> exclude a relation on the basis of a CHECK constraint that is not
>>> currently VALID.
>>
>> Do the standards explicitly stipulate an expected behaviour for this?
>
> No I believe that this is a PostgreSQL-specific optimisation, and we
> need to ensure that queries return the correct results with
> constraint_exclusion on.
>
>> And does such a problem affect the invalid foreign key change too?
>
> No only CHECK constraints (and possibly NOT NULL constraints in the future).
>
> Regards,
> Dean
>

Since you've mentioned the SQL spec, its worth noting that whilst I think
that this feature will be very useful, it's not the feature in the SQL
spec (at least not in my version).

The SQL spec feature is to mark a constraint as NOT ENFORCED, which
means that no data (existing or new) is checked against the
constraint. It's as if the constraint were not present at all. In
Oracle this corresponds to the syntax

ALTER TABLE mytable ENABLE/DISABLE myconstraint

which is actually quite handy during a bulk load/update - disable all
your constraints, do the bulk operation and then re-enable them
(automatically re-validating them). This is better than dropping and
re-creating the constraints because you don't need to remember all the
constraint definitions.

I can see both features being quite useful in different situations.

Regards,
Dean