PostgreSQL9.0新特性介绍：唯一约束的增强
时间：2010-08-22 来源：osdba
在PostgreSQL9.0提供了一个新特性： Deferrable unique constraints, now permit mass updates to unique keys，基本意思是，对于可延迟的唯一约束，允许集中更新后再统一做约束检查。在PostgreSQL9.0以前的版本中，只有外键约束可以定义为deferrable，其它的都不行，而PostgreSQL9.0中，可为deferrable的约束类型还增加了UNIQUE, PRIMARY KEY, EXCLUDE。
在PostgreSQL9.0版本之前，在执行DML语句时，唯一约束是不能deferable的，所以每更新一行，就要检查一次唯一性约束。所以，如果有一张表上有一个唯一字段叫id，内容是按顺序增长的，执行一条SQL语句： update t set id=id+1，这条SQL就会报错。实际上这条SQL语句如果更新完所有数据，是没有唯一性冲突的。这条SQL在oracle数据库中是可以正常执行完的，也不会报错。
而PostgreSQL9.0之后，当创建的约束为“Deferable”后，不论你用set constraints all immediate，还是set constraints all deferrd时，这条SQL都不会了报错了，原因是当表上的唯一约束为“Deferable”后，当执行DML语句做批量更新时，是整个更新完后，才开始做唯一性检查，而不是每更新完一行后就检查。
见下面的实验： 环境为PostgreSQL9.0 beta4， 先不加deferable参数，约束默认是no deferable的： postgres=# create table t4 (id int primary key,name varchar(40)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t4_pkey" for table "t4" CREATE TABLE postgres=# insert into t4 values(1,'1111111111111111'); INSERT 0 1 postgres=# insert into t4 values(2,'2222222222222222'); INSERT 0 1 postgres=# postgres=# update t4 set id=id+1; ERROR: duplicate key value violates unique constraint "t4_pkey" DETAIL: Key (id)=(2) already exists. STATEMENT: update t4 set id=id+1; ERROR: duplicate key value violates unique constraint "t4_pkey" DETAIL: Key (id)=(2) already exists. 默认约束是no deferable的，这时可以看到SQL执行出错。
执行set constraints all deferred: postgres=# begin; BEGIN postgres=# set constraints all deferred; SET CONSTRAINTS postgres=# update t4 set id=id+1; ERROR: duplicate key value violates unique constraint "t4_pkey" DETAIL: Key (id)=(2) already exists. STATEMENT: update t4 set id=id+1; ERROR: duplicate key value violates unique constraint "t4_pkey" DETAIL: Key (id)=(2) already exists. postgres=# end; ROLLBACK 可以看出即使设置了constraints为deferred，由于建表时约束为no deferred的，也是就约束是不能延迟的检查，执行时，即使设置constraints为deferred时，也不生效。
下面我们把约束建成deferable的： postgres=# drop table t4; DROP TABLE postgres=# create table t4 (id int primary key DEFERRABLE,name varchar(40)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t4_pkey" for table "t4" CREATE TABLE postgres=# insert into t4 values(1,'1111111111111111'); INSERT 0 1 postgres=# insert into t4 values(2,'2222222222222222'); INSERT 0 1 postgres=# update t4 set id=id+1; UPDATE 2 可以看到执行成功。 同样，我们在执行时，把constraints设置为immediate时，也可以执行成功： postgres=# begin; BEGIN postgres=# set constraints all IMMEDIATE; SET CONSTRAINTS postgres=# update t4 set id=id+1; UPDATE 2 postgres=# end; COMMIT
如果执行时设置constraints为deferred时，然后执行一个会导致唯一约束冲突的更新，可以看到到提交时，才检查约束： postgres=# begin; BEGIN postgres=# set constraints all deferred; SET CONSTRAINTS postgres=# update t4 set id=2 where id=1; UPDATE 1 postgres=# end; ERROR: duplicate key value violates unique constraint "t4_pk" DETAIL: Key (id)=(2) already exists. STATEMENT: end; ERROR: duplicate key value violates unique constraint "t4_pk" DETAIL: Key (id)=(2) already exists.
见下面的实验： 环境为PostgreSQL9.0 beta4， 先不加deferable参数，约束默认是no deferable的： postgres=# create table t4 (id int primary key,name varchar(40)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t4_pkey" for table "t4" CREATE TABLE postgres=# insert into t4 values(1,'1111111111111111'); INSERT 0 1 postgres=# insert into t4 values(2,'2222222222222222'); INSERT 0 1 postgres=# postgres=# update t4 set id=id+1; ERROR: duplicate key value violates unique constraint "t4_pkey" DETAIL: Key (id)=(2) already exists. STATEMENT: update t4 set id=id+1; ERROR: duplicate key value violates unique constraint "t4_pkey" DETAIL: Key (id)=(2) already exists. 默认约束是no deferable的，这时可以看到SQL执行出错。
执行set constraints all deferred: postgres=# begin; BEGIN postgres=# set constraints all deferred; SET CONSTRAINTS postgres=# update t4 set id=id+1; ERROR: duplicate key value violates unique constraint "t4_pkey" DETAIL: Key (id)=(2) already exists. STATEMENT: update t4 set id=id+1; ERROR: duplicate key value violates unique constraint "t4_pkey" DETAIL: Key (id)=(2) already exists. postgres=# end; ROLLBACK 可以看出即使设置了constraints为deferred，由于建表时约束为no deferred的，也是就约束是不能延迟的检查，执行时，即使设置constraints为deferred时，也不生效。
下面我们把约束建成deferable的： postgres=# drop table t4; DROP TABLE postgres=# create table t4 (id int primary key DEFERRABLE,name varchar(40)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t4_pkey" for table "t4" CREATE TABLE postgres=# insert into t4 values(1,'1111111111111111'); INSERT 0 1 postgres=# insert into t4 values(2,'2222222222222222'); INSERT 0 1 postgres=# update t4 set id=id+1; UPDATE 2 可以看到执行成功。 同样，我们在执行时，把constraints设置为immediate时，也可以执行成功： postgres=# begin; BEGIN postgres=# set constraints all IMMEDIATE; SET CONSTRAINTS postgres=# update t4 set id=id+1; UPDATE 2 postgres=# end; COMMIT
如果执行时设置constraints为deferred时，然后执行一个会导致唯一约束冲突的更新，可以看到到提交时，才检查约束： postgres=# begin; BEGIN postgres=# set constraints all deferred; SET CONSTRAINTS postgres=# update t4 set id=2 where id=1; UPDATE 1 postgres=# end; ERROR: duplicate key value violates unique constraint "t4_pk" DETAIL: Key (id)=(2) already exists. STATEMENT: end; ERROR: duplicate key value violates unique constraint "t4_pk" DETAIL: Key (id)=(2) already exists.
相关阅读 更多 +
