Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL 11 preview - 支持 数组外键约束

6 minute read

背景

通常，数据库的外键约束是一种一行对一行的约束，然而，在某些场景中，可能要求一行匹配多行的约束。例如：

1、表1：商品表，商品ID为主键

2、表2：用户购买的商品列表，商品ID数组

要求表2数组内的每一个元素都在商品表ID字段中存在，同时要求在删除或更新商品表的商品ID时，可以影响到外键。

PostgreSQL本身支持一类外键（兼容SQL标准），同时支持数组类型，为了实现这个功能，同时性能足够的好，可以结合GIN倒排索引。

patch如下

https://commitfest.postgresql.org/17/1252/

数组外键约束语法

FOREIGN KEY (EACH ELEMENT OF column_name) REFERENCES table_name

例如

+     CREATE TABLE drivers (  
+         driver_id integer PRIMARY KEY,  
+         first_name text,  
+         last_name text  
+     );  
+  
+     CREATE TABLE races (  
+         race_id integer PRIMARY KEY,  
+         title text,  
+         race_day date,  
+         final_positions integer[],  
+         FOREIGN KEY <emphasis> (EACH ELEMENT OF final_positions) REFERENCES drivers </emphasis>  
+     );

ACTION目前仅支持NO ACTION或RESTRICT（理论上是可以支持其他action的。未来版本迭代，相信会支持其他action）

NO ACTION，如果有任何元素不在主表中，则报错，支持延迟CHECK，如事务结束时CHECK。

RESTRICT，同NO ACTION，但是不支持延迟CHECK。

+-- Allowed references with actions (NO ACTION, RESTRICT)  
+CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE NO ACTION, ftest2 int );  
+DROP TABLE FKTABLEFORARRAY;  
+CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE RESTRICT, ftest2 int );  
+DROP TABLE FKTABLEFORARRAY;  
+CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE NO ACTION, ftest2 int );  
+DROP TABLE FKTABLEFORARRAY;  
+CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE RESTRICT ON UPDATE RESTRICT, ftest2 int );  
+DROP TABLE FKTABLEFORARRAY;  
+-- Not allowed references (SET NULL, SET DEFAULT, CASCADE)  
+CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE SET DEFAULT, ftest2 int );  
+ERROR:  Array Element Foreign Keys support only NO ACTION and RESTRICT actions  
+DROP TABLE IF EXISTS FKTABLEFORARRAY;  
+NOTICE:  table "fktableforarray" does not exist, skipping  
+CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE NO ACTION ON UPDATE SET NULL, ftest2 int );  
+ERROR:  Array Element Foreign Keys support only NO ACTION and RESTRICT actions  
+DROP TABLE IF EXISTS FKTABLEFORARRAY;  
+NOTICE:  table "fktableforarray" does not exist, skipping  
+CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY ON DELETE SET NULL ON UPDATE CASCADE, ftest2 int );  
+ERROR:  Array Element Foreign Keys support only NO ACTION and RESTRICT actions

性能相关

由于主表UPDATE或DELETE时，需要CHECK数组表内的元素是否都能匹配到主表的正在被变更的值，所以为了加速，需要高效的进行“是否满足约束条件”判断，使得主表的DML操作性能影响较小。

对于普通外键，我们使用普通索引就可以，而对于数组外键，可以用GIN索引，加速数组包含、相交的判断。

例如

1、更新需要满足

array_column @> array[主表正在变更的OLD.VALUE] == false

2、删除需要满足

array_column @> array[主表正在删除的old.VALUE] == false

3、延迟判定，可以使用批量操作，只判定一次

array_column && array[主表正在变更或删除的old.VALUEs] == false

描述

