标签
PostgreSQL , 多值类型 , 数组 , 全局约束
背景
《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
