Re: creating CHECK constraints as NOT VALID

On Mon, Jun 13, 2011 at 9:41 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Josh Berkus's message of lun jun 13 18:11:54 -0400 2011:
>> Alvaro,  Dean,
>>
>> >> 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.
>> > Ouch, yeah, thanks for pointing that out.  Fortunately the patch to fix
>> > this is quite simple.  I don't have it handy right now but I'll post it
>> > soon.
>>
>> Hmmm. Is this the behavior we want with NOT VALID constraints though?
>>
>> I know that if I'm pouring 100m rows into a new partition as part of a
>> repartitioning scheme, I don't want to *ever* check them if I know
>> they're correct because of how I created the table (CREATE TABLE AS ...).
>
> Well, if we don't validate the data, it's an open door for potentially
> corrupt query results.  I'm not really sure that we want to provide
> support for "I don't ever want to check this data for validity" because
> of that.  But then, I just work here.

At any rate, we can't very well have two different meanings for NOT
VALID, so the 9.2 meaning vis-a-vis CHECK constraints had better match
the 9.1 behavior vis-a-vis FOREIGN KEYs.

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