Re: creating CHECK constraints as NOT VALID

Excerpts from Jaime Casanova's message of mié jun 15 02:09:15 -0400 2011:

> 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?

Okay, I removed the redundant line from the synposis. As far as other
types of constraints go, I don't feel we need to do anything here -- the
description already says that it only works on FKs and CHECK.

I'm not going to go to the trouble of fixing the redundant
table_constraint line in the synopsis in HEAD -- if someone else wants
to send a patch to fix that, I can apply it easily enough.

> 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

Hmm, I think this means we need to send a sinval message to invalidate
cached plans when a constraint is validated. I'll see about this.

> 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

I think this is OK. The NOT VALID declaration says that the existing
columns declared using this constraint is not checked, but new columns
(as well as new data in existing columns) are certainly going to require
their values to pass the checks.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support