pgsql: Enable CHECK constraints to be declared NOT VALID

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@commandprompt.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@gmx.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@gmx.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@gmx.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@momjian.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@momjian.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@momjian.us> wrote:

Simon Riggs wrote:

On Sun, Jul 3, 2011 at 7:51 PM, Peter Eisentraut <peter_e@gmx.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@commandprompt.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@momjian.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@nasby.net
512.569.9461 (cell) http://jim.nasby.net

On Sun, Jul 17, 2011 at 1:20 PM, Jim Nasby <jim@nasby.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@dunslane.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@so.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@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 18 July 2011 02:46, Alvaro Herrera <alvherre@commandprompt.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@linux.com> wrote:

On 18 July 2011 02:46, Alvaro Herrera <alvherre@commandprompt.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@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.

--
Álvaro Herrera <alvherre@commandprompt.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@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.

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

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.

Thanks :)

Thom

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

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

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