Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-30 Thread Alvaro Herrera
Excerpts from Robert Haas's message of sáb jun 18 23:53:17 -0400 2011:

 I agree.  That's pretty contorted.  How about something like this:
 

Thanks Jaime and Robert.  I have pushed this patch with these fixes.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-18 Thread Jaime Casanova
On Thu, Jun 16, 2011 at 4:10 AM, Jaime Casanova ja...@2ndquadrant.com wrote:
 On Wed, Jun 15, 2011 at 7:08 PM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:

 Yeah, nothing serious.  Updated patch attached.  The wording in the doc
 changes could probably use some look over.


 looks good to me... at least it compiles, and function as i would expect...
 tomorrow i will read the code more carefully and look at the docs, but
 probably this is just fine to be commited...


I think that this paragraph is confusing, but not being an natural
english speaker i will let others give their opinion here:
!   If NOT VALID is not specified,
!   the command will only succeed if all columns using the
!   domain satisfy the new constraint.
!   The constraint is going to be enforced on new data inserted into columns
!   using the domain in all cases, regardless of literalNOT VALID/.
!   literalNOT VALID/ is only accepted for literalCHECK/
constraints.


Even if it is redundant maybe here should say This form validates a
constraint previously added as NOT VALID, otherwise someone could
think that by default constraints are not enforced and should be
validated
+ termVALIDATE CONSTRAINT/term
+ listitem
+  para
+   This form validates a constraint previously added, that is, verify that
+   all data in columns using the domain satisfy the specified constraint.
+  /para
+ /listitem
+/varlistentry


otherwise the patch looks good, and works fine in every test i could imagine...

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-18 Thread Robert Haas
On Sat, Jun 18, 2011 at 2:57 AM, Jaime Casanova ja...@2ndquadrant.com wrote:
 looks good to me... at least it compiles, and function as i would expect...
 tomorrow i will read the code more carefully and look at the docs, but
 probably this is just fine to be commited...

 I think that this paragraph is confusing, but not being an natural
 english speaker i will let others give their opinion here:
 !       If NOT VALID is not specified,
 !       the command will only succeed if all columns using the
 !       domain satisfy the new constraint.
 !       The constraint is going to be enforced on new data inserted into 
 columns
 !       using the domain in all cases, regardless of literalNOT VALID/.
 !       literalNOT VALID/ is only accepted for literalCHECK/
 constraints.

I agree.  That's pretty contorted.  How about something like this:

When a new constraint is added to a domain, all columns using that
domain will be checked against the newly added constraint.  These
checks can be suppressed by adding the new constraint using the NOT
VALID option; the constraint can later be made valid using ALTER
DOMAIN .. VALIDATE CONSTRAINT.  Newly inserted or updated rows are
always checked against all constraints, even those marked NOT VALID.

In lieu of:

 commandALTER DOMAIN/command conforms to the acronymSQL/acronym
 standard,
!except for the literalOWNER/, literalSET SCHEMA/ and
!literalVALIDATE CONSTRAINT/ variants,
!as well as the literalNOT VALID/ clause of the literalADD
CONSTRAINT/ variant,
 which are productnamePostgreSQL/productname extensions.
/para

I suggest: ALTER DOMAIN conforms to the SQL standard, except for the
OWNER, SET SCHEMA, and VALIDATE CONSTRAINT variants, which are
PostgreSQL extensions.  The NOT VALID clause of the ADD CONSTRAINT
variant is also a PostgreSQL extension.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-16 Thread Jaime Casanova
On Wed, Jun 15, 2011 at 7:08 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:

 Yeah, nothing serious.  Updated patch attached.  The wording in the doc
 changes could probably use some look over.


looks good to me... at least it compiles, and function as i would expect...
tomorrow i will read the code more carefully and look at the docs, but
probably this is just fine to be commited...

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-15 Thread Jaime Casanova
On Tue, Jun 14, 2011 at 4:14 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 Excerpts from Alvaro Herrera's message of lun jun 13 18:08:12 -0400 2011:
 Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011:

  I think that you also need to update the constraint exclusion code
  (get_relation_constraints() or nearby), otherwise the planner might
  exclude a relation on the basis of a CHECK constraint that is not
  currently VALID.

 Ouch, yeah, thanks for pointing that out.  Fortunately the patch to fix
 this is quite simple.  I don't have it handy right now but I'll post it
 soon.

 Here's the complete patch.


psql \h says (among other things) for ALTER TABLE

   ADD table_constraint
   ADD table_constraint_using_index
   ADD table_constraint [ NOT VALID ]


ADD table_constraint appears twice and isn't true that all
table_constraint accept the NOT VALID syntax... maybe we can accpet
the syntax and send an unimplemented feature message for the other
table_constraints?

attached, is a script with the examples i have tried:

