PostgreSQL 18 preview - 妥协了? check和foreign key 约束要引入假设为真(NOT ENFORCED)了?

PostgreSQL 18 preview - 妥协了? check和foreign key 约束要引入假设为真(NOT ENFORCED)了?

作者

digoal

日期

2025-01-15

标签

PostgreSQL , PolarDB , DuckDB , check , foreign key , constraint , NOT ENFORCED , ENFORCED

背景

妥协了? check和foreign key 约束要引入假设为真(NOT ENFORCED)了?

为了性能, 例如

• 大量导入数据时, 明知数据约束一定为真的情况下, 数据库不做检测(可以减少cpu消耗/IO消耗, 特别是foreign key的检测). 导入完成后再改成需要检测.
• 假设应用可以保证约束为真, 把责任推给应用.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ca87c415e2fccf81cec6fd45698dde9fae0ab570

Add support for NOT ENFORCED in CHECK constraints  
author  Peter Eisentraut <peter@eisentraut.org>   
Sat, 11 Jan 2025 09:45:17 +0000 (10:45 +0100)  
committer   Peter Eisentraut <peter@eisentraut.org>   
Sat, 11 Jan 2025 09:52:30 +0000 (10:52 +0100)  
commit  ca87c415e2fccf81cec6fd45698dde9fae0ab570  
tree    f9e1f5fc7637f0baf91566f4d8a333ddb60960b1    tree  
parent  72ceb21b029433dd82f29182894dce63e639b4d4    commit | diff  
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 <amul.sul@enterprisedb.com>  
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>  
Reviewed-by: jian he <jian.universality@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  

+   <varlistentry id="sql-createtable-parms-enforced">  
+    <term><literal>ENFORCED</literal></term>  
+    <term><literal>NOT ENFORCED</literal></term>  
+    <listitem>  
+     <para>  
+      When the constraint is <literal>ENFORCED</literal>, 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 <literal>NOT ENFORCED</literal>, 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.  
+     </para>  
+  
+     <para>  
+      <literal>NOT ENFORCED</literal> constraints can be useful as  
+      documentation if the actual checking of the constraint at run time is  
+      too expensive.  
+     </para>  
+  
+     <para>  
+      This is currently only supported for <literal>CHECK</literal>  
+      constraints.  
+     </para>  
+    </listitem>  
+   </varlistentry>  

期望 PostgreSQL|开源PolarDB 增加什么功能?

PolarDB 开源数据库

PolarDB 学习图谱

PostgreSQL 解决方案集合

德哥 / digoal’s Github - 公益是一辈子的事.

About 德哥