Re: [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID

On 25 November 2011 16:16, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> On 24 November 2011 21:50, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>>
>> Excerpts from Alvaro Herrera's message of vie nov 11 00:32:33 -0300 2011:
>>> Excerpts from Thom Brown's message of jue nov 10 21:28:06 -0300 2011:
>>> >
>>> > On 10 November 2011 23:56, Thom Brown <thom(at)linux(dot)com> wrote:
>>>
>>> > > The dump correctly contains:
>>> > >
>>> > > CREATE TABLE a (
>>> > >    num integer,
>>> > >    CONSTRAINT meow CHECK ((num < 20)) NOT VALID
>>> > > );
>>> >
>>> > Actually I mean incorrectly contains, because the constraint needs
>>> > adding after the data insertion, not as part of the create table
>>> > statement.
>>>
>>> Interesting, thanks -- I'll look into it.
>>
>> I have just pushed a fix for this.  Thanks for the report and sorry for
>> the delay.
>>
>
> There is a similar problem with NOT VALID check constraints on
> domains. These are still being dumped as part of the CREATE DOMAIN
> statement, which is invalid syntax, so they need to be dumped
> separately from the domain creation, and presumably also after any
> data for tables that use them.
>
> Regards,
> Dean
>

Looking back at Thom's original example, it seems odd to allow this
syntax at all:

CREATE TABLE a (
num integer,
CONSTRAINT meow CHECK ((num < 20)) NOT VALID
);

It's not documented, but is currently allowed. However, since all data
subsequently added to the table is checked against the constraint, the
constraint is guaranteed to be valid, so there seems to be no point in
allowing it to be declared NOT VALID.

Regards,
Dean