Digoal.zhou

Digoal.zhou

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

PostgreSQL 多值类型（如数组），元素值全局唯一 - 约束如何实现

4 minute read

背景

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

在PostgreSQL中，应用可以非常方便使用多值类型，比如数组、全文检索、范围类型等。

对于多值类型的操作也越来越方便，比如查询相交、包含可以使用索引，更新、替换、APPEND的操作也都有对应的UDF和OP来支撑。

可能应用会有这样的需求，对于多值类型，能否做到全局唯一约束呢?

比如数组内的元素，要求整张表唯一。

对于单值类型，很容易做到，加个unique, primary key约束就可以，使用b-tree很方便判断值是否已存在。但是对于多值类型，怎么判断呢？

1、采用排他约束

PostgreSQL的排他约束可以支持空间、范围、普通数据的排他，采用gist索引，使用能支持左右互换操作数并且返回boolean值不变的操作符。

例如：

1、会议室预定系统，EXCLUDE约束，确保一个会议室在某个时间点不会被多人预定。

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

2、空间EXCLUDE约束，比如国土面积，不会出现相交。

https://www.postgresql.org/docs/10/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

对于数组类型有一个操作符为&&，可以支持操作数互换，结果不变，代表的含义是两个array是否overlap。

但是目前GIN还不支持EXCLUDE约束，相信后面会支持。

postgres=# \set VERBOSITY verbose  
postgres=# create table t_unique_arr (id int, arr int[], exclude using gin (arr with &&));   
ERROR:  0A000: access method "gin" does not support exclusion constraints  
LOCATION:  DefineIndex, indexcmds.c:580

所以对于int类型，我们可以加一个intarray插件，让gist接口来支持int[]。

postgres=# create table t_unique_arr (id int, arr int[], exclude using gist (arr with &&));   
ERROR:  data type integer[] has no default operator class for access method "gist"  
HINT:  You must specify an operator class for the index or define a default operator class for the data type.  
  
postgres=# create extension intarray;  
CREATE EXTENSION

创建数组排他约束

postgres=# create table t_unique_arr (id int, arr int[], exclude using gist (arr with &&));   
CREATE TABLE

现在，这个约束可以保证数组内的元素，在整个表的所有行中，全局唯一。

postgres=# insert into t_unique_arr values (1,array[1,2,3]);  
INSERT 0 1  
postgres=# insert into t_unique_arr values (1,array[1,2,3]);  
ERROR:  conflicting key value violates exclusion constraint "t_unique_arr_arr_excl"  
DETAIL:  Key (arr)=({1,2,3}) conflicts with existing key (arr)=({1,2,3}).  
postgres=# insert into t_unique_arr values (1,array[1,4,5]);  
ERROR:  conflicting key value violates exclusion constraint "t_unique_arr_arr_excl"  
DETAIL:  Key (arr)=({1,4,5}) conflicts with existing key (arr)=({1,2,3}).  
postgres=# insert into t_unique_arr values (1,array[4,5,6]);  
INSERT 0 1  
  
postgres=# insert into t_unique_arr select id, array[id] from generate_series(7,10000) t(id);  
ERROR:  conflicting key value violates exclusion constraint "t_unique_arr_arr_excl"  
DETAIL:  Key (arr)=({7}) conflicts with existing key (arr)=({7}).  
Time: 3.131 ms  
postgres=# update t_unique_arr set arr=array[1,2,3] where id=2;  
UPDATE 0  
Time: 1.405 ms  
postgres=# update t_unique_arr set arr=array[1,2,3] where id=7;  
ERROR:  conflicting key value violates exclusion constraint "t_unique_arr_arr_excl"  
DETAIL:  Key (arr)=({1,2,3}) conflicts with existing key (arr)=({1,2,3}).  
Time: 2.126 ms

目前使用intarray，exclude约束来实现数组内元素全局唯一，性能不怎么样。

postgres=# insert into t_unique_arr select id, array[id] from generate_series(7,10000) t(id);  
INSERT 0 9994  
Time: 122861.074 ms (02:02.861)

所以我们可以用另外的方法，比如rule, trigger。

2、采用rule

原理是这样的，当数据写入一个包含数组的表时，如果你想让某个数组列全局唯一，那么可以把数据展开，放到一个单值列，并用B-TREE的unique, primary key这类约束来约束其唯一性。

使用规则，即可完成透明展开。

drop table t_unique_arr;  
  
create table t_unique_arr(id int, arr int[]);  
create index idx_t_unique_arr on t_unique_arr(id);

创建展开表

create table check_t_unique_arr(arr int unique);

创建insert, update, delete的规则，在操作源表时，自动展开多值列。

create rule r1 as on insert to t_unique_arr do also insert into check_t_unique_arr select unnest(NEW.arr);  
create rule r2 as on update to t_unique_arr do also delete from check_t_unique_arr where arr = any (OLD.arr);  
create rule r3 as on update to t_unique_arr do also insert into check_t_unique_arr select unnest(NEW.arr);  
create rule r4 as on delete to t_unique_arr do also delete from check_t_unique_arr where arr = any (OLD.arr);

创建一个测试函数，用于对源表进行DML操作。

create or replace function ins_t_unique_arr(int,int,int,int,int,int) returns void as $$  
declare  
begin  
  insert into t_unique_arr values ($1,array[$2,$3,$4,$5,$6]);  
  update t_unique_arr set arr=array[$2,$3,$4,$5,$6] where id=$1+1;  
  delete from t_unique_arr where id=$1+100;  
  exception when others then  
    return;  
