Re: creating CHECK constraints as NOT VALID

On 11 June 2011 14:40, Thom Brown <thom@linux.com> wrote:
> On 11 June 2011 14:32, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>> On 1 June 2011 23:47, Alvaro Herrera <alvherre@commandprompt.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