Skip site navigation (1) Skip section navigation (2)

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

From: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Thom Brown <thom(at)linux(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Jim Nasby <jim(at)nasby(dot)net>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID
Date: 2011-11-25 16:45:34
Message-ID: CAEZATCX8u8GU-M_DFtjksRUQhwm8zur3BQvLamFUX8MwYNntPg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-committerspgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2011-11-25 16:48:09
Subject: Re: proposal : backend startup hook / after logon trigger
Previous:From: Alvaro HerreraDate: 2011-11-25 16:39:31
Subject: Re: Displaying accumulated autovacuum cost

pgsql-committers by date

Next:From: Tom LaneDate: 2011-11-25 19:00:02
Subject: pgsql: Fix erroneous replay of GIN_UPDATE_META_PAGE WAL records.
Previous:From: Dean RasheedDate: 2011-11-25 16:16:29
Subject: Re: [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group