Re: [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID - Mailing list pgsql-hackers

From Dean Rasheed
Subject Re: [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID
Date
Msg-id CAEZATCX8u8GU-M_DFtjksRUQhwm8zur3BQvLamFUX8MwYNntPg@mail.gmail.com
Whole thread Raw
In response to Re: [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID  (Dean Rasheed <dean.a.rasheed@gmail.com>)
Responses Re: [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID
List pgsql-hackers
On 25 November 2011 16:16, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> On 24 November 2011 21:50, Alvaro Herrera <alvherre@commandprompt.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@linux.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


pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: Displaying accumulated autovacuum cost
Next
From: Tom Lane
Date:
Subject: Re: proposal : backend startup hook / after logon trigger