From e1ccaff6eea1b48f4b9b28cabaf0f990119b0d19 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 15 Jun 2011 19:05:11 -0400 Subject: Rework parsing of ConstraintAttributeSpec to improve NOT VALID handling. The initial commit of the ALTER TABLE ADD FOREIGN KEY NOT VALID feature failed to support labeling such constraints as deferrable. The best fix for this seems to be to fold NOT VALID into ConstraintAttributeSpec. That's a bit more general than the documented syntax, but it allows better-targeted syntax error messages. In addition, do some mostly-but-not-entirely-cosmetic code review for the whole NOT VALID patch. --- doc/src/sgml/catalogs.sgml | 3 ++- doc/src/sgml/ref/alter_table.sgml | 48 +++++++++++++++++++++++---------------- 2 files changed, 30 insertions(+), 21 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 8504555bac9..24d7d98722a 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1898,7 +1898,8 @@ convalidated bool - Has the constraint been validated? Can only be false for foreign keys + Has the constraint been validated? + Currently, can only be false for foreign keys diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 4e02438483b..01d3105bf6c 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -42,9 +42,8 @@ ALTER TABLE name ALTER [ COLUMN ] column SET ( attribute_option = value [, ... ] ) ALTER [ COLUMN ] column RESET ( attribute_option [, ... ] ) ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } - ADD table_constraint - ADD table_constraint_using_index ADD table_constraint [ NOT VALID ] + ADD table_constraint_using_index VALIDATE CONSTRAINT constraint_name DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] DISABLE TRIGGER [ trigger_name | ALL | USER ] @@ -235,27 +234,21 @@ ALTER TABLE name - ADD table_constraint - [ NOT VALID ] + ADD table_constraint [ NOT VALID ] 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 - does not contain this option. - - - - - - 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. + , plus the option NOT + VALID, which is currently only allowed for foreign key + 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 + database will not assume that the constraint holds for all rows in + the table, until it is validated by using the VALIDATE + CONSTRAINT option. @@ -311,6 +304,21 @@ ALTER TABLE name + + VALIDATE CONSTRAINT + + + This form validates a foreign key 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. + 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. + + + + DROP CONSTRAINT [ IF EXISTS ] -- cgit v1.2.3