end;  
$$ language plpgsql strict;

创建压测脚本

vi test.sql  
\set id1 random(100001,200000)  
\set id2 random(200001,300000)  
\set id3 random(300001,400000)  
\set id4 random(400001,500000)  
\set id5 random(500001,600000)  
\set id random(1,200000000)  
select ins_t_unique_arr(:id,:id1,:id2,:id3,:id4,:id5);

开始DML压测

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 120  

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 64  
number of threads: 64  
duration: 120 s  
number of transactions actually processed: 19510269  
latency average = 0.394 ms  
latency stddev = 1.115 ms  
tps = 162325.823786 (including connections establishing)  
tps = 162351.285338 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.001  \set id1 random(100001,200000)  
         0.000  \set id2 random(200001,300000)  
         0.000  \set id3 random(300001,400000)  
         0.000  \set id4 random(400001,500000)  
         0.000  \set id5 random(500001,600000)  
         0.000  \set id random(1,200000000)  
         0.391  select ins_t_unique_arr(:id,:id1,:id2,:id3,:id4,:id5);

检查结果一致性，是否达到了唯一要求

postgres=# select count(*) from t_unique_arr ;  
 count   
-------  
 91429  
(1 row)  
  
postgres=# select count(*) from (select unnest(arr) from t_unique_arr) t;  
 count    
--------  
 417541  
(1 row)  
  
postgres=# select count(*) from check_t_unique_arr ;  
 count    
--------  
 417541  
(1 row)

缺陷：

1、rule 不支持copy，即COPY不会触发RULE。

2、RULE 不支持 truncate。 即TRUNCATE不会触发RULE。

可以改成trigger，trigger支持copy，支持truncate。

3、采用trigger

创建触发器函数

create or replace function tg_t_unique_arr() returns trigger as $$  
declare  
begin  
  case TG_OP    
  when 'INSERT' then  
    insert into check_t_unique_arr select unnest(NEW.arr);  
    return NEW;  
  when 'UPDATE' then  
    delete from check_t_unique_arr where arr = any (OLD.arr);  
    insert into check_t_unique_arr select unnest(NEW.arr);  
    return NEW;  
  when 'DELETE' then  
    delete from check_t_unique_arr where arr = any (OLD.arr);  
    return OLD;  
  when 'TRUNCATE' then  
    truncate check_t_unique_arr;  
  end case;  
  return null;  
end;  
$$ language plpgsql strict;

删除规则

postgres=# drop rule r1 on t_unique_arr ;  
DROP RULE  
postgres=# drop rule r2 on t_unique_arr ;  
DROP RULE  
postgres=# drop rule r3 on t_unique_arr ;  
DROP RULE  
postgres=# drop rule r4 on t_unique_arr ;  
DROP RULE

创建DML触发器

postgres=# create trigger tg1 before insert or update or delete on t_unique_arr for each row execute procedure tg_t_unique_arr();  
CREATE TRIGGER

创建TRUNCATE触发器

postgres=# create trigger tg2 after truncate on t_unique_arr for each statement execute procedure tg_t_unique_arr();  
CREATE TRIGGER

压测

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 64  
number of threads: 64  
duration: 120 s  
number of transactions actually processed: 16580381  
latency average = 0.463 ms  
latency stddev = 0.408 ms  
tps = 137735.702798 (including connections establishing)  
tps = 137754.497564 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.001  \set id1 random(100001,200000)  
         0.000  \set id2 random(200001,300000)  
         0.000  \set id3 random(300001,400000)  
         0.000  \set id4 random(400001,500000)  
         0.000  \set id5 random(500001,600000)  
         0.000  \set id random(1,200000000)  
         0.460  select ins_t_unique_arr(:id,:id1,:id2,:id3,:id4,:id5);

检查约束是否生效

postgres=# select count(*) from t_unique_arr ;  
 count   
-------  
 80307  
(1 row)  
  
postgres=# select count(*) from (select unnest(arr) from t_unique_arr) t;  
 count    
--------  
 401535  
(1 row)  
  
postgres=# select count(*) from check_t_unique_arr ;  
 count    
--------  
 401535  
(1 row)

TRUNCATE测试

postgres=# truncate t_unique_arr ;  
TRUNCATE TABLE  
postgres=# select count(*) from t_unique_arr ;  
 count   
-------  
     0  
(1 row)  
  
postgres=# select count(*) from check_t_unique_arr ;  
 count   
-------  
     0  
(1 row)

使用trigger满足了最终的要求，性能也不错。

小结

1、目前使用exclude排他约束来对多值类型实现元素的全局唯一约束，使用intarry的gist && 操作符，性能并不理想。期待PostgreSQL后续版本开通gin接口的exclude支持。

2、使用rule可以实现数组元素全局唯一约束，但缺陷是： rule 不支持copy, 不支持 truncate。

3、使用trigger可以实现数组元素全局唯一约束，并且性能很不错，支持copy, truncate。

空间、。。。

4、排他约束，不仅仅能支持多值类型，同时还支持空间、范围等异构类型。广泛应用于面积不相交、范围不相交等约束场景。

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

5、PostgreSQL对多值类型的支持细节做得越来越不错，除了业务上常用的“查询相交、包含可以使用索引，更新、替换、APPEND的操作”也都有对应的UDF和OP来支撑。

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

参考

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

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

https://www.postgresql.org/docs/10/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

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...