From 897795240cfaaed724af2f53ed2c50c9862f951f Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Wed, 1 Jun 2011 18:43:50 -0400 Subject: Enable CHECK constraints to be declared NOT VALID This means that they can initially be added to a large existing table without checking its initial contents, but new tuples must comply to them; a separate pass invoked by ALTER TABLE / VALIDATE can verify existing data and ensure it complies with the constraint, at which point it is marked validated and becomes a normal part of the table ecosystem. An non-validated CHECK constraint is ignored in the planner for constraint_exclusion purposes; when validated, cached plans are recomputed so that partitioning starts working right away. This patch also enables domains to have unvalidated CHECK constraints attached to them as well by way of ALTER DOMAIN / ADD CONSTRAINT / NOT VALID, which can later be validated with ALTER DOMAIN / VALIDATE CONSTRAINT. Thanks to Thom Brown, Dean Rasheed and Jaime Casanova for the various reviews, and Robert Hass for documentation wording improvement suggestions. This patch was sponsored by Enova Financial. --- doc/src/sgml/catalogs.sgml | 2 +- doc/src/sgml/ref/alter_domain.sgml | 45 ++++++++++++++++++++++++++++++++------ doc/src/sgml/ref/alter_table.sgml | 12 +++++----- 3 files changed, 46 insertions(+), 13 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 713ee25939e..d1e708165ac 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1899,7 +1899,7 @@ bool Has the constraint been validated? - Currently, can only be false for foreign keys + Currently, can only be false for foreign keys and CHECK constraints diff --git a/doc/src/sgml/ref/alter_domain.sgml b/doc/src/sgml/ref/alter_domain.sgml index 2a5a935edcd..cf504c3bcc9 100644 --- a/doc/src/sgml/ref/alter_domain.sgml +++ b/doc/src/sgml/ref/alter_domain.sgml @@ -28,9 +28,11 @@ ALTER DOMAIN name ALTER DOMAIN name { SET | DROP } NOT NULL ALTER DOMAIN name - ADD domain_constraint + 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 @@ -70,13 +72,19 @@ ALTER DOMAIN name - ADD domain_constraint + ADD domain_constraint [ NOT VALID ] 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. + 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. + NOT VALID is only accepted for CHECK constraints. @@ -90,6 +98,17 @@ ALTER DOMAIN name + + VALIDATE CONSTRAINT + + + This form validates a constraint previously added as + NOT VALID, that is, verify that all data in columns using the + domain satisfy the specified constraint. + + + + OWNER @@ -155,6 +174,16 @@ ALTER DOMAIN name + + NOT VALID + + + Do not verify existing column data for constraint validity. + + + + + CASCADE @@ -250,9 +279,11 @@ 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. + 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. diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 01d3105bf6c..4c2a4cd22ec 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -240,12 +240,14 @@ ALTER TABLE name This form adds a new constraint to a table using the same syntax as , plus the option NOT VALID, which is currently only allowed for foreign key - constraints. + and CHECK constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail - unless there is a matching row in the referenced table). But the + unless there is a matching row in the referenced table, in the case + of foreign keys; and they'll fail unless the new row matches the + specified check constraints). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option. @@ -308,10 +310,10 @@ ALTER TABLE name VALIDATE CONSTRAINT - This form validates a foreign key constraint that was previously created + This form validates a foreign key or check constraint that was previously created as NOT VALID, by scanning the table to ensure there - are no unmatched rows. Nothing happens if the constraint is - already marked valid. + are no rows for which the constraint is not satisfied. + Nothing happens if the constraint is already marked valid. The value of separating validation from initial creation of the constraint is that validation requires a lesser lock on the table than constraint creation does. -- cgit v1.2.3