+     <para>  
+      It is advisable to index the refrencing column using GIN index as it   
+      considerably enhances the performance. Also concerning coercion while using the   
+      GIN index:  
+          
+      <programlisting>  
+       CREATE TABLE pktableforarray ( ptest1 int2 PRIMARY KEY, ptest2 text );  
+       CREATE TABLE fktableforarray ( ftest1 int4[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int );  
+      </programlisting>  
+      This syntax is fine since it will cast ptest1 to int4 upon RI checks,          
+  
+      <programlisting>  
+       CREATE TABLE pktableforarray ( ptest1 int4 PRIMARY KEY, ptest2 text );  
+       CREATE TABLE fktableforarray ( ftest1 int2[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int );          
+      </programlisting>  
+       however, this syntax will cast ftest1 to int4 upon RI checks, thus defeating the  
+       purpose of the index.  
+     </para>

元数据

1、获取约束定义

List of functions  
   Schema   |              Name              | Result data type | Argument data types | Type   
------------+--------------------------------+------------------+---------------------+------  
 pg_catalog | pg_get_constraintdef           | text             | oid                 | func  
 pg_catalog | pg_get_constraintdef           | text             | oid, boolean        | func  
 pg_catalog | pg_get_partition_constraintdef | text             | oid                 | func  
(3 rows)

2、约束定义元数据表

               Table "pg_catalog.pg_constraint"  
    Column     |     Type     | Collation | Nullable | Default   
---------------+--------------+-----------+----------+---------  
 conname       | name         |           | not null |   
 connamespace  | oid          |           | not null |   
 contype       | "char"       |           | not null |   
 condeferrable | boolean      |           | not null |   
 condeferred   | boolean      |           | not null |   
 convalidated  | boolean      |           | not null |   
 conrelid      | oid          |           | not null |   
 contypid      | oid          |           | not null |   
 conindid      | oid          |           | not null |   
 confrelid     | oid          |           | not null |   
 confupdtype   | "char"       |           | not null |   
 confdeltype   | "char"       |           | not null |   
 confmatchtype | "char"       |           | not null |   
 conislocal    | boolean      |           | not null |   
 coninhcount   | integer      |           | not null |   
 connoinherit  | boolean      |           | not null |   
 conkey        | smallint[]   |           |          |   
 confkey       | smallint[]   |           |          |   
 conpfeqop     | oid[]        |           |          |   
 conppeqop     | oid[]        |           |          |   
 conffeqop     | oid[]        |           |          |   
 conexclop     | oid[]        |           |          |   
 conbin        | pg_node_tree |           |          |   
 consrc        | text         |           |          |

3、约束类型解释(p, 普通外键约束。e, 数组元素外键约束)

pg_constraint.confreftype

+      <entry><structfield>confreftype</structfield></entry>  
+      <entry><type>char[]</type></entry>  
+      <entry></entry>  
+      <entry>If a foreign key, the reference semantics for each column:  
+       <literal>p</literal> = plain (simple equality),  
+       <literal>e</literal> = each element of referencing array must have a match  
+      </entry>  
+     </row>

例子

+     CREATE TABLE available_moves (  
+         kind text,  
+         move text,  
+         description text,  
+         PRIMARY KEY (kind, move)  
+     );  
+  
+     CREATE TABLE paths (  
+         description text,  
+         kind text,  
+         moves text[],  
+         <emphasis>FOREIGN KEY (kind, EACH ELEMENT OF moves) REFERENCES available_moves (kind, move)</emphasis>  
+     );  
+  
+     INSERT INTO available_moves VALUES ('relative', 'LN', 'look north');  
+     INSERT INTO available_moves VALUES ('relative', 'RL', 'rotate left');  
+     INSERT INTO available_moves VALUES ('relative', 'RR', 'rotate right');  
+     INSERT INTO available_moves VALUES ('relative', 'MF', 'move forward');  
+     INSERT INTO available_moves VALUES ('absolute', 'N', 'move north');  
+     INSERT INTO available_moves VALUES ('absolute', 'S', 'move south');  
+     INSERT INTO available_moves VALUES ('absolute', 'E', 'move east');  
+     INSERT INTO available_moves VALUES ('absolute', 'W', 'move west');  
+  
+     INSERT INTO paths VALUES ('L-shaped path', 'relative', '{LN, RL, MF, RR, MF, MF}');  
+     INSERT INTO paths VALUES ('L-shaped path', 'absolute', '{W, N, N}');  


+-- EACH-ELEMENT FK CONSTRAINTS  
+CREATE TABLE PKTABLEFORARRAY ( ptest1 int PRIMARY KEY, ptest2 text );  
+-- Insert test data into PKTABLEFORARRAY  
+INSERT INTO PKTABLEFORARRAY VALUES (1, 'Test1');  
+INSERT INTO PKTABLEFORARRAY VALUES (2, 'Test2');  
+INSERT INTO PKTABLEFORARRAY VALUES (3, 'Test3');  
+INSERT INTO PKTABLEFORARRAY VALUES (4, 'Test4');  
+INSERT INTO PKTABLEFORARRAY VALUES (5, 'Test5');  
  
+INSERT INTO FKTABLEFORARRAYMDIM VALUES ('{{1,2},{6,NULL}}', 20);  
+ERROR:  insert or update on table "fktableforarraymdim" violates foreign key constraint "fktableforarraymdim_ftest1_fkey"  
+DETAIL:  Key (EACH ELEMENT OF ftest1)=({{1,2},{6,NULL}}) is not present in table "pktableforarray".  
+INSERT INTO FKTABLEFORARRAYNOTNULL VALUES (NULL, 21);  
+ERROR:  null value in column "ftest1" violates not-null constraint  
+DETAIL:  Failing row contains (null, 21).  
+-- Check FKTABLE  
+SELECT * FROM FKTABLEFORARRAY;  
+  ftest1  | ftest2   
+----------+--------  
+ {1}      |      3  
+ {2}      |      4  
+ {1}      |      5  
+ {3}      |      6  
+ {1}      |      7  
+ {4,5}    |      8  
+ {4,4}    |      9  
+          |     10  
+ {}       |     11  
+ {1,NULL} |     12  
+ {NULL}   |     13  
+(11 rows)  
+  
+-- Delete a row from PK TABLE (must fail due to ON DELETE NO ACTION)  
+DELETE FROM PKTABLEFORARRAY WHERE ptest1=1;  
+ERROR:  update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" on table "fktableforarray"  
+DETAIL:  Key (EACH ELEMENT OF ptest1)=(1) is still referenced from table "fktableforarray".  
+-- Check FKTABLE for removal of matched row  
+SELECT * FROM FKTABLEFORARRAY;  
+  ftest1  | ftest2   
+----------+--------  
+ {1}      |      3  
+ {2}      |      4  
+ {1}      |      5  
+ {3}      |      6  
+ {1}      |      7  
+ {4,5}    |      8  
+ {4,4}    |      9  
+          |     10  
+ {}       |     11  
+ {1,NULL} |     12  
+ {NULL}   |     13  
+(11 rows)  
+  
+-- Update a row from PK TABLE (must fail due to ON UPDATE NO ACTION)  
+UPDATE PKTABLEFORARRAY SET ptest1=7 WHERE ptest1=1;  
+ERROR:  update or delete on table "pktableforarray" violates foreign key constraint "fktableforarray_ftest1_fkey" on table "fktableforarray"  
+DETAIL:  Key (EACH ELEMENT OF ptest1)=(1) is still referenced from table "fktableforarray".

小结

PostgreSQL支持丰富的约束功能，包括：

  • 主键

  • 唯一键

  • NOT NULL

  • check

  • 外键

  • 排他

  • 数组外键

数组外键使用GIN索引，可以实现高效率的是否满足约束判定，使得业务系统又多了一重数据强约束的保障。

另一方面，主外键的锁粒度也进行了细化，早在2013年的版本中，就支持了4种行锁粒度，降低主外键数据操作的锁冲突。原理参考末尾文档。

参考

《会议室预定系统实践(解放开发) - PostgreSQL tsrange(时间范围类型) + 排他约束》

https://commitfest.postgresql.org/17/1252/

https://www.postgresql.org/docs/10/static/sql-createtable.html

《PostgreSQL add 2 DML LOCK TUPLE MODE to 4》

《PostgreSQL performance speedup by avoid lock references tuple when add or update(new) Foreign Key’s value》

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...