postgresql支持唯一约束吗_PostgreSQL 多值类型（如数组），元素值全局唯一 - 约束如何实现-阿里云开发者社区...

标签

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来支撑。

参考