EXAMPLE 1:
constraint_exclusion when using NOT VALID check constraints... and it
works well, except when the constraint has been validated, it keeps
ignoring it (which means i won't benefit from constraint_exclusion)
until i execute ANALYZE on the table or close connection

EXAMPLE 2:
if i have a DOMAIN with a NOT VALID check constraint, and i use it as
the new type of a column it checks the constraint

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
/* example 1 */
DROP TABLE IF EXISTS padre CASCADE;

create table padre(i serial primary key, d date); 
create table hija_2010 () inherits (padre);
create table hija_2011 () inherits (padre);
insert into hija_2010(d) values ('2011-08-15'::date);
insert into hija_2011(d) values ('2011-09-15'::date);
alter table hija_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
alter table hija_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
explain analyze select * from padre where d between '2011-08-01'::date and '2011-08-31'::date;

create table hija_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (padre);
insert into hija_2009(d) values ('2009-06-13');

explain analyze select * from padre where d between '2011-08-01'::date and '2011-08-31'::date;
explain analyze select * from padre where d between '2009-08-01'::date and '2009-08-31'::date;

alter table hija_2011 VALIDATE CONSTRAINT hija_2011_d_check;

explain analyze select * from padre where d between '2009-08-01'::date and '2009-08-31'::date;


/* example 2 */
create domain mes as int;
create table t_mes (m mes);
insert into t_mes values(13);
alter domain mes add check (value between 1 and 12) not valid;

create table t_mes2 (m int);
insert into t_mes2 values(13);
alter table t_mes2 ALTER  m type mes;
ERROR:  value for domain mes violates check constraint mes_check


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-15 Thread Dean Rasheed
On 15 June 2011 07:09, Jaime Casanova ja...@2ndquadrant.com wrote:
 On Tue, Jun 14, 2011 at 4:14 PM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:
 Excerpts from Alvaro Herrera's message of lun jun 13 18:08:12 -0400 2011:
 Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011:

  I think that you also need to update the constraint exclusion code
  (get_relation_constraints() or nearby), otherwise the planner might
  exclude a relation on the basis of a CHECK constraint that is not
  currently VALID.

 Ouch, yeah, thanks for pointing that out.  Fortunately the patch to fix
 this is quite simple.  I don't have it handy right now but I'll post it
 soon.

 Here's the complete patch.


 psql \h says (among other things) for ALTER TABLE
 
   ADD table_constraint
   ADD table_constraint_using_index
   ADD table_constraint [ NOT VALID ]
 

 ADD table_constraint appears twice and isn't true that all
 table_constraint accept the NOT VALID syntax... maybe we can accpet
 the syntax and send an unimplemented feature message for the other
 table_constraints?


Yeah, I was just about to make the same observation about the 9.1beta
docs. The 3rd line makes the 1st one redundant.

Regards,
Dean

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-15 Thread Alvaro Herrera
Excerpts from Jaime Casanova's message of mié jun 15 02:09:15 -0400 2011:

 psql \h says (among other things) for ALTER TABLE
 
ADD table_constraint
ADD table_constraint_using_index
ADD table_constraint [ NOT VALID ]
 
 
 ADD table_constraint appears twice and isn't true that all
 table_constraint accept the NOT VALID syntax... maybe we can accpet
 the syntax and send an unimplemented feature message for the other
 table_constraints?

Okay, I removed the redundant line from the synposis.  As far as other
types of constraints go, I don't feel we need to do anything here -- the
description already says that it only works on FKs and CHECK.

I'm not going to go to the trouble of fixing the redundant
table_constraint line in the synopsis in HEAD -- if someone else wants
to send a patch to fix that, I can apply it easily enough.

 EXAMPLE 1:
 constraint_exclusion when using NOT VALID check constraints... and it
 works well, except when the constraint has been validated, it keeps
 ignoring it (which means i won't benefit from constraint_exclusion)
 until i execute ANALYZE on the table or close connection

Hmm, I think this means we need to send a sinval message to invalidate
cached plans when a constraint is validated.  I'll see about this.

 EXAMPLE 2:
 if i have a DOMAIN with a NOT VALID check constraint, and i use it as
 the new type of a column it checks the constraint

I think this is OK.  The NOT VALID declaration says that the existing
columns declared using this constraint is not checked, but new columns
(as well as new data in existing columns) are certainly going to require
their values to pass the checks.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-15 Thread Robert Haas
On Wed, Jun 15, 2011 at 12:24 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 Hmm, I think this means we need to send a sinval message to invalidate
 cached plans when a constraint is validated.  I'll see about this.

I feel like that really ought to be happening automatically, as a
result of committing the transaction that did the system catalog
modification.  It seems pretty strange if it isn't.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-15 Thread Alvaro Herrera
Excerpts from Robert Haas's message of mié jun 15 12:53:59 -0400 2011:
 On Wed, Jun 15, 2011 at 12:24 PM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:
  Hmm, I think this means we need to send a sinval message to invalidate
  cached plans when a constraint is validated.  I'll see about this.
 
 I feel like that really ought to be happening automatically, as a
 result of committing the transaction that did the system catalog
 modification.  It seems pretty strange if it isn't.

The catalog change takes place in pg_constraint, so I'm not sure that
it'd cause the sort of event we need.  I'm testing whether adding a call
to CacheInvalidateRelcache in the appropriate place works.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-15 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes:
 Excerpts from Robert Haas's message of mié jun 15 12:53:59 -0400 2011:
 On Wed, Jun 15, 2011 at 12:24 PM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:
 Hmm, I think this means we need to send a sinval message to invalidate
 cached plans when a constraint is validated.  I'll see about this.

 I feel like that really ought to be happening automatically, as a
 result of committing the transaction that did the system catalog
 modification.  It seems pretty strange if it isn't.

 The catalog change takes place in pg_constraint, so I'm not sure that
 it'd cause the sort of event we need.  I'm testing whether adding a call
 to CacheInvalidateRelcache in the appropriate place works.

Currently, only updates in pg_class, pg_attribute, and pg_index cause
automatic relcache invalidations --- see the logic in
PrepareForTupleInvalidation.  If you want to force replanning after an
update elsewhere, you need to call CacheInvalidateRelcache.  I've
occasionally thought about extending the number of cases that get
handled automatically by PrepareForTupleInvalidation, but not gotten off
my duff to change it.  I doubt that we want to make that routine know
about *every* possible case, so it's a matter of tradeoffs ...

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-15 Thread Alvaro Herrera
Excerpts from Tom Lane's message of mié jun 15 14:49:04 -0400 2011:
 Alvaro Herrera alvhe...@commandprompt.com writes:
  Excerpts from Robert Haas's message of mié jun 15 12:53:59 -0400 2011:
  On Wed, Jun 15, 2011 at 12:24 PM, Alvaro Herrera
  alvhe...@commandprompt.com wrote:
  Hmm, I think this means we need to send a sinval message to invalidate
  cached plans when a constraint is validated.  I'll see about this.
 
  I feel like that really ought to be happening automatically, as a
  result of committing the transaction that did the system catalog
  modification.  It seems pretty strange if it isn't.
 
  The catalog change takes place in pg_constraint, so I'm not sure that
  it'd cause the sort of event we need.  I'm testing whether adding a call
  to CacheInvalidateRelcache in the appropriate place works.
 
 Currently, only updates in pg_class, pg_attribute, and pg_index cause
 automatic relcache invalidations --- see the logic in
 PrepareForTupleInvalidation.  If you want to force replanning after an
 update elsewhere, you need to call CacheInvalidateRelcache.  I've
 occasionally thought about extending the number of cases that get
 handled automatically by PrepareForTupleInvalidation, but not gotten off
 my duff to change it.  I doubt that we want to make that routine know
 about *every* possible case, so it's a matter of tradeoffs ...

I think pg_trigger is closer to needing a new case in
PrepareForTupleInvalidation than pg_constraint, at this point --
triggers seem to be involved rather more with CacheInvalidateRelcache
(and close relatives) calls than constraints.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-15 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes:
 Here's an updated patch fixing all of the above.  I stole your first
 test case and added it to regression, after some editorialization.

I've probably created some merge conflicts for you in process of fixing
the FOREIGN KEY NOT VALID patch, but in any case you need to change this
to use ConstraintAttributeSpec rather than a duplicate production.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-14 Thread Alvaro Herrera
Excerpts from Alvaro Herrera's message of lun jun 13 18:08:12 -0400 2011:
 Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011:

  I think that you also need to update the constraint exclusion code
  (get_relation_constraints() or nearby), otherwise the planner might
  exclude a relation on the basis of a CHECK constraint that is not
  currently VALID.
 
 Ouch, yeah, thanks for pointing that out.  Fortunately the patch to fix
 this is quite simple.  I don't have it handy right now but I'll post it
 soon.

Here's the complete patch.


*** a/doc/src/sgml/catalogs.sgml
--- b/doc/src/sgml/catalogs.sgml
***
*** 1898,1904 
entrystructfieldconvalidated/structfield/entry
entrytypebool/type/entry
entry/entry
!   entryHas the constraint been validated? Can only be false for foreign 
keys/entry
   /row
  
   row
--- 1898,1904 
entrystructfieldconvalidated/structfield/entry
entrytypebool/type/entry
entry/entry
!   entryHas the constraint been validated? Can only be false for foreign 
keys and CHECK constraints/entry
   /row
  
   row
*** a/doc/src/sgml/ref/alter_domain.sgml
--- b/doc/src/sgml/ref/alter_domain.sgml
***
*** 28,37  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  { SET | DROP } NOT NULL
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
! ADD replaceable class=PARAMETERdomain_constraint/replaceable
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  DROP CONSTRAINT replaceable 
class=PARAMETERconstraint_name/replaceable [ RESTRICT | CASCADE ]
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  OWNER TO replaceable class=PARAMETERnew_owner/replaceable
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  SET SCHEMA replaceable class=PARAMETERnew_schema/replaceable
--- 28,39 
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  { SET | DROP } NOT NULL
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
! ADD replaceable class=PARAMETERdomain_constraint/replaceable [ NOT 
VALID ]
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  DROP CONSTRAINT replaceable 
class=PARAMETERconstraint_name/replaceable [ RESTRICT | CASCADE ]
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
+ VALIDATE CONSTRAINT replaceable 
class=PARAMETERconstraint_name/replaceable
+ ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  OWNER TO replaceable class=PARAMETERnew_owner/replaceable
  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
  SET SCHEMA replaceable class=PARAMETERnew_schema/replaceable
***
*** 70,82  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
 /varlistentry
  
 varlistentry
! termADD replaceable 
class=PARAMETERdomain_constraint/replaceable/term
  listitem
   para
This form adds a new constraint to a domain using the same syntax as
xref linkend=SQL-CREATEDOMAIN.
!   This will only succeed if all columns using the domain satisfy the
!   new constraint.
   /para
  /listitem
 /varlistentry
--- 72,88 
 /varlistentry
  
 varlistentry
! termADD replaceable class=PARAMETERdomain_constraint/replaceable 
[ NOT VALID ]/term
  listitem
   para
This form adds a new constraint to a domain using the same syntax as
xref linkend=SQL-CREATEDOMAIN.
!   If NOT VALID is not specified,
! the command will only succeed if all columns using the
! domain satisfy the new constraint.
! The constraint is going to be enforced on new data inserted into 
columns
! using the domain in all cases, regardless of literalNOT VALID/.
! literalNOT VALID/ is only accepted for literalCHECK/ 
constraints.
   /para
  /listitem
 /varlistentry
***
*** 91,96  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
--- 97,113 
 /varlistentry
  
 varlistentry
+ termVALIDATE CONSTRAINT/term
+ listitem
+  para
+   This form validates a constraint previously added, that is, verify that
+   all data in columns using the domain satisfy the specified constraint.
+  /para
+ /listitem
+/varlistentry
+ 
+ 
+varlistentry
  termOWNER/term
  listitem
   para
***
*** 156,161  ALTER DOMAIN replaceable class=PARAMETERname/replaceable
--- 173,188 
   /varlistentry
  
   varlistentry
+   termreplaceable class=PARAMETERNOT VALID/replaceable/term
+   listitem
+para
+ Do not verify existing column data for constraint validity.
+/para
+   /listitem
+  /varlistentry
+ 
+ 
+  varlistentry
termliteralCASCADE/literal/term
listitem
 para
***
*** 251,257  ALTER DOMAIN zipcode SET SCHEMA customers;
para
 

Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-14 Thread Jaime Casanova
On Tue, Jun 14, 2011 at 4:14 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 Excerpts from Alvaro Herrera's message of lun jun 13 18:08:12 -0400 2011:
 Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011:

  I think that you also need to update the constraint exclusion code
  (get_relation_constraints() or nearby), otherwise the planner might
  exclude a relation on the basis of a CHECK constraint that is not
  currently VALID.

 Ouch, yeah, thanks for pointing that out.  Fortunately the patch to fix
 this is quite simple.  I don't have it handy right now but I'll post it
 soon.

 Here's the complete patch.


this doesn't apply

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-14 Thread Jaime Casanova
On Tue, Jun 14, 2011 at 4:41 PM, Jaime Casanova ja...@2ndquadrant.com wrote:
 On Tue, Jun 14, 2011 at 4:14 PM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:
 Excerpts from Alvaro Herrera's message of lun jun 13 18:08:12 -0400 2011:
 Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011:

  I think that you also need to update the constraint exclusion code
  (get_relation_constraints() or nearby), otherwise the planner might
  exclude a relation on the basis of a CHECK constraint that is not
  currently VALID.

 Ouch, yeah, thanks for pointing that out.  Fortunately the patch to fix
 this is quite simple.  I don't have it handy right now but I'll post it
 soon.

 Here's the complete patch.


 this doesn't apply


oops! sorry for the noise... this was gmail problem...
the patch was expanded as text, instead of an attachments and when i
copy/paste it it should have moved something... copy'ing from archives
instead was good

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-13 Thread Alvaro Herrera
Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011:
 On 1 June 2011 23:47, Alvaro Herrera alvhe...@commandprompt.com wrote:
 
  Here's a complete patch with all this stuff, plus doc additions and
  simple regression tests for the new ALTER DOMAIN commands.
 
     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.
 
 
 I think that you also need to update the constraint exclusion code
 (get_relation_constraints() or nearby), otherwise the planner might
 exclude a relation on the basis of a CHECK constraint that is not
 currently VALID.

Ouch, yeah, thanks for pointing that out.  Fortunately the patch to fix
this is quite simple.  I don't have it handy right now but I'll post it
soon.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-13 Thread Josh Berkus
Alvaro,  Dean,

 I think that you also need to update the constraint exclusion code
  (get_relation_constraints() or nearby), otherwise the planner might
  exclude a relation on the basis of a CHECK constraint that is not
  currently VALID.
 Ouch, yeah, thanks for pointing that out.  Fortunately the patch to fix
 this is quite simple.  I don't have it handy right now but I'll post it
 soon.

Hmmm. Is this the behavior we want with NOT VALID constraints though?

I know that if I'm pouring 100m rows into a new partition as part of a
repartitioning scheme, I don't want to *ever* check them if I know
they're correct because of how I created the table (CREATE TABLE AS ...).

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-13 Thread Josh Berkus

 I can see why you would want that, but I'd say that's a separate feature
 you need to explicitly request when creating the constraint.  Consider
 what happens in the old data is garbage, but I want the new data to be
 validated use case if we allow constraint exclusion on NOT VALID
 constraints.

Yeah, I guess what I'm suggesting is that we should have an ALTER TABLE
... VALID DONT CHECK option.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-13 Thread Marko Tiikkaja

On 14/06/2011 01:11, Josh Berkus wrote:

Hmmm. Is this the behavior we want with NOT VALID constraints though?

I know that if I'm pouring 100m rows into a new partition as part of a
repartitioning scheme, I don't want to *ever* check them if I know
they're correct because of how I created the table (CREATE TABLE AS ...).


I can see why you would want that, but I'd say that's a separate feature 
you need to explicitly request when creating the constraint.  Consider 
what happens in the old data is garbage, but I want the new data to be 
validated use case if we allow constraint exclusion on NOT VALID 
constraints.



--
Marko Tiikkajahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-13 Thread Alvaro Herrera
Excerpts from Josh Berkus's message of lun jun 13 18:11:54 -0400 2011:
 Alvaro,  Dean,
 
  I think that you also need to update the constraint exclusion code
   (get_relation_constraints() or nearby), otherwise the planner might
   exclude a relation on the basis of a CHECK constraint that is not
   currently VALID.
  Ouch, yeah, thanks for pointing that out.  Fortunately the patch to fix
  this is quite simple.  I don't have it handy right now but I'll post it
  soon.
 
 Hmmm. Is this the behavior we want with NOT VALID constraints though?
 
 I know that if I'm pouring 100m rows into a new partition as part of a
 repartitioning scheme, I don't want to *ever* check them if I know
 they're correct because of how I created the table (CREATE TABLE AS ...).

Well, if we don't validate the data, it's an open door for potentially
corrupt query results.  I'm not really sure that we want to provide
support for I don't ever want to check this data for validity because
of that.  But then, I just work here.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-13 Thread Robert Haas
On Mon, Jun 13, 2011 at 9:41 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 Excerpts from Josh Berkus's message of lun jun 13 18:11:54 -0400 2011:
 Alvaro,  Dean,

  I think that you also need to update the constraint exclusion code
   (get_relation_constraints() or nearby), otherwise the planner might
   exclude a relation on the basis of a CHECK constraint that is not
   currently VALID.
  Ouch, yeah, thanks for pointing that out.  Fortunately the patch to fix
  this is quite simple.  I don't have it handy right now but I'll post it
  soon.

 Hmmm. Is this the behavior we want with NOT VALID constraints though?

 I know that if I'm pouring 100m rows into a new partition as part of a
 repartitioning scheme, I don't want to *ever* check them if I know
 they're correct because of how I created the table (CREATE TABLE AS ...).

 Well, if we don't validate the data, it's an open door for potentially
 corrupt query results.  I'm not really sure that we want to provide
 support for I don't ever want to check this data for validity because
 of that.  But then, I just work here.

At any rate, we can't very well have two different meanings for NOT
VALID, so the 9.2 meaning vis-a-vis CHECK constraints had better match
the 9.1 behavior vis-a-vis FOREIGN KEYs.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-11 Thread Dean Rasheed
On 1 June 2011 23:47, Alvaro Herrera alvhe...@commandprompt.com wrote:

 Here's a complete patch with all this stuff, plus doc additions and
 simple regression tests for the new ALTER DOMAIN commands.

    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.


I think that you also need to update the constraint exclusion code
(get_relation_constraints() or nearby), otherwise the planner might
exclude a relation on the basis of a CHECK constraint that is not
currently VALID.

Regards,
Dean

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-11 Thread Thom Brown
On 11 June 2011 14:32, Dean Rasheed dean.a.rash...@gmail.com wrote:
 On 1 June 2011 23:47, Alvaro Herrera alvhe...@commandprompt.com wrote:

 Here's a complete patch with all this stuff, plus doc additions and
 simple regression tests for the new ALTER DOMAIN commands.

    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.


 I think that you also need to update the constraint exclusion code
 (get_relation_constraints() or nearby), otherwise the planner might
 exclude a relation on the basis of a CHECK constraint that is not
 currently VALID.

Do the standards explicitly stipulate an expected behaviour for this?
And does such a problem affect the invalid foreign key change too?

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

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-11 Thread Dean Rasheed
On 11 June 2011 14:40, Thom Brown t...@linux.com wrote:
 On 11 June 2011 14:32, Dean Rasheed dean.a.rash...@gmail.com wrote:
 On 1 June 2011 23:47, Alvaro Herrera alvhe...@commandprompt.com wrote:

 Here's a complete patch with all this stuff, plus doc additions and
 simple regression tests for the new ALTER DOMAIN commands.

    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.


 I think that you also need to update the constraint exclusion code
 (get_relation_constraints() or nearby), otherwise the planner might
 exclude a relation on the basis of a CHECK constraint that is not
 currently VALID.

 Do the standards explicitly stipulate an expected behaviour for this?

No I believe that this is a PostgreSQL-specific optimisation, and we
need to ensure that queries return the correct results with
constraint_exclusion on.

 And does such a problem affect the invalid foreign key change too?

No only CHECK constraints (and possibly NOT NULL constraints in the future).

Regards,
Dean

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-11 Thread Dean Rasheed
On 11 June 2011 16:40, Dean Rasheed dean.a.rash...@gmail.com wrote:
 On 11 June 2011 14:40, Thom Brown t...@linux.com wrote:
 On 11 June 2011 14:32, Dean Rasheed dean.a.rash...@gmail.com wrote:
 On 1 June 2011 23:47, Alvaro Herrera alvhe...@commandprompt.com wrote:

 Here's a complete patch with all this stuff, plus doc additions and
 simple regression tests for the new ALTER DOMAIN commands.

    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.


 I think that you also need to update the constraint exclusion code
 (get_relation_constraints() or nearby), otherwise the planner might
 exclude a relation on the basis of a CHECK constraint that is not
 currently VALID.

 Do the standards explicitly stipulate an expected behaviour for this?

 No I believe that this is a PostgreSQL-specific optimisation, and we
 need to ensure that queries return the correct results with
 constraint_exclusion on.

 And does such a problem affect the invalid foreign key change too?

 No only CHECK constraints (and possibly NOT NULL constraints in the future).

 Regards,
 Dean


Since you've mentioned the SQL spec, its worth noting that whilst I think
that this feature will be very useful, it's not the feature in the SQL
spec (at least not in my version).

The SQL spec feature is to mark a constraint as NOT ENFORCED, which
means that no data (existing or new) is checked against the
constraint. It's as if the constraint were not present at all. In
Oracle this corresponds to the syntax

  ALTER TABLE mytable ENABLE/DISABLE myconstraint

which is actually quite handy during a bulk load/update - disable all
your constraints, do the bulk operation and then re-enable them
(automatically re-validating them). This is better than dropping and
re-creating the constraints because you don't need to remember all the
constraint definitions.

I can see both features being quite useful in different situations.

Regards,
Dean

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-04 Thread Simon Riggs
On Thu, Jun 2, 2011 at 5:48 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 Excerpts from Alvaro Herrera's message of mié jun 01 20:56:12 -0400 2011:
 Excerpts from Thom Brown's message of mié jun 01 19:48:44 -0400 2011:

  Is this expected?
  [ pg_dump fails to preserve not-valid status of constraints ]

 Certainly not.

  Shouldn't the constraint be dumped as not valid too??

 Sure, I'll implement that tomorrow.

 Actually, it turns out that NOT VALID foreign keys were already buggy
 here, and fixing them automatically fixes this case as well, because the
 fix involves touching pg_get_constraintdef to dump the flag.  This also
 gets it into psql's \d.  Patch attached.

 (Maybe the changes in psql's describe.c should be reverted, not sure.)

Thanks. As soon as Thom said that, I thought ahh... didn't do that.

Patch looks fine. Will you commit this patch to 9.1 now, or would you
like me to?

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-04 Thread Alvaro Herrera
Excerpts from Simon Riggs's message of sáb jun 04 09:11:52 -0400 2011:
 On Thu, Jun 2, 2011 at 5:48 PM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:

  Actually, it turns out that NOT VALID foreign keys were already buggy
  here, and fixing them automatically fixes this case as well, because the
  fix involves touching pg_get_constraintdef to dump the flag.  This also
  gets it into psql's \d.  Patch attached.
 
  (Maybe the changes in psql's describe.c should be reverted, not sure.)
 
 Thanks. As soon as Thom said that, I thought ahh... didn't do that.
 
 Patch looks fine. Will you commit this patch to 9.1 now, or would you
 like me to?

Thanks for the review.  I already committed it on 9.1:
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=048417511aef8d5fb2d541b17b73afc730935cd5

I'd still like your opinion on the psql bits.  Should they be reverted?
I haven't verified what the output currently looks like.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-03 Thread Thom Brown
On 2 June 2011 17:48, Alvaro Herrera alvhe...@commandprompt.com wrote:
 Excerpts from Alvaro Herrera's message of mié jun 01 20:56:12 -0400 2011:
 Excerpts from Thom Brown's message of mié jun 01 19:48:44 -0400 2011:

  Is this expected?
  [ pg_dump fails to preserve not-valid status of constraints ]

 Certainly not.

  Shouldn't the constraint be dumped as not valid too??

 Sure, I'll implement that tomorrow.

 Actually, it turns out that NOT VALID foreign keys were already buggy
 here, and fixing them automatically fixes this case as well, because the
 fix involves touching pg_get_constraintdef to dump the flag.  This also
 gets it into psql's \d.  Patch attached.

 (Maybe the changes in psql's describe.c should be reverted, not sure.)

Nice work Alvaro :)  Shouldn't patches be sent to -hackers instead of
the obsolete -patches list?  Plus I'm a bit confused as to why the
patch looks like an email instead of a patch.

According to the SQL:2011 standard: The SQL Standard allows you to
turn the checking on and off for CHECK constraints, UNIQUE constraints
and FOREIGN KEYS.

So is it much work to also add the ADD CONSTRAINT UNIQUE (column, ...)
NOT VALID syntax to this too?  This would mean we're completely
covered for this standards requirement.

Cheers

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

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-03 Thread Alvaro Herrera
Excerpts from Thom Brown's message of vie jun 03 12:47:58 -0400 2011:
 On 2 June 2011 17:48, Alvaro Herrera alvhe...@commandprompt.com wrote:

  Actually, it turns out that NOT VALID foreign keys were already buggy
  here, and fixing them automatically fixes this case as well, because the
  fix involves touching pg_get_constraintdef to dump the flag.  This also
  gets it into psql's \d.  Patch attached.
 
  (Maybe the changes in psql's describe.c should be reverted, not sure.)
 
 Nice work Alvaro :)  Shouldn't patches be sent to -hackers instead of
 the obsolete -patches list?  Plus I'm a bit confused as to why the
 patch looks like an email instead of a patch.

Did I really email pgsql-patches?  If so, I didn't notice -- but I don't
see it (and the archives seem to agree with me, there's no email after
2008-10).

The patch looks like an email because that's what git format-patch
produced, and I attached it instead of putting it inline.

 According to the SQL:2011 standard: The SQL Standard allows you to
 turn the checking on and off for CHECK constraints, UNIQUE constraints
 and FOREIGN KEYS.
 
 So is it much work to also add the ADD CONSTRAINT UNIQUE (column, ...)
 NOT VALID syntax to this too?  This would mean we're completely
 covered for this standards requirement.

Yeah, UNIQUE is a completely different beast.  There's already some work
on making them accept invalid (duplicate) values temporarily, but making
that more general, even if it was acceptable to the community at large
(which I'm not sure it is) is way beyond what I set to do here :-)

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-03 Thread Thom Brown
On 3 June 2011 17:58, Alvaro Herrera alvhe...@commandprompt.com wrote:
 Excerpts from Thom Brown's message of vie jun 03 12:47:58 -0400 2011:
 On 2 June 2011 17:48, Alvaro Herrera alvhe...@commandprompt.com wrote:

  Actually, it turns out that NOT VALID foreign keys were already buggy
  here, and fixing them automatically fixes this case as well, because the
  fix involves touching pg_get_constraintdef to dump the flag.  This also
  gets it into psql's \d.  Patch attached.
 
  (Maybe the changes in psql's describe.c should be reverted, not sure.)

 Nice work Alvaro :)  Shouldn't patches be sent to -hackers instead of
 the obsolete -patches list?  Plus I'm a bit confused as to why the
 patch looks like an email instead of a patch.

 Did I really email pgsql-patches?  If so, I didn't notice -- but I don't
 see it (and the archives seem to agree with me, there's no email after
 2008-10).

My bad, I was reading your patch which contained an email subject
beginning with [PATCH] (similar to mailing list subject prefixes)
which, if I had given it any further though, doesn't mean it's on the
-patches list.

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

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-03 Thread Alvaro Herrera
Excerpts from Thom Brown's message of vie jun 03 13:45:57 -0400 2011:
 On 3 June 2011 17:58, Alvaro Herrera alvhe...@commandprompt.com wrote:
  Excerpts from Thom Brown's message of vie jun 03 12:47:58 -0400 2011:

  Nice work Alvaro :)  Shouldn't patches be sent to -hackers instead of
  the obsolete -patches list?  Plus I'm a bit confused as to why the
  patch looks like an email instead of a patch.
 
  Did I really email pgsql-patches?  If so, I didn't notice -- but I don't
  see it (and the archives seem to agree with me, there's no email after
  2008-10).
 
 My bad, I was reading your patch which contained an email subject
 beginning with [PATCH] (similar to mailing list subject prefixes)
 which, if I had given it any further though, doesn't mean it's on the
 -patches list.

Ah, that makes sense.  The pgsql-patches tag was [PATCHES] actually,
though :-)

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-02 Thread Alvaro Herrera
Excerpts from Alvaro Herrera's message of mié jun 01 20:56:12 -0400 2011:
 Excerpts from Thom Brown's message of mié jun 01 19:48:44 -0400 2011:
 
  Is this expected?
  [ pg_dump fails to preserve not-valid status of constraints ]
 
 Certainly not.
 
  Shouldn't the constraint be dumped as not valid too??
 
 Sure, I'll implement that tomorrow.

Actually, it turns out that NOT VALID foreign keys were already buggy
here, and fixing them automatically fixes this case as well, because the
fix involves touching pg_get_constraintdef to dump the flag.  This also
gets it into psql's \d.  Patch attached.

(Maybe the changes in psql's describe.c should be reverted, not sure.)

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


0001-Fix-pg_get_constraintdef-to-cope-with-NOT-VALID-cons.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-01 Thread Alvaro Herrera
Excerpts from Thom Brown's message of mar may 31 20:18:18 -0400 2011:

 test=# CREATE DOMAIN things AS INT CHECK (VALUE  5);
 CREATE DOMAIN
 test=# CREATE TABLE abc (id SERIAL, stuff things);
 NOTICE:  CREATE TABLE will create implicit sequence abc_id_seq for
 serial column abc.id
 CREATE TABLE
 test=# INSERT INTO abc (stuff) VALUES (55);
 INSERT 0 1
 test=# ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE  11) NOT VALID;
 ERROR:  column stuff of table abc contains values that violate the
 new constraint
 STATEMENT:  ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE  11)
 NOT VALID;

Okay, fixed that and added ALTER DOMAIN VALIDATE CONSTRAINT too.
Thanks for the review.

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


0001-Make-NOT-VALID-constraints-work-on-domains-too.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-01 Thread Thom Brown
On 1 June 2011 23:47, Alvaro Herrera alvhe...@commandprompt.com wrote:

 Here's a complete patch with all this stuff, plus doc additions and
 simple regression tests for the new ALTER DOMAIN commands.

    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.

    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.

Is this expected?

postgres=# CREATE TABLE a (num INT);
CREATE TABLE
postgres=# INSERT INTO a (num) VALUES (90);
INSERT 0 1
postgres=# ALTER TABLE a ADD CONSTRAINT meow CHECK (num  20) NOT VALID;
ALTER TABLE
postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# \q
postgresql thom$ pg_dump -f /tmp/test.sql postgres
postgresql thom$ psql test  /tmp/test.sql
SET
SET
SET
SET
SET
COMMENT
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

Shouldn't the constraint be dumped as not valid too??

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

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-01 Thread Alvaro Herrera
Excerpts from Thom Brown's message of mié jun 01 19:48:44 -0400 2011:

 Is this expected?
 [ pg_dump fails to preserve not-valid status of constraints ]

Certainly not.

 Shouldn't the constraint be dumped as not valid too??

Sure, I'll implement that tomorrow.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-05-31 Thread Jaime Casanova
On Tue, May 31, 2011 at 11:04 AM, Alvaro Herrera
alvhe...@alvh.no-ip.org wrote:
 This patch allows you to initially declare a CHECK constraint as NOT
 VALID

seems you forgot to add the patch itself

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-05-31 Thread Alvaro Herrera
Excerpts from Alvaro Herrera's message of mar may 31 12:39:48 -0400 2011:
 Excerpts from Jaime Casanova's message of mar may 31 12:24:09 -0400 2011:
  On Tue, May 31, 2011 at 11:04 AM, Alvaro Herrera
  alvhe...@alvh.no-ip.org wrote:
   This patch allows you to initially declare a CHECK constraint as NOT
   VALID
  
  seems you forgot to add the patch itself
 
 oops ... another bug in my email client, it seems.

Hmm, found an inconsistency in the way recursion is handled -- other
commands have a AT_DoFooRecurse case.  Weird.  I'll change this to be
like that, though I don't readily see why we do it that way.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-05-31 Thread Alvaro Herrera
Excerpts from Jaime Casanova's message of mar may 31 12:24:09 -0400 2011:
 On Tue, May 31, 2011 at 11:04 AM, Alvaro Herrera
 alvhe...@alvh.no-ip.org wrote:
  This patch allows you to initially declare a CHECK constraint as NOT
  VALID
 
 seems you forgot to add the patch itself

oops ... another bug in my email client, it seems.

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


0001-Enable-CHECK-constraints-to-be-declared-NOT-VALID.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-05-31 Thread Robert Haas
On Tue, May 31, 2011 at 12:04 PM, Alvaro Herrera
alvhe...@alvh.no-ip.org wrote:
 This patch allows you to initially declare a CHECK constraint as NOT
 VALID, similar to what we already allow for foreign keys.  That is, you
 create the constraint without scanning the table and after it is
 committed, it is enforced for new rows; later, all rows are checked by
 running ALTER TABLE VALIDATE CONSTRAINT, which doesn't need
 AccessExclusive thus allowing for better concurrency.

 The trickiest bit here was realizing that unlike FKs, check constraints
 do inherit, and so needed special treatment for recursion.  Other than
 that I think this was pretty straightforward.

 I intend to attempt to apply this to NOT NULL constraints as well, once
 the patch to add them to pg_constraint is in.

Seems like a logical extension of what we have now.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-05-31 Thread Kevin Grittner
Alvaro Herrera alvhe...@alvh.no-ip.org wrote:
 
 This patch allows you to initially declare a CHECK constraint as
 NOT VALID, similar to what we already allow for foreign keys. 
 That is, you create the constraint without scanning the table and
 after it is committed, it is enforced for new rows; later, all
 rows are checked by running ALTER TABLE VALIDATE CONSTRAINT, which
 doesn't need AccessExclusive thus allowing for better concurrency.
 
I think it's a valuable feature, not just in terms of timing and
concurrency, but in terms of someone starting with less-than-perfect
data who wants to prevent further degradation while cleaning up the
existing problems.  This feature is present in other databases I've
used.
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-05-31 Thread Alvaro Herrera

Here it is -- as a context patch this time, as well.

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


0001-Enable-CHECK-constraints-to-be-declared-NOT-VALID.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-05-31 Thread Ross J. Reedstrom
On Tue, May 31, 2011 at 11:35:01AM -0500, Kevin Grittner wrote:
 Alvaro Herrera alvhe...@alvh.no-ip.org wrote:
  
  This patch allows you to initially declare a CHECK constraint as
  NOT VALID, similar to what we already allow for foreign keys. 
  That is, you create the constraint without scanning the table and
  after it is committed, it is enforced for new rows; later, all
  rows are checked by running ALTER TABLE VALIDATE CONSTRAINT, which
  doesn't need AccessExclusive thus allowing for better concurrency.
  
 I think it's a valuable feature, not just in terms of timing and
 concurrency, but in terms of someone starting with less-than-perfect
 data who wants to prevent further degradation while cleaning up the
 existing problems.  This feature is present in other databases I've
 used.

Yup, the ER triage approach to data integrity: Stop the major bleeding,
we'll go back and make it a pretty scar later

Follows from one of the practical maxims of databases: The data is
always dirty Being able to have the constraints enforced at least for
new data allows you to at least fence the bad data, and have a shot at
fixing it all. Right now, you may be forced into running with
constraints effectively 'off', depending on the app to get new data
right, while attempting to catch up. And the app probably put the bad
data in there in the first place. One of the thankless, important but
seemingly never urgent tasks.

Ross
-- 
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer  Admin, Research Scientistphone: 713-348-6166
Connexions  http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-05-31 Thread Alvaro Herrera
Excerpts from Ross J. Reedstrom's message of mar may 31 14:02:04 -0400 2011:

 Follows from one of the practical maxims of databases: The data is
 always dirty Being able to have the constraints enforced at least for
 new data allows you to at least fence the bad data, and have a shot at
 fixing it all. Right now, you may be forced into running with
 constraints effectively 'off', depending on the app to get new data
 right, while attempting to catch up. And the app probably put the bad
 data in there in the first place. One of the thankless, important but
 seemingly never urgent tasks.

Interesting point of view.  I have to admit that I didn't realize I was
allowing that, even though I have wished for it in the past myself.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-05-31 Thread Greg Stark
On Tue, May 31, 2011 at 1:07 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 Excerpts from Ross J. Reedstrom's message of mar may 31 14:02:04 -0400 2011:

 Follows from one of the practical maxims of databases: The data is
 always dirty Being able to have the constraints enforced at least for
 new data allows you to at least fence the bad data, and have a shot at
 fixing it all.

 Interesting point of view.  I have to admit that I didn't realize I was
 allowing that, even though I have wished for it in the past myself.

What happens when there's bad data that the new transaction touches in
some minor way? For example updating some other column of the row or
just locking the row? What about things like cluster or table
rewrites?

Also I think NOT NULL might be used in the join elimination patch.
Make sure it understands the valid flag and doesn't drop joins that
aren't needed. It would be nice to have this for unique constraints as
well which would *definitely* need to have the planner understand
whether they're valid or not.

-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-05-31 Thread Thom Brown
On 31 May 2011 18:43, Alvaro Herrera alvhe...@commandprompt.com wrote:

 Here it is -- as a context patch this time, as well.

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

There is this scenario:

test=# CREATE DOMAIN things AS INT CHECK (VALUE  5);
CREATE DOMAIN
test=# CREATE TABLE abc (id SERIAL, stuff things);
NOTICE:  CREATE TABLE will create implicit sequence abc_id_seq for
serial column abc.id
CREATE TABLE
test=# INSERT INTO abc (stuff) VALUES (55);
INSERT 0 1
test=# ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE  11) NOT VALID;
ERROR:  column stuff of table abc contains values that violate the
new constraint
STATEMENT:  ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE  11)
NOT VALID;

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

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-05-31 Thread Robert Haas
On Tue, May 31, 2011 at 7:03 PM, Greg Stark gsst...@mit.edu wrote:
 On Tue, May 31, 2011 at 1:07 PM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:
 Excerpts from Ross J. Reedstrom's message of mar may 31 14:02:04 -0400 2011:

 Follows from one of the practical maxims of databases: The data is
 always dirty Being able to have the constraints enforced at least for
 new data allows you to at least fence the bad data, and have a shot at
 fixing it all.

 Interesting point of view.  I have to admit that I didn't realize I was
 allowing that, even though I have wished for it in the past myself.

 What happens when there's bad data that the new transaction touches in
 some minor way? For example updating some other column of the row or
 just locking the row?

Updating some other column should fail unless the constraint is
satisfied for the resulting row, I think.  The rule should be simple
and easy to understand: old row (versions) aren't checked, but new
ones must satisfy all constraints, whether validated or not.

There's no question that this feature has a certain amount of foot-gun
potential.  But it's also really useful.  And there are plenty of
people who know how to use a gun safely, without shooting themselves
in the foot.  We shouldn't aim for the lowest common denominator.

 What about things like cluster or table
 rewrites?

 Also I think NOT NULL might be used in the join elimination patch.
 Make sure it understands the valid flag and doesn't drop joins that
 aren't needed. It would be nice to have this for unique constraints as
 well which would *definitely* need to have the planner understand
 whether they're valid or not.

Yeah.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-05-31 Thread Alvaro Herrera
Excerpts from Thom Brown's message of mar may 31 20:18:18 -0400 2011:
 On 31 May 2011 18:43, Alvaro Herrera alvhe...@commandprompt.com wrote:
 
  Here it is -- as a context patch this time, as well.

 There is this scenario:
 
 test=# CREATE DOMAIN things AS INT CHECK (VALUE  5);
 CREATE DOMAIN
 test=# CREATE TABLE abc (id SERIAL, stuff things);
 NOTICE:  CREATE TABLE will create implicit sequence abc_id_seq for
 serial column abc.id
 CREATE TABLE
 test=# INSERT INTO abc (stuff) VALUES (55);
 INSERT 0 1
 test=# ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE  11) NOT VALID;
 ERROR:  column stuff of table abc contains values that violate the
 new constraint
 STATEMENT:  ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE  11)
 NOT VALID;

Oooh, I hadn't realized that I was opening the door for domains and
check constraints therein.  I'll have a look at this.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-05-31 Thread David Fetter
On Tue, May 31, 2011 at 12:04:07PM -0400, Alvaro Herrera wrote:
 This patch allows you to initially declare a CHECK constraint as NOT
 VALID, similar to what we already allow for foreign keys.  That is, you
 create the constraint without scanning the table and after it is
 committed, it is enforced for new rows; later, all rows are checked by
 running ALTER TABLE VALIDATE CONSTRAINT, which doesn't need
 AccessExclusive thus allowing for better concurrency.
 
 The trickiest bit here was realizing that unlike FKs, check constraints
 do inherit, and so needed special treatment for recursion.  Other than
 that I think this was pretty straightforward.
 
 I intend to attempt to apply this to NOT NULL constraints as well, once
 the patch to add them to pg_constraint is in.
 
 Thoughts?
 
 This patch courtesy of Enova Financial.

Great stuff!

A colleague brought up an interesting idea that I think is worth
exploring for all NOT VALID constraints, to wit, is there some way
(via SQL) to find which rows violate which constraints?  I'm picturing
some kind of function that could be aggregated into some structure for
each violating row...

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-05-31 Thread Alvaro Herrera
Excerpts from David Fetter's message of mar may 31 21:42:08 -0400 2011:

 A colleague brought up an interesting idea that I think is worth
 exploring for all NOT VALID constraints, to wit, is there some way
 (via SQL) to find which rows violate which constraints?  I'm picturing
 some kind of function that could be aggregated into some structure for
 each violating row...

Seems like a job for a plpgsql function with a bunch of exception handlers ...
Some details like the violated constraint name would be hard to extract,
probably, though.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers