pgsql: Enable CHECK constraints to be declared NOT VALID

Enable CHECK constraints to be declared NOT VALID

This means that they can initially be added to a large existing table
without checking its initial contents, but new tuples must comply to
them; a separate pass invoked by ALTER TABLE / VALIDATE can verify
existing data and ensure it complies with the constraint, at which point
it is marked validated and becomes a normal part of the table ecosystem.

An non-validated CHECK constraint is ignored in the planner for
constraint_exclusion purposes; when validated, cached plans are
recomputed so that partitioning starts working right away.

This patch also enables domains to have unvalidated CHECK constraints
attached to them as well by way of ALTER DOMAIN / ADD CONSTRAINT / NOT
VALID, which can later be validated with ALTER DOMAIN / VALIDATE
CONSTRAINT.

Thanks to Thom Brown, Dean Rasheed and Jaime Casanova for the various
reviews, and Robert Hass for documentation wording improvement
suggestions.

This patch was sponsored by Enova Financial.

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/897795240cfaaed724af2f53ed2c50c9862f951f

Modified Files
--------------
doc/src/sgml/catalogs.sgml | 2 +-
doc/src/sgml/ref/alter_domain.sgml | 45 +++++-
doc/src/sgml/ref/alter_table.sgml | 12 +-
src/backend/access/common/tupdesc.c | 1 +
src/backend/catalog/heap.c | 13 +-
src/backend/commands/tablecmds.c | 236 ++++++++++++++++++++++++----
src/backend/commands/typecmds.c | 140 +++++++++++++++--
src/backend/optimizer/util/plancat.c | 11 +-
src/backend/parser/gram.y | 12 ++-
src/backend/tcop/utility.c | 4 +
src/backend/utils/cache/relcache.c | 1 +
src/include/access/tupdesc.h | 1 +
src/include/catalog/heap.h | 1 +
src/include/commands/typecmds.h | 1 +
src/include/nodes/parsenodes.h | 3 +
src/test/regress/expected/alter_table.out | 101 ++++++++++++
src/test/regress/expected/domain.out | 11 ++
src/test/regress/sql/alter_table.sql | 46 ++++++
src/test/regress/sql/domain.sql | 10 ++
19 files changed, 581 insertions(+), 70 deletions(-)

Excerpts from Alvaro Herrera's message of jue jun 30 11:58:09 -0400 2011:
> Enable CHECK constraints to be declared NOT VALID
>
> [...]
>
> This patch was sponsored by Enova Financial.

Robert Hass (whose name I misspelled in the commit message above) just
mentioned to me (in an answer to my apologizing about it) that he didn't
think that mentioning sponsors for patch development was a good idea.

I don't think we have a policy for this, but I have done it for some
time now and nobody has complained, so I sort of assumed it was okay.
Besides, some of the people pouring the money in does care about it;
moreover, it provides a little incentive for other companies that might
also be in a position to fund development but lack the "peer approval"
of the idea, or a final little push.

So what's the general opinion here?

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Jun 30, 2011, at 12:09 PM, Alvaro Herrera wrote:

> Robert Hass (whose name I misspelled in the commit message above) just
> mentioned to me (in an answer to my apologizing about it) that he didn't
> think that mentioning sponsors for patch development was a good idea.
>
> I don't think we have a policy for this, but I have done it for some
> time now and nobody has complained, so I sort of assumed it was okay.
> Besides, some of the people pouring the money in does care about it;
> moreover, it provides a little incentive for other companies that might
> also be in a position to fund development but lack the "peer approval"
> of the idea, or a final little push.
>
> So what's the general opinion here?

I certainly see no harm in it, and contributors at all levels -- including sponsors of new features or fixes -- ought to be acknowledged and thanked.

Best,

David

On Thu, 2011-06-30 at 15:09 -0400, Alvaro Herrera wrote:

<snip>

> I don't think we have a policy for this, but I have done it for some
> time now and nobody has complained, so I sort of assumed it was okay.
> Besides, some of the people pouring the money in does care about it;
> moreover, it provides a little incentive for other companies that
> might also be in a position to fund development but lack the "peer
> approval" of the idea, or a final little push.
>
> So what's the general opinion here?

+1 for adding sponsor name to the commit message. It will encourage
companies more.
--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz

On tor, 2011-06-30 at 15:09 -0400, Alvaro Herrera wrote:
> Robert Hass (whose name I misspelled in the commit message above) just
> mentioned to me (in an answer to my apologizing about it) that he
> didn't think that mentioning sponsors for patch development was a good
> idea.
>
> I don't think we have a policy for this, but I have done it for some
> time now and nobody has complained, so I sort of assumed it was okay.
> Besides, some of the people pouring the money in does care about it;
> moreover, it provides a little incentive for other companies that
> might also be in a position to fund development but lack the "peer
> approval" of the idea, or a final little push.

