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

On 10 November 2011 23:56, Thom Brown <thom(at)linux(dot)com> wrote:
> On 18 July 2011 02:46, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>> Excerpts from Robert Haas's message of dom jul 17 20:36:49 -0400 2011:
>>
>>> > Does git allow for additional commit fields? That would allow for easy tracking without much additional burden on committers.
>>>
>>> I mean, there's git notes, but that's not exactly what we're looking
>>> for here, and I don't see how it would easy the burden on committers
>>> anyway, and it doesn't solve the problem of not being able to change
>>> things after the fact.
>>
>> Eh, git notes *can* be changed after the fact, and are *not* append
>> only.  And as the committer who started this discussion in the first
>> place, I don't have any problem with having to edit them separately from
>> the commit message, which is a tiny portion of the work involved in
>> figuring out the patch, anyway.
>>
>> What's not clear to me, is whether they are sent to the remote when you
>> invoke git push.  I'm not clear on whether this needing a separate
>> command or more arguments to push, or it's just not possible.
>>
>>> I think this is a clear-cut case of needing some sort of web
>>> application to manage this.  I'd even be willing to help fill in the
>>> relevant info.  But I'm not going to write it myself...
>>
>> Having a web app would work for me, but a larger job than just using git
>> notes.  So if the notes really work, +1 to them from me.
>
> I've only just noticed that this still doesn't work for me:
>
> test6=# CREATE TABLE a (num INT);
> CREATE TABLE
> test6=# INSERT INTO a (num) VALUES (90);
> INSERT 0 1
> test6=# ALTER TABLE a ADD CONSTRAINT meow CHECK (num < 20) NOT VALID;
> ALTER TABLE
> test6=# \q
> toucan:~ thom$ createdb test7
> toucan:~ thom$ pg_dump -f /tmp/test.sql test6
> toucan:~ thom$ psql test7 < /tmp/test.sql
>
> SET
> SET
> SET
> SET
> SET
> CREATE EXTENSION
> COMMENT
> SET
> SET
> SET
> CREATE TABLE
> ALTER TABLE
> ERROR:  new row for relation "a" violates check constraint "meow"
> CONTEXT:  COPY a, line 1: "90"
> STATEMENT:  COPY a (num) FROM stdin;
> ERROR:  new row for relation "a" violates check constraint "meow"
> CONTEXT:  COPY a, line 1: "90"
> REVOKE
> REVOKE
> GRANT
> GRANT
>
> 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.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company