PostgreSQL 多值类型（如数组），元素值全局唯一

背景

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

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

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

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

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

1、采用排他约束

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

例如：

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

2、空间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、排他约束，不仅仅能支持多值类型，同时还支持空间、范围等异构类型。广泛应用于面积不相交、范围不相交等约束场景。

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

标签

背景

1、采用排他约束

2、采用rule

3、采用trigger

小结

参考