I think commit messages should be restricted to describing what was
changed and who is responsible for it. Once we open it for things like
sponsorship, what's to stop people from adding personal messages, what
they had for breakfast, "currently listening to", or just selling
advertising space in each commit message for 99 cents?

On Sun, Jul 3, 2011 at 20:51, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On tor, 2011-06-30 at 15:09 -0400, Alvaro Herrera wrote:
>> Robert Hass (whose name I misspelled in the commit message above) just
>> mentioned to me (in an answer to my apologizing about it) that he
>> didn't think that mentioning sponsors for patch development was a good
>> idea.
>>
>> I don't think we have a policy for this, but I have done it for some
>> time now and nobody has complained, so I sort of assumed it was okay.
>> Besides, some of the people pouring the money in does care about it;
>> moreover, it provides a little incentive for other companies that
>> might also be in a position to fund development but lack the "peer
>> approval" of the idea, or a final little push.
>
> I think commit messages should be restricted to describing what was
> changed and who is responsible for it.  Once we open it for things like

+1.

> sponsorship, what's to stop people from adding personal messages, what
> they had for breakfast, "currently listening to", or just selling
> advertising space in each commit message for 99 cents?

Well, listing the sponsor pretty much *is* selling advertising
space... Though I hope it was more than 99 cents ;)

We definitely need a good venue for advertising sponsorship of
features, but I don't think the commit message is that.

(Also, a lot of the commit messages would contain "feature sponsored
by redhat" for example - else we'd exclude those who invest a *lot* of
time and money in postgres while promoting those that spend money on
single and/or smaller things..)

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Sun, Jul 3, 2011 at 7:51 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On tor, 2011-06-30 at 15:09 -0400, Alvaro Herrera wrote:
>> Robert Hass (whose name I misspelled in the commit message above) just
>> mentioned to me (in an answer to my apologizing about it) that he
>> didn't think that mentioning sponsors for patch development was a good
>> idea.
>>
>> I don't think we have a policy for this, but I have done it for some
>> time now and nobody has complained, so I sort of assumed it was okay.
>> Besides, some of the people pouring the money in does care about it;
>> moreover, it provides a little incentive for other companies that
>> might also be in a position to fund development but lack the "peer
>> approval" of the idea, or a final little push.
>
> I think commit messages should be restricted to describing what was
> changed and who is responsible for it.  Once we open it for things like
> sponsorship, what's to stop people from adding personal messages, what
> they had for breakfast, "currently listening to", or just selling
> advertising space in each commit message for 99 cents?

Agreed.

We should credit people somewhere, but not here.

Otherwise, we'll be forced to add "Sponsored by RedHat", "Sponsored by
2ndQuadrant" etc onto commit messages.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Simon Riggs wrote:
> On Sun, Jul 3, 2011 at 7:51 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> > On tor, 2011-06-30 at 15:09 -0400, Alvaro Herrera wrote:
> >> Robert Hass (whose name I misspelled in the commit message above) just
> >> mentioned to me (in an answer to my apologizing about it) that he
> >> didn't think that mentioning sponsors for patch development was a good
> >> idea.
> >>
> >> I don't think we have a policy for this, but I have done it for some
> >> time now and nobody has complained, so I sort of assumed it was okay.
> >> Besides, some of the people pouring the money in does care about it;
> >> moreover, it provides a little incentive for other companies that
> >> might also be in a position to fund development but lack the "peer
> >> approval" of the idea, or a final little push.
> >
> > I think commit messages should be restricted to describing what was
> > changed and who is responsible for it. ?Once we open it for things like
> > sponsorship, what's to stop people from adding personal messages, what
> > they had for breakfast, "currently listening to", or just selling
> > advertising space in each commit message for 99 cents?
>
> Agreed.
>
> We should credit people somewhere, but not here.
>
> Otherwise, we'll be forced to add "Sponsored by RedHat", "Sponsored by
> 2ndQuadrant" etc onto commit messages.

Agreed. On one level I like the sponsor message, but on the other
having "Sponsored by RedHat" on every Tom Lane item will get tiring.
;-)

Can we add text if the employer is _not_ the feature sponsor?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Jul 11, 2011, at 8:34 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Can we add text if the employer is _not_ the feature sponsor?

I don't see that as much better. Commit messages should not be ads, IMHO. There are plenty of ways to give credit without polluting the commit log with it.

...Robert

