Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commiteec0040

Browse files
committed
Add support for NOT ENFORCED in foreign key constraints
This expands the NOT ENFORCED constraint flag, previously onlysupported for CHECK constraints (commitca87c41), to foreign keyconstraints.Normally, when a foreign key constraint is created on a table, actionand check triggers are added to maintain data integrity. With thispatch, if a constraint is marked as NOT ENFORCED, integrity checks areno longer required, making these triggers unnecessary. Consequently,when creating a NOT ENFORCED foreign key constraint, triggers will notbe created, and the constraint will be marked as NOT VALID.Similarly, if an existing foreign key constraint is changed to NOTENFORCED, the associated triggers will be dropped, and the constraintwill also be marked as NOT VALID. Conversely, if a NOT ENFORCEDforeign key constraint is changed to ENFORCED, the necessary triggerswill be created, and the will be changed to VALID by performingnecessary validation.Since not-enforced foreign key constraints have no triggers, theshortcut used for example in psql and pg_dump to skip looking forforeign keys if the relation is known not to have triggers no longerapplies. (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 parent327d987 commiteec0040

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 forforeign 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 @@ F461Named character setsNO
281281
F471Scalar subquery valuesYES
282282
F481Expanded NULL predicateYES
283283
F491Constraint managementYES
284-
F492Optional table constraint enforcementNOcheck constraints only
284+
F492Optional table constraint enforcementYESexcept not-null constraints
285285
F501Features and conformance viewsYES
286286
F501Features and conformance views01SQL_FEATURES viewYES
287287
F501Features and conformance views02SQL_SIZING viewYES

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp