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

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
);

And the COPY command is:

COPY a (num) FROM stdin;
90
\.

So this is broken.

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

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