From ca87c415e2fccf81cec6fd45698dde9fae0ab570 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Sat, 11 Jan 2025 10:45:17 +0100 Subject: Add support for NOT ENFORCED in CHECK constraints This adds support for the NOT ENFORCED/ENFORCED flag for constraints, with support for check constraints. The plan is to eventually support this for foreign key constraints, where it is typically more useful. Note that CHECK constraints do not currently support ALTER operations, so changing the enforceability of an existing constraint isn't possible without dropping and recreating it. This could be added later. Author: Amul Sul Reviewed-by: Peter Eisentraut Reviewed-by: jian he Tested-by: Triveni N Discussion: https://www.postgresql.org/message-id/flat/CAAJ_b962c5AcYW9KUt_R_ER5qs3fUGbe4az-SP-vuwPS-w-AGA@mail.gmail.com --- doc/src/sgml/catalogs.sgml | 10 +++++++++ doc/src/sgml/information_schema.sgml | 4 +--- doc/src/sgml/ref/alter_table.sgml | 12 ++++++----- doc/src/sgml/ref/create_foreign_table.sgml | 6 ++++-- doc/src/sgml/ref/create_table.sgml | 34 ++++++++++++++++++++++++++++-- 5 files changed, 54 insertions(+), 12 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index cc6cf9bef09..238ed679190 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2591,6 +2591,16 @@ SCRAM-SHA-256$<iteration count>:&l + + + conenforced bool + + + Is the constraint enforced? + Currently, can be false only for CHECK constraints + + + convalidated bool diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 9442b0718c0..19dffe7be6a 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -6896,9 +6896,7 @@ ORDER BY c.ordinal_position; enforced yes_or_no - Applies to a feature not available in - PostgreSQL (currently always - YES) + YES if the constraint is enforced, NO if not diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index c8f7ab7d956..938450fba18 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -108,7 +108,7 @@ WITH ( MODULUS numeric_literal, REM PRIMARY KEY index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } -[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] +[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ] and table_constraint is: @@ -120,7 +120,7 @@ WITH ( MODULUS numeric_literal, REM EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD refcolumn ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } -[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] +[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ] and table_constraint_using_index is: @@ -1423,9 +1423,11 @@ WITH ( MODULUS numeric_literal, REM - Adding a CHECK or NOT NULL constraint requires - scanning the table to verify that existing rows meet the constraint, - but does not require a table rewrite. + Adding an enforced CHECK or NOT NULL + constraint requires scanning the table to verify that existing rows meet the + constraint, but does not require a table rewrite. If a CHECK + constraint is added as NOT ENFORCED, the validation will + not be performed. diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml index fc81ba3c498..0dcd9ca6f87 100644 --- a/doc/src/sgml/ref/create_foreign_table.sgml +++ b/doc/src/sgml/ref/create_foreign_table.sgml @@ -48,12 +48,14 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name CHECK ( expression ) [ NO INHERIT ] | DEFAULT default_expr | GENERATED ALWAYS AS ( generation_expr ) STORED } +[ ENFORCED | NOT ENFORCED ] and table_constraint is: [ CONSTRAINT constraint_name ] - NOT NULL column_name [ NO INHERIT ] | -CHECK ( expression ) [ NO INHERIT ] +{ NOT NULL column_name [ NO INHERIT ] | + CHECK ( expression ) [ NO INHERIT ] } +[ ENFORCED | NOT ENFORCED ] and partition_bound_spec is: diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 70fa929caa4..2237321cb4f 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -71,7 +71,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI PRIMARY KEY index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } -[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] +[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ] and table_constraint is: @@ -84,7 +84,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD refcolumn ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } -[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] +[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ] and like_option is: @@ -1377,6 +1377,36 @@ WITH ( MODULUS numeric_literal, REM + + ENFORCED + NOT ENFORCED + + + When the constraint is ENFORCED, then the database + system will ensure that the constraint is satisfied, by checking the + constraint at appropriate times (after each statement or at the end of + the transaction, as appropriate). That is the default. If the + constraint is NOT ENFORCED, the database system will + not check the constraint. It is then up to the application code to + ensure that the constraints are satisfied. The database system might + still assume that the data actually satisfies the constraint for + optimization decisions where this does not affect the correctness of the + result. + + + + NOT ENFORCED constraints can be useful as + documentation if the actual checking of the constraint at run time is + too expensive. + + + + This is currently only supported for CHECK + constraints. + + + + USING method -- cgit v1.2.3