Skip to content

Commit eec0040

Browse files
peterepetere
committed
Add support for NOT ENFORCED in foreign key constraints
This expands the NOT ENFORCED constraint flag, previously only supported for CHECK constraints (commit ca87c41), to foreign key constraints. Normally, when a foreign key constraint is created on a table, action and check triggers are added to maintain data integrity. With this patch, if a constraint is marked as NOT ENFORCED, integrity checks are no longer required, making these triggers unnecessary. Consequently, when creating a NOT ENFORCED foreign key constraint, triggers will not be created, and the constraint will be marked as NOT VALID. Similarly, if an existing foreign key constraint is changed to NOT ENFORCED, the associated triggers will be dropped, and the constraint will also be marked as NOT VALID. Conversely, if a NOT ENFORCED foreign key constraint is changed to ENFORCED, the necessary triggers will be created, and the will be changed to VALID by performing necessary validation. Since not-enforced foreign key constraints have no triggers, the shortcut used for example in psql and pg_dump to skip looking for foreign keys if the relation is known not to have triggers no longer applies. (It already didn't work for partitioned tables.) Author: Amul Sul <sulamul@gmail.com> Reviewed-by: Joel Jacobson <joel@compiler.org> Reviewed-by: Andrew Dunstan <andrew@dunslane.net> Reviewed-by: Peter Eisentraut <peter@eisentraut.org> Reviewed-by: jian he <jian.universality@gmail.com> Reviewed-by: Alvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> Reviewed-by: Isaac Morland <isaac.morland@gmail.com> Reviewed-by: Alexandra Wang <alexandra.wang.oss@gmail.com> Tested-by: Triveni N <triveni.n@enterprisedb.com> Discussion: https://www.postgresql.org/message-id/flat/CAAJ_b962c5AcYW9KUt_R_ER5qs3fUGbe4az-SP-vuwPS-w-AGA@mail.gmail.com
1 parent 327d987 commit eec0040

File tree

19 files changed

+885
-274
lines changed

19 files changed

+885
-274
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2620,7 +2620,6 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
26202620
</para>
26212621
<para>
26222622
Is the constraint enforced?
2623-
Currently, can be false only for CHECK constraints
26242623
</para></entry>
26252624
</row>
26262625

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -58,7 +58,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
5858
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET COMPRESSION <replaceable class="parameter">compression_method</replaceable>
5959
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
6060
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
61-
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
61+
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]
6262
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ INHERIT | NO INHERIT ]
6363
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
6464
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
@@ -589,7 +589,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
589589
This form validates a foreign key or check constraint that was
590590
previously created as <literal>NOT VALID</literal>, by scanning the
591591
table to ensure there are no rows for which the constraint is not
592-
satisfied. Nothing happens if the constraint is already marked valid.
592+
satisfied. If the constraint is not enforced, an error is thrown.
593+
Nothing happens if the constraint is already marked valid.
593594
(See <xref linkend="sql-altertable-notes"/> below for an explanation
594595
of the usefulness of this command.)
595596
</para>

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1409,7 +1409,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
14091409
</para>
14101410

14111411
<para>
1412-
This is currently only supported for <literal>CHECK</literal>
1412+
This is currently only supported for foreign key and <literal>CHECK</literal>
14131413
constraints.
14141414
</para>
14151415
</listitem>

‎src/backend/catalog/pg_constraint.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -100,8 +100,9 @@ CreateConstraintEntry(const char *constraintName,
100100
ObjectAddresses *addrs_auto;
101101
ObjectAddresses *addrs_normal;
102102

103-
/* Only CHECK constraint can be not enforced */
104-
Assert(isEnforced || constraintType == CONSTRAINT_CHECK);
103+
/* Only CHECK or FOREIGN KEY constraint can be not enforced */
104+
Assert(isEnforced || constraintType == CONSTRAINT_CHECK ||
105+
constraintType == CONSTRAINT_FOREIGN);
105106
/* NOT ENFORCED constraint must be NOT VALID */
106107
Assert(isEnforced || !isValidated);
107108

‎src/backend/catalog/sql_features.txt

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -281,7 +281,7 @@ F461 Named character sets NO
281281
F471 Scalar subquery values YES
282282
F481 Expanded NULL predicate YES
283283
F491 Constraint management YES
284-
F492 Optional table constraint enforcement NO check constraints only
284+
F492 Optional table constraint enforcement YES except not-null constraints
285285
F501 Features and conformance views YES
286286
F501 Features and conformance views 01 SQL_FEATURES view YES
287287
F501 Features and conformance views 02 SQL_SIZING view YES

0 commit comments

Comments
 (0)