On Tue, Jul 12, 2011 at 02:34, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Simon Riggs wrote:
>> On Sun, Jul 3, 2011 at 7:51 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>> > On tor, 2011-06-30 at 15:09 -0400, Alvaro Herrera wrote:
>> >> Robert Hass (whose name I misspelled in the commit message above) just
>> >> mentioned to me (in an answer to my apologizing about it) that he
>> >> didn't think that mentioning sponsors for patch development was a good
>> >> idea.
>> >>
>> >> I don't think we have a policy for this, but I have done it for some
>> >> time now and nobody has complained, so I sort of assumed it was okay.
>> >> Besides, some of the people pouring the money in does care about it;
>> >> moreover, it provides a little incentive for other companies that
>> >> might also be in a position to fund development but lack the "peer
>> >> approval" of the idea, or a final little push.
>> >
>> > I think commit messages should be restricted to describing what was
>> > changed and who is responsible for it. ?Once we open it for things like
>> > sponsorship, what's to stop people from adding personal messages, what
>> > they had for breakfast, "currently listening to", or just selling
>> > advertising space in each commit message for 99 cents?
>>
>> Agreed.
>>
>> We should credit people somewhere, but not here.
>>
>> Otherwise, we'll be forced to add "Sponsored by RedHat", "Sponsored by
>> 2ndQuadrant" etc onto commit messages.
>
> Agreed.  On one level I like the sponsor message, but on the other
> having "Sponsored by RedHat" on every Tom Lane item will get tiring.
> ;-)
>
> Can we add text if the employer is _not_ the feature sponsor?

That would be quite unfair to those who *do* employ committers....
Basically you'd get credit only if you didn't employ a committer.

This all becomes much easier if we keep the ads out of the commit
messages, and stick to the technical side there. And find another
venue for the other credit.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Excerpts from Magnus Hagander's message of mar jul 12 09:34:56 -0400 2011:

> > Agreed.  On one level I like the sponsor message, but on the other
> > having "Sponsored by RedHat" on every Tom Lane item will get tiring.
> > ;-)
> >
> > Can we add text if the employer is _not_ the feature sponsor?
>
> That would be quite unfair to those who *do* employ committers....
> Basically you'd get credit only if you didn't employ a committer.

Well, that has worked well for my case -- I haven't ever credited my
employer, only those that have specifically hired us for a particular
patch. My employer gets a lot of "credit" in the form of email
signatures, like the one below ;-)

But I see your point and I will stick to whatever policy we come up with
(assuming we come up with one).

> This all becomes much easier if we keep the ads out of the commit
> messages, and stick to the technical side there. And find another
> venue for the other credit.

I'm open to ideas.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
> Excerpts from Magnus Hagander's message of mar jul 12 09:34:56 -0400 2011:
>
> > > Agreed. On one level I like the sponsor message, but on the other
> > > having "Sponsored by RedHat" on every Tom Lane item will get tiring.
> > > ;-)
> > >
> > > Can we add text if the employer is _not_ the feature sponsor?
> >
> > That would be quite unfair to those who *do* employ committers....
> > Basically you'd get credit only if you didn't employ a committer.
>
> Well, that has worked well for my case -- I haven't ever credited my
> employer, only those that have specifically hired us for a particular
> patch. My employer gets a lot of "credit" in the form of email
> signatures, like the one below ;-)
>
> But I see your point and I will stick to whatever policy we come up with
> (assuming we come up with one).
>
> > This all becomes much easier if we keep the ads out of the commit
> > messages, and stick to the technical side there. And find another
> > venue for the other credit.
>
> I'm open to ideas.

Agreed. I am not firm either way on the issue; I was just throwing out
a suggestion.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 07/12/2011 06:54 AM, Alvaro Herrera wrote:
> Excerpts from Magnus Hagander's message of mar jul 12 09:34:56 -0400 2011:
>
>>> Agreed. On one level I like the sponsor message, but on the other
>>> having "Sponsored by RedHat" on every Tom Lane item will get tiring.
>>> ;-)

Create a macro ;)

>>>
>>> Can we add text if the employer is _not_ the feature sponsor?
>>
>> That would be quite unfair to those who *do* employ committers....
>> Basically you'd get credit only if you didn't employ a committer.
>
> Well, that has worked well for my case -- I haven't ever credited my
> employer, only those that have specifically hired us for a particular
> patch. My employer gets a lot of "credit" in the form of email
> signatures, like the one below ;-)

Yeah it depends on the committer. CMD gets credit through
@commandprompt.com, the sig file and a host of other areas but Tom uses
his personal information, so...

