Re: [HACKERS] creating CHECK constraints as NOT VALID
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 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
On Sat, Jun 18, 2011 at 2:57 AM, Jaime Casanova 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 NOT VALID. > ! NOT VALID is only accepted for CHECK > 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: ALTER DOMAIN conforms to the SQL standard, !except for the OWNER, SET SCHEMA and !VALIDATE CONSTRAINT variants, !as well as the NOT VALID clause of the ADD CONSTRAINT variant, which are PostgreSQL extensions. 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
On Thu, Jun 16, 2011 at 4:10 AM, Jaime Casanova wrote: > On Wed, Jun 15, 2011 at 7:08 PM, Alvaro Herrera > 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 NOT VALID. ! NOT VALID is only accepted for CHECK 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 + VALIDATE CONSTRAINT + + + This form validates a constraint previously added, that is, verify that + all data in columns using the domain satisfy the specified constraint. + + + 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
On Wed, Jun 15, 2011 at 7:08 PM, Alvaro Herrera 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
Alvaro Herrera 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
Excerpts from Tom Lane's message of mié jun 15 14:49:04 -0400 2011: > Alvaro Herrera 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 > >> 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 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
Alvaro Herrera 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 >> 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
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 > 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 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
On Wed, Jun 15, 2011 at 12:24 PM, Alvaro Herrera 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
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 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
On 15 June 2011 07:09, Jaime Casanova wrote: > On Tue, Jun 14, 2011 at 4:14 PM, Alvaro Herrera > 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
On Tue, Jun 14, 2011 at 4:14 PM, Alvaro Herrera 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
On Tue, Jun 14, 2011 at 4:41 PM, Jaime Casanova wrote: > On Tue, Jun 14, 2011 at 4:14 PM, Alvaro Herrera > 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
On Tue, Jun 14, 2011 at 4:14 PM, Alvaro Herrera 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
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 convalidated bool ! Has the constraint been validated? Can only be false for foreign keys --- 1898,1904 convalidated bool ! Has the constraint been validated? Can only be false for foreign keys and CHECK constraints *** a/doc/src/sgml/ref/alter_domain.sgml --- b/doc/src/sgml/ref/alter_domain.sgml *** *** 28,37 ALTER DOMAIN name ALTER DOMAIN name { SET | DROP } NOT NULL ALTER DOMAIN name ! ADD domain_constraint ALTER DOMAIN name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ] ALTER DOMAIN name OWNER TO new_owner ALTER DOMAIN name SET SCHEMA new_schema --- 28,39 ALTER DOMAIN name { SET | DROP } NOT NULL ALTER DOMAIN name ! ADD domain_constraint [ NOT VALID ] ALTER DOMAIN name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ] ALTER DOMAIN name + VALIDATE CONSTRAINT constraint_name + ALTER DOMAIN name OWNER TO new_owner ALTER DOMAIN name SET SCHEMA new_schema *** *** 70,82 ALTER DOMAIN name ! ADD domain_constraint This form adds a new constraint to a domain using the same syntax as . ! This will only succeed if all columns using the domain satisfy the ! new constraint. --- 72,88 ! ADD domain_constraint [ NOT VALID ] This form adds a new constraint to a domain using the same syntax as . ! 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 NOT VALID. ! NOT VALID is only accepted for CHECK constraints. *** *** 91,96 ALTER DOMAIN name --- 97,113 + VALIDATE CONSTRAINT + + + This form validates a constraint previously added, that is, verify that + all data in columns using the domain satisfy the specified constraint. + + + + + + OWNER *** *** 156,161 ALTER DOMAIN name --- 173,188 + NOT VALID + + + Do not verify existing column data for constraint validity. + + + + + + CASCADE *** *** 251,257 ALTER DOMAIN zipcode SET SCHEMA customers; ALTER DOMAIN conforms to the SQL standard, !except for the OWNER and SET SCHEMA variants, which are PostgreSQL extensions. --- 278,286 ALTER DOMAIN conforms to the SQL standard, !except for the OWNER, SET SCHEMA and !VALIDATE CONSTRAINT variants, !as well as the NOT VALID clause of the ADD CONSTRAINT variant, which are PostgreSQL extensions. *** a/doc/src/sgml/ref/alter_table.sgml --- b/doc/src/sgml/ref/alter_table.sgml *** *** 240,246 ALTER TABLE name This form adds a new constraint to a table using the same syntax as ! . Newly added foreign key constraints can also be defined as NOT VALID to avoid the potentially lengthy initial check that must otherwise be performed. Constraint checks are skipped at create table time, so --- 240,246 This form adds a new constraint to a table using the same syntax as ! . Newly added foreign key and CHECK constraints can also be defined as NOT VALID to avoid the potentially lengthy initial check that must otherwise be performed. Constraint checks are skipped at create table time, so *** *** 253,259 ALTER TABLE name VALIDATE CONSTRAINT ! This form validates a foreign key constraint that was previously created as NOT VALID. Constraints already marked valid do not cause an error response. --- 253,259 VALIDATE CONSTRAINT ! This form validates a foreign key or CHECK constraint that was previously created as NO
Re: [HACKERS] creating CHECK constraints as NOT VALID
On Mon, Jun 13, 2011 at 9:41 PM, Alvaro Herrera 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
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 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
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
> 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
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
Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011: > On 1 June 2011 23:47, Alvaro Herrera 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 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
On 11 June 2011 16:40, Dean Rasheed wrote: > On 11 June 2011 14:40, Thom Brown wrote: >> On 11 June 2011 14:32, Dean Rasheed wrote: >>> On 1 June 2011 23:47, Alvaro Herrera 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
On 11 June 2011 14:40, Thom Brown wrote: > On 11 June 2011 14:32, Dean Rasheed wrote: >> On 1 June 2011 23:47, Alvaro Herrera 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
On 11 June 2011 14:32, Dean Rasheed wrote: > On 1 June 2011 23:47, Alvaro Herrera 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
On 1 June 2011 23:47, Alvaro Herrera 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
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 > 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 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
On Thu, Jun 2, 2011 at 5:48 PM, Alvaro Herrera 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
Excerpts from Thom Brown's message of vie jun 03 13:45:57 -0400 2011: > On 3 June 2011 17:58, Alvaro Herrera 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 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
On 3 June 2011 17:58, Alvaro Herrera wrote: > Excerpts from Thom Brown's message of vie jun 03 12:47:58 -0400 2011: >> On 2 June 2011 17:48, Alvaro Herrera 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
Excerpts from Thom Brown's message of vie jun 03 12:47:58 -0400 2011: > On 2 June 2011 17:48, Alvaro Herrera 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 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
On 2 June 2011 17:48, Alvaro Herrera 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
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 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
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 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
On 1 June 2011 23:47, Alvaro Herrera 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
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 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
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 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
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 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
Excerpts from Thom Brown's message of mar may 31 20:18:18 -0400 2011: > On 31 May 2011 18:43, Alvaro Herrera 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 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
On Tue, May 31, 2011 at 7:03 PM, Greg Stark wrote: > On Tue, May 31, 2011 at 1:07 PM, Alvaro Herrera > 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
On 31 May 2011 18:43, Alvaro Herrera wrote: > > Here it is -- as a context patch this time, as well. > > -- > Álvaro Herrera > 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
On Tue, May 31, 2011 at 1:07 PM, Alvaro Herrera 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
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 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
On Tue, May 31, 2011 at 11:35:01AM -0500, Kevin Grittner wrote: > 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. > > 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
Here it is -- as a context patch this time, as well. -- Álvaro Herrera 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
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. 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
On Tue, May 31, 2011 at 12:04 PM, 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. 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
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 > 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 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
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 > > 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 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
On Tue, May 31, 2011 at 11:04 AM, Alvaro Herrera 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
[HACKERS] creating CHECK constraints as NOT VALID
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. -- Álvaro Herrera -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers