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

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

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

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

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

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:

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

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

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.

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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;

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

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

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

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

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

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

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

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

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