>
> But I see your point and I will stick to whatever policy we come up with
> (assuming we come up with one).
>
>> This all becomes much easier if we keep the ads out of the commit
>> messages, and stick to the technical side there. And find another
>> venue for the other credit.
>
> I'm open to ideas.

I think the commit log isn't actually useful for the "advertising"
portion of this. Users don't read commit logs for the most part.
However, it is an easy way for people who are writing release notes,
press releases, etc... to find the information.

Is it a good place for the information? No.

Is it the easiest place to store it until somebody steps up and creates
a proper way to track it so that it can be desimnated properly
throughout the community? Probably.

We do need a way to track this information.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579

On Jul 12, 2011, at 11:30 AM, Joshua D. Drake wrote:
>>> This all becomes much easier if we keep the ads out of the commit
>>> messages, and stick to the technical side there. And find another
>>> venue for the other credit.
>>
>> I'm open to ideas.
>
> I think the commit log isn't actually useful for the "advertising" portion of this. Users don't read commit logs for the most part. However, it is an easy way for people who are writing release notes, press releases, etc... to find the information.
>
> Is it a good place for the information? No.
>
> Is it the easiest place to store it until somebody steps up and creates a proper way to track it so that it can be desimnated properly throughout the community? Probably.
>
> We do need a way to track this information.
+1 on everything Josh said.

Does git allow for additional commit fields? That would allow for easy tracking without much additional burden on committers.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Sun, Jul 17, 2011 at 1:20 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> On Jul 12, 2011, at 11:30 AM, Joshua D. Drake wrote:
>>>> This all becomes much easier if we keep the ads out of the commit
>>>> messages, and stick to the technical side there. And find another
>>>> venue for the other credit.
>>>
>>> I'm open to ideas.
>>
>> I think the commit log isn't actually useful for the "advertising" portion of this. Users don't read commit logs for the most part. However, it is an easy way for people who are writing release notes, press releases, etc... to find the information.
>>
>> Is it a good place for the information? No.
>>
>> Is it the easiest place to store it until somebody steps up and creates a proper way to track it so that it can be desimnated properly throughout the community? Probably.
>>
>> We do need a way to track this information.
> +1 on everything Josh said.
>
> 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. 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...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 07/17/2011 08:36 PM, Robert Haas wrote:
>
>>> We do need a way to track this information.
>> +1 on everything Josh said.
>>
>> 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. 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...
>

My understanding of git notes is that they can be added after a commit
without changing the commit - indeed that's apparently a large part of
their raison d'être:

A typical use of notes is to supplement a commit message without
changing the commit itself. Notes can be shown by git log along with
the original commit message.

It is a pity that you can't define extra fields as is suggested above.

cheers

andrew

On Sun, Jul 17, 2011 at 8:44 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> My understanding of git notes is that they can be added after a commit
> without changing the commit - indeed that's apparently a large part of their
> raison d'être:
>
>   A typical use of notes is to supplement a commit message without
>   changing the commit itself. Notes can be shown by git log along with
>   the original commit message.

Right... but it's still append-only, and I think that there is little
reason to suppose that append-only is what we want or need here.

> It is a pity that you can't define extra fields as is suggested above.

Agreed. The 'git way' is apparently to add things like:

Reviewed-by: So And So <so(at)so(dot)com>

at the end of the commit message.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

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

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.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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.

Thanks :)

Thom

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

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

Excerpts from Dean Rasheed's message of vie nov 25 13:16:29 -0300 2011:

> 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.

Doh, thanks. I just pushed a patch that should close this bug. I
didn't do anything in particular to ensure that the constraint is dumped
after tables that use it, but pg_dump does it that way anyway -- I think
the reason is that separate constraints are always loaded at the end of
the dump, after all data has been loaded. (AFAIK FKs have always worked
like this, because it's much faster to load them after the data has been
imported.)

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Excerpts from Dean Rasheed's message of vie nov 25 13:45:34 -0300 2011:

> 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.

Hah ... interesting. Not sure it's worth fussing about this. If the
user shoots himself in the foot by declaring an unvalidated constraint,
which is not even documented, are we really at fault?

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Fri, Nov 25, 2011 at 4:28 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
>
> Excerpts from Dean Rasheed's message of vie nov 25 13:45:34 -0300 2011:
>
>> 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.
>
> Hah ... interesting.  Not sure it's worth fussing about this.  If the
> user shoots himself in the foot by declaring an unvalidated constraint,
> which is not even documented, are we really at fault?
>

i can't find anything about this in the standard, so i guess even if
the standard allows us to turn checks off. ours is not standard syntax
so, IMHO, it should be only in ALTER TABLE.

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación