Thread

Re: creating CHECK constraints as NOT VALID

Jaime Casanova <jaime@2ndquadrant.com> — 2011-06-15T06:09:15Z

On Tue, Jun 14, 2011 at 4:14 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Excerpts from Alvaro Herrera's message of lun jun 13 18:08:12 -0400 2011:
>> Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011:
>
>> > 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.
>
> Here's the complete patch.
>

psql \h says (among other things) for ALTER TABLE
"""
   ADD table_constraint
   ADD table_constraint_using_index
   ADD table_constraint [ NOT VALID ]
"""

ADD table_constraint appears twice and isn't true that all
table_constraint accept the NOT VALID syntax... maybe we can accpet
the syntax and send an unimplemented feature message for the other
table_constraints?

attached, is a script with the examples i have tried:

EXAMPLE 1:
constraint_exclusion when using NOT VALID check constraints... and it
works well, except when the constraint has been validated, it keeps
ignoring it (which means i won't benefit from constraint_exclusion)
until i execute ANALYZE on the table or close connection

EXAMPLE 2:
if i have a DOMAIN with a NOT VALID check constraint, and i use it as
the new type of a column it checks the